Sql Linked Server: link

Showing posts with label link. Show all posts

Friday, March 30, 2012

Linked Server query

I have set up a link server to the AS400and I can see the tables in the Enterprise Manager, How do I query it so I can use the data in my SQL Server database.
*****************************************
* This message was posted via http://sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://sqlmonster.com/Uwe/Abuse.aspx...b79e73dd024921
*****************************************
Take a look at OPENQUERY in the BOL
Rick Sawtell
MCT, MCSD, MCDBA
"Vince Lassiter via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:66558ca34dd7468082b79e73dd024921@.SQLMonster.c om...
> I have set up a link server to the AS400and I can see the tables in the
Enterprise Manager, How do I query it so I can use the data in my SQL Server
database.
> *****************************************
> * This message was posted via http://sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://sqlmonster.com/Uwe/Abuse.aspx...b79e73dd024921
> *****************************************
sql

Linked Server query

I have set up a link server to the AS400and I can see the tables in the Enterprise Manager, How do I query it so I can use the data in my SQL Server database.
*****************************************
* This message was posted via http://sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://sqlmonster.com/Uwe/Abuse.aspx?aid=66558ca34dd7468082b79e73dd024921
*****************************************Take a look at OPENQUERY in the BOL
Rick Sawtell
MCT, MCSD, MCDBA
"Vince Lassiter via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:66558ca34dd7468082b79e73dd024921@.SQLMonster.com...
> I have set up a link server to the AS400and I can see the tables in the
Enterprise Manager, How do I query it so I can use the data in my SQL Server
database.
> *****************************************
> * This message was posted via http://sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://sqlmonster.com/Uwe/Abuse.aspx?aid=66558ca34dd7468082b79e73dd024921
> *****************************************

Linked Server query

I have set up a link server to the AS400and I can see the tables in the Ent
erprise Manager, How do I query it so I can use the data in my SQL Server da
tabase.
****************************************
*
* This message was posted via http://droptable.com
*
* Report spam or abuse by clicking the following URL:
* http://droptable.com/Uwe/Abuse.asp...2b79e73dd024921
****************************************
*Take a look at OPENQUERY in the BOL
Rick Sawtell
MCT, MCSD, MCDBA
"Vince Lassiter via droptable.com" <forum@.droptable.com> wrote in message
news:66558ca34dd7468082b79e73dd024921@.SQ
droptable.com...
> I have set up a link server to the AS400and I can see the tables in the
Enterprise Manager, How do I query it so I can use the data in my SQL Server
database.
> ****************************************
*
> * This message was posted via http://droptable.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://droptable.com/Uwe/Abuse.asp...2b79e73dd024921
> ****************************************
*sql

linked server problems

I do the following to link a server:
EXEC sp_addlinkedserver 'MainLinkedServer', 'SQLServer OLEDB Provider',
'SQLOLEDB', 'MATT', NULL, NULL, 'WEEK'
It says it links fine (no errors), but when I do a select on a table I know
exists
select distinct T1."Institution" c1 from 'MainLinkedServer'...myTable T1
I get the following error:
OLE DB provider 'MainLinkedServer'does not contain table 'myTable'. The
table either does not exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain
the table: ProviderName='MainLinkedServer', TableName='myTable'].
any ideas?
I figured it out:
SELECT * FROM linked_server.database.dbo.table_name tn
"Matt Tapia" <mtapia@.inbizservices.com> wrote in message
news:ebXRQoASEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I do the following to link a server:
> EXEC sp_addlinkedserver 'MainLinkedServer', 'SQLServer OLEDB Provider',
> 'SQLOLEDB', 'MATT', NULL, NULL, 'WEEK'
> It says it links fine (no errors), but when I do a select on a table I
know
> exists
> select distinct T1."Institution" c1 from 'MainLinkedServer'...myTable T1
> I get the following error:
> OLE DB provider 'MainLinkedServer'does not contain table 'myTable'. The
> table either does not exist or the current user does not have permissions
on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider does not contain
> the table: ProviderName='MainLinkedServer', TableName='myTable'].
> any ideas?
>
>
>

LINKED server problem..

Hi All,
Here is the problem:
I want to link my sql server 2000 with oracle server and i used Linked
Servers feature under Security in SQL Server Enterprise Manager. I succeed t
o
configure and can view all the table name (not content) whihc belong in
Oracle database.
So, i try to use Query Analyser to pull some data from the linked server.
After i run, here is my error message:
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Exec
ute
returned 0x80040155].
Any ideas? Please advice.
thank you in advance.
regards,
Ocurnos.Any idea expert?Help needed.
thanks
"Daniel" wrote:

> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Ex
ecute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.|||Any idea expert?Help needed.
thanks
"Daniel" wrote:

> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Ex
ecute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.|||I don't think this is security related since you can establish the initial
connection without error. You might try using the Microsoft OLEDB Provider
for Oracle instead of the Oracle driver.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:76511FDC-6852-48A3-BC5E-E6B758AEDC84@.microsoft.com...[vbcol=seagreen]
> Any idea expert?Help needed.
> thanks
>
> "Daniel" wrote:
>|||I don't think this is security related since you can establish the initial
connection without error. You might try using the Microsoft OLEDB Provider
for Oracle instead of the Oracle driver.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:76511FDC-6852-48A3-BC5E-E6B758AEDC84@.microsoft.com...[vbcol=seagreen]
> Any idea expert?Help needed.
> thanks
>
> "Daniel" wrote:
>|||you'd want to start here:
http://support.microsoft.com/kb/280106
-oj
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:0475374F-7DA1-441B-B30E-4BDC7295149D@.microsoft.com...
> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
> to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
> ICommandText::Execute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.|||you'd want to start here:
http://support.microsoft.com/kb/280106
-oj
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:0475374F-7DA1-441B-B30E-4BDC7295149D@.microsoft.com...
> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
> to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
> ICommandText::Execute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.

Wednesday, March 28, 2012

linked server problem

I have two SQL server A and B and I want to access data from one to the other server.
I setup a linked server named 'LinkB' on A to link server B, I can see tables and views of B when I click the linked server node in EM. I can also execute select or SP of B in Query analyzer, It works well.

My question is, when I try to built a linked server 'LinkA' on B to link A, It seems no problem occur, but when I click the linked server node, I can not see any tables or views of A, and I can't execute SQL like 'select * from A.database.dbo.table' in Query analyzer.

I use 'sa' to connect the two SQL server.

who can tell me the real reason?
thankscan u give more imformation about servers?|||Originally posted by a1ex
can u give more imformation about servers?
the two server are all Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
I can connect to A and B in EM use same userid 'sa' and password.

Thanks very much!!|||who can help me with this problem? thanks!|||Originally posted by shine_law

My question is, when I try to built a linked server 'LinkA' on B to link A, It seems no problem occur, but when I click the linked server node, I can not see any tables or views of A

i noticed that you mentioned "linkA" and then said "link A" i thi sa typo or is this how you called it?|||I placed 'LinkA' on server B, 'LinkA' is the name of the lined server, which is used to access data on server A. Actully,I can call it any other name, suck as 'LinkFromBtoA'

thankssql

Linked server problem

I linked two sql servers (one an x64 sql 2005 server the other a sql 2000 x86 server). The link is from the x64 box to the x86 box and I used Windows authentication to forge the links.

When the developer (who has Windows accounts on both boxes) logs in locally to the x64 box and runs a query, data is returned from the linked server. But when the same developer logs into his own client box and uses Management Studio to access the x64 box (via Windows authentication) and run the same query, he gets this error message"

"Login failed for user '(null)'. Reason: not associated with a trusted SQL Server Connection"

I presume that the developer should be able to access this data from his own box or can the linked data only be gotten from an account logged into the x64 box?

TIA,

barkingdog

I'm a little confused about what you mean by 'windows authentication' to forge the links.

when you create a linked server, you have the following options:

map a local login to a login on the remote server

connect using the user login's security context

connect using a specific (provided) login's security context

Whenever I've seen the above error, it has been because a developer had the server registered incorrectly in EM.

|||

Under SSMS, I thought that those options only applied when the connection trying to access the linked server was not listed in the top box ("Local server login to remote server login mappings".). So I ignored those options. Maybe I dun wrong!

The developer who is trying to access data on the linked server from SSMS from his own box is listed (using Windows authentication) in the Local Login section of the Linked server properties box of the x64 server. "Impersonate" is selected for that developer. No names are supplied for the Remote User\Remote password box as his Windows authentication will be used on the linked box. (Or so I think...)

TIA,

barkingdog

|||

I woul dfill in the other two boxes and see if that works.

I am struggling with my own linked server issues right now, related to windows authentication accross linked servers, but we would be using the

'connect using the user's login'.

You might try that and tell me if it works. ;)

|||

Here's what I have so far:

Suppose Server B is linked to Server A via a "Link Account". When I log on to Server B (using Remote Desktop) as "Link Account" I can successfully pull data from Server A, so the link is working. The problem: I logged into my own local workstation (not Server A or Server B) as "LinkAccount" fired up SSMS, connected to Server B as that account, and tried to pull the same data that I did earlier. The attempt fails saying that I am using a "null" account and no sql account exists.....blah, blah, blah.

I found this SQL 2005 BOL link that describes the situation:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9e302c29-639d-4509-ba1e-cf452582c5c3.htm

Apparently, what I wanted to do was not as simple as I thought it should be. I am creating a "double hop" and the servers need to be prepared as described in the help article. Otherwise, the error message is to be expected!

Monday, March 26, 2012

Linked Server Oracle 10g problem

I have followed the examples from MS. to create the link between my MSSQL200
5
and Oracle 10g
I have all of the Oracle client installed on the server.
I have a net service defined.
Here is what I ran to create link:
EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD89'
EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr07
'
Here is the query:
select count(*) from OraLinkServer2..sysadm.SUN_PUBT
Here is the error:
OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for
linked server "OraLinkServer2".
What am I doing wrong all the oracle tools connect just fine. I need SQL2005
to connect?
Thanks
GregHob,
I've had the same problem. I used the openquery syntax to work around
the problem. This also has the advantage of pushing the work to the
Oracle Server.
So.. try:
select * from
openquery(OraLinkServer2,
' select count(*) from SUN_PUBT
'
)
No -- you don't need the formating and returns -- I just use them to
make it easy for me to read the Oracle Commands separate from the
MS-SQL commands.
If you find a better solution, please post it.
You also might want to read:
http://www.sqlservercentral.com/col...br />
art3.asp
It has the best set of instructions for getting the Instant Client to
work.
Good luck,
Peter Ryan
Hob_Naggers wrote:
> I have followed the examples from MS. to create the link between my MSSQL2
005
> and Oracle 10g
> I have all of the Oracle client installed on the server.
> I have a net service defined.
> Here is what I ran to create link:
> EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD8
9'
> EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr
07'
>
> Here is the query:
> select count(*) from OraLinkServer2..sysadm.SUN_PUBT
> Here is the error:
> OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
> message "ORA-12154: TNS:could not resolve the connect identifier specified
> ".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "OraLinkServer2".
> What am I doing wrong all the oracle tools connect just fine. I need SQL20
05
> to connect?
> Thanks
> Greg|||Hob,
I've had the same problem. I used the openquery syntax to work around
the problem. This also has the advantage of pushing the work to the
Oracle Server.
So.. try:
select * from
openquery(OraLinkServer2,
' select count(*) from SUN_PUBT
'
)
No -- you don't need the formating and returns -- I just use them to
make it easy for me to read the Oracle Commands separate from the
MS-SQL commands.
If you find a better solution, please post it.
You also might want to read:
http://www.sqlservercentral.com/col...br />
art3.asp
It has the best set of instructions for getting the Instant Client to
work.
Good luck,
Peter Ryan
Hob_Naggers wrote:
> I have followed the examples from MS. to create the link between my MSSQL2
005
> and Oracle 10g
> I have all of the Oracle client installed on the server.
> I have a net service defined.
> Here is what I ran to create link:
> EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD8
9'
> EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr
07'
>
> Here is the query:
> select count(*) from OraLinkServer2..sysadm.SUN_PUBT
> Here is the error:
> OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
> message "ORA-12154: TNS:could not resolve the connect identifier specified
> ".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "OraLinkServer2".
> What am I doing wrong all the oracle tools connect just fine. I need SQL20
05
> to connect?
> Thanks
> Greg|||Hi Peter,
Thanks for the response, but I finally found the problem yesterday. The real
problem was not with SQL server, but rather it was a problem with the Oracle
config.
Though i had the TNSNames.ora file configured correctly the SQLNET.ora file
was where the problem lay. It was configured for ezconnect instead of
tnsnames. After this was ironed out the linking of the oracle db into sql wa
s
easy. One weird not though I found after getting the config right. When
querying the linked db. It is case sensitive with regards to column and tabl
e
names, even though on the Oracle side it is not case sensitive. I'm guessing
it has something to do with the translation being provided by the providers.
Thanks
Greg
"pryan64" wrote:

> Hob,
> I've had the same problem. I used the openquery syntax to work around
> the problem. This also has the advantage of pushing the work to the
> Oracle Server.
> So.. try:
> select * from
> openquery(OraLinkServer2,
> ' select count(*) from SUN_PUBT
> '
> )
> No -- you don't need the formating and returns -- I just use them to
> make it easy for me to read the Oracle Commands separate from the
> MS-SQL commands.
> If you find a better solution, please post it.
> You also might want to read:
> http://www.sqlservercentral.com/col... />
ypart3.asp
> It has the best set of instructions for getting the Instant Client to
> work.
> Good luck,
> Peter Ryan
> Hob_Naggers wrote:
>|||Hi Peter,
Thanks for the response, but I finally found the problem yesterday. The real
problem was not with SQL server, but rather it was a problem with the Oracle
config.
Though i had the TNSNames.ora file configured correctly the SQLNET.ora file
was where the problem lay. It was configured for ezconnect instead of
tnsnames. After this was ironed out the linking of the oracle db into sql wa
s
easy. One weird not though I found after getting the config right. When
querying the linked db. It is case sensitive with regards to column and tabl
e
names, even though on the Oracle side it is not case sensitive. I'm guessing
it has something to do with the translation being provided by the providers.
Thanks
Greg
"pryan64" wrote:

> Hob,
> I've had the same problem. I used the openquery syntax to work around
> the problem. This also has the advantage of pushing the work to the
> Oracle Server.
> So.. try:
> select * from
> openquery(OraLinkServer2,
> ' select count(*) from SUN_PUBT
> '
> )
> No -- you don't need the formating and returns -- I just use them to
> make it easy for me to read the Oracle Commands separate from the
> MS-SQL commands.
> If you find a better solution, please post it.
> You also might want to read:
> http://www.sqlservercentral.com/col... />
ypart3.asp
> It has the best set of instructions for getting the Instant Client to
> work.
> Good luck,
> Peter Ryan
> Hob_Naggers wrote:
>

Linked server on a different domain

Hello,
I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
I use SQL authentication, and both are working on TCPIP port 1433.
It doesn't work, it says "Access denied or not existant Sql"...but the
credentials are right.
I'm connected through a VPN channel (CISCO client vpn connector) with
port 1433 (and udp 1434) open...I can use Excel or create an UDL
connected to 2005 and it works fine.
What it might be? The port is fixed. Do I need to open something else?
Thanx,
BigeHi
I assume this is linking SQL 2000 from a SQL 2005 instance? Have you tried
to connect directly to this server using the credentials through Management
Studio?
If you can do that then you should be ok.
Check that you are using protocols that are enabled on the remote server.
What version are you running on each server?
John
"Bige" wrote:
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>|||Have you tried pass through authentication? This is where you have two NT
accounts with the same account names and passwords in both domains? If you
use the security context of the one account it should work against the
server in the other domain.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bige" <ivan.pololi@.interah.com> wrote in message
news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> Hello,
> I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> I use SQL authentication, and both are working on TCPIP port 1433.
> It doesn't work, it says "Access denied or not existant Sql"...but the
> credentials are right.
> I'm connected through a VPN channel (CISCO client vpn connector) with
> port 1433 (and udp 1434) open...I can use Excel or create an UDL
> connected to 2005 and it works fine.
> What it might be? The port is fixed. Do I need to open something else?
> Thanx,
> Bige
>|||I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
I don't want to use NT accounts, I need to use SQL authentication.
About the ports are the same and both use protocol TCPIP...if I
connect a SQL 2000 to 2005
in the same domain with my SQL credentials it works.
The problem is when I try to connect from another network in VPN
mode...
Thanks for your help, I appreciate it.
Bige
Hilary Cotter ha scritto:
> Have you tried pass through authentication? This is where you have two NT
> accounts with the same account names and passwords in both domains? If you
> use the security context of the one account it should work against the
> server in the other domain.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Bige" <ivan.pololi@.interah.com> wrote in message
> news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > Hello,
> >
> > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> >
> > I use SQL authentication, and both are working on TCPIP port 1433.
> >
> > It doesn't work, it says "Access denied or not existant Sql"...but the
> > credentials are right.
> >
> > I'm connected through a VPN channel (CISCO client vpn connector) with
> > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > connected to 2005 and it works fine.
> >
> > What it might be? The port is fixed. Do I need to open something else?
> >
> > Thanx,
> > Bige
> >|||Hi
Connecting with a SQL connection should not matter about the servers being
on different domains. Can you connect with Query Analyser, if you can, then
it would tend to imply that it was configuration of the linked server is the
most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
check out http://support.microsoft.com/kb/287932 and
http://support.microsoft.com/kb/306199/
With a VPN there may be delays introduced in the connection, so increasing
timeouts may be necessary, also check that you can access the server via an
IP address, if this works there may be a DNS issue.
John
"Bige" wrote:
> I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> I don't want to use NT accounts, I need to use SQL authentication.
> About the ports are the same and both use protocol TCPIP...if I
> connect a SQL 2000 to 2005
> in the same domain with my SQL credentials it works.
> The problem is when I try to connect from another network in VPN
> mode...
> Thanks for your help, I appreciate it.
> Bige
> Hilary Cotter ha scritto:
> > Have you tried pass through authentication? This is where you have two NT
> > accounts with the same account names and passwords in both domains? If you
> > use the security context of the one account it should work against the
> > server in the other domain.
> >
> > --
> > Hilary Cotter
> > Director of Text Mining and Database Strategy
> > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> >
> > This posting is my own and doesn't necessarily represent RelevantNoise's
> > positions, strategies or opinions.
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> >
> >
> >
> > "Bige" <ivan.pololi@.interah.com> wrote in message
> > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > Hello,
> > >
> > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > >
> > > I use SQL authentication, and both are working on TCPIP port 1433.
> > >
> > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > credentials are right.
> > >
> > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > connected to 2005 and it works fine.
> > >
> > > What it might be? The port is fixed. Do I need to open something else?
> > >
> > > Thanx,
> > > Bige
> > >
>|||Yes, I can connect using Query Analyser.
I created the linked server using EM; here it returns "SQL server not
existant" when I try to list tables or views...
I tried to change the timeout but didn't worked...and it is not a DNS
problem
About the microsoft kbs were not really helpful...any clues?
Thanx,
Bige
John Bell ha scritto:
> Hi
> Connecting with a SQL connection should not matter about the servers being
> on different domains. Can you connect with Query Analyser, if you can, then
> it would tend to imply that it was configuration of the linked server is the
> most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> check out http://support.microsoft.com/kb/287932 and
> http://support.microsoft.com/kb/306199/
> With a VPN there may be delays introduced in the connection, so increasing
> timeouts may be necessary, also check that you can access the server via an
> IP address, if this works there may be a DNS issue.
> John
> "Bige" wrote:
> > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> >
> > I don't want to use NT accounts, I need to use SQL authentication.
> >
> > About the ports are the same and both use protocol TCPIP...if I
> > connect a SQL 2000 to 2005
> > in the same domain with my SQL credentials it works.
> >
> > The problem is when I try to connect from another network in VPN
> > mode...
> >
> > Thanks for your help, I appreciate it.
> > Bige
> >
> > Hilary Cotter ha scritto:
> >
> > > Have you tried pass through authentication? This is where you have two NT
> > > accounts with the same account names and passwords in both domains? If you
> > > use the security context of the one account it should work against the
> > > server in the other domain.
> > >
> > > --
> > > Hilary Cotter
> > > Director of Text Mining and Database Strategy
> > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > >
> > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > positions, strategies or opinions.
> > >
> > > Looking for a SQL Server replication book?
> > > http://www.nwsu.com/0974973602.html
> > >
> > > Looking for a FAQ on Indexing Services/SQL FTS
> > > http://www.indexserverfaq.com
> > >
> > >
> > >
> > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > Hello,
> > > >
> > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > >
> > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > >
> > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > credentials are right.
> > > >
> > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > connected to 2005 and it works fine.
> > > >
> > > > What it might be? The port is fixed. Do I need to open something else?
> > > >
> > > > Thanx,
> > > > Bige
> > > >
> >
> >|||Hi
That implies it is the configuration of the linked server that may not be
working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
Analyser and if they don't give an error see if you try run a query using 4
part naming.
John
"Bige" wrote:
> Yes, I can connect using Query Analyser.
> I created the linked server using EM; here it returns "SQL server not
> existant" when I try to list tables or views...
> I tried to change the timeout but didn't worked...and it is not a DNS
> problem
> About the microsoft kbs were not really helpful...any clues?
> Thanx,
> Bige
> John Bell ha scritto:
> > Hi
> >
> > Connecting with a SQL connection should not matter about the servers being
> > on different domains. Can you connect with Query Analyser, if you can, then
> > it would tend to imply that it was configuration of the linked server is the
> > most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> > check out http://support.microsoft.com/kb/287932 and
> > http://support.microsoft.com/kb/306199/
> >
> > With a VPN there may be delays introduced in the connection, so increasing
> > timeouts may be necessary, also check that you can access the server via an
> > IP address, if this works there may be a DNS issue.
> >
> > John
> >
> > "Bige" wrote:
> >
> > > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> > >
> > > I don't want to use NT accounts, I need to use SQL authentication.
> > >
> > > About the ports are the same and both use protocol TCPIP...if I
> > > connect a SQL 2000 to 2005
> > > in the same domain with my SQL credentials it works.
> > >
> > > The problem is when I try to connect from another network in VPN
> > > mode...
> > >
> > > Thanks for your help, I appreciate it.
> > > Bige
> > >
> > > Hilary Cotter ha scritto:
> > >
> > > > Have you tried pass through authentication? This is where you have two NT
> > > > accounts with the same account names and passwords in both domains? If you
> > > > use the security context of the one account it should work against the
> > > > server in the other domain.
> > > >
> > > > --
> > > > Hilary Cotter
> > > > Director of Text Mining and Database Strategy
> > > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > > >
> > > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > > positions, strategies or opinions.
> > > >
> > > > Looking for a SQL Server replication book?
> > > > http://www.nwsu.com/0974973602.html
> > > >
> > > > Looking for a FAQ on Indexing Services/SQL FTS
> > > > http://www.indexserverfaq.com
> > > >
> > > >
> > > >
> > > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > > Hello,
> > > > >
> > > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > > >
> > > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > > >
> > > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > > credentials are right.
> > > > >
> > > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > > connected to 2005 and it works fine.
> > > > >
> > > > > What it might be? The port is fixed. Do I need to open something else?
> > > > >
> > > > > Thanx,
> > > > > Bige
> > > > >
> > >
> > >
>|||Nothing, still the same behavior...I really think is something about
VPN but I don't know where to investigate more...
Bige
John Bell ha scritto:
> Hi
> That implies it is the configuration of the linked server that may not be
> working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
> Analyser and if they don't give an error see if you try run a query using 4
> part naming.
> John
> "Bige" wrote:
> > Yes, I can connect using Query Analyser.
> >
> > I created the linked server using EM; here it returns "SQL server not
> > existant" when I try to list tables or views...
> >
> > I tried to change the timeout but didn't worked...and it is not a DNS
> > problem
> >
> > About the microsoft kbs were not really helpful...any clues?
> >
> > Thanx,
> > Bige
> >
> > John Bell ha scritto:
> >
> > > Hi
> > >
> > > Connecting with a SQL connection should not matter about the servers being
> > > on different domains. Can you connect with Query Analyser, if you can, then
> > > it would tend to imply that it was configuration of the linked server is the
> > > most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> > > check out http://support.microsoft.com/kb/287932 and
> > > http://support.microsoft.com/kb/306199/
> > >
> > > With a VPN there may be delays introduced in the connection, so increasing
> > > timeouts may be necessary, also check that you can access the server via an
> > > IP address, if this works there may be a DNS issue.
> > >
> > > John
> > >
> > > "Bige" wrote:
> > >
> > > > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> > > >
> > > > I don't want to use NT accounts, I need to use SQL authentication.
> > > >
> > > > About the ports are the same and both use protocol TCPIP...if I
> > > > connect a SQL 2000 to 2005
> > > > in the same domain with my SQL credentials it works.
> > > >
> > > > The problem is when I try to connect from another network in VPN
> > > > mode...
> > > >
> > > > Thanks for your help, I appreciate it.
> > > > Bige
> > > >
> > > > Hilary Cotter ha scritto:
> > > >
> > > > > Have you tried pass through authentication? This is where you have two NT
> > > > > accounts with the same account names and passwords in both domains? If you
> > > > > use the security context of the one account it should work against the
> > > > > server in the other domain.
> > > > >
> > > > > --
> > > > > Hilary Cotter
> > > > > Director of Text Mining and Database Strategy
> > > > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > > > >
> > > > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > > > positions, strategies or opinions.
> > > > >
> > > > > Looking for a SQL Server replication book?
> > > > > http://www.nwsu.com/0974973602.html
> > > > >
> > > > > Looking for a FAQ on Indexing Services/SQL FTS
> > > > > http://www.indexserverfaq.com
> > > > >
> > > > >
> > > > >
> > > > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > > > Hello,
> > > > > >
> > > > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > > > >
> > > > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > > > >
> > > > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > > > credentials are right.
> > > > > >
> > > > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > > > connected to 2005 and it works fine.
> > > > > >
> > > > > > What it might be? The port is fixed. Do I need to open something else?
> > > > > >
> > > > > > Thanx,
> > > > > > Bige
> > > > > >
> > > >
> > > >
> >
> >|||Hi
Check that the ports are not being blocked, you may need to set a static
port if it being allocated dynamically (for instance if the instance is a
named instance). Try connecting with a IP address, use PING and TRACERT to
see if you can see the server, use telnet to see if you can connect to the
port. The following articles and links may help
http://support.microsoft.com/kb/287932
John
"Bige" wrote:
> Nothing, still the same behavior...I really think is something about
> VPN but I don't know where to investigate more...
> Bige
> John Bell ha scritto:
> > Hi
> >
> > That implies it is the configuration of the linked server that may not be
> > working. Try using sp_addlinkedserver and sp_addlinkedsrvlogin from Query
> > Analyser and if they don't give an error see if you try run a query using 4
> > part naming.
> >
> > John
> >
> > "Bige" wrote:
> >
> > > Yes, I can connect using Query Analyser.
> > >
> > > I created the linked server using EM; here it returns "SQL server not
> > > existant" when I try to list tables or views...
> > >
> > > I tried to change the timeout but didn't worked...and it is not a DNS
> > > problem
> > >
> > > About the microsoft kbs were not really helpful...any clues?
> > >
> > > Thanx,
> > > Bige
> > >
> > > John Bell ha scritto:
> > >
> > > > Hi
> > > >
> > > > Connecting with a SQL connection should not matter about the servers being
> > > > on different domains. Can you connect with Query Analyser, if you can, then
> > > > it would tend to imply that it was configuration of the linked server is the
> > > > most likely cause (have you run sp_addlinkedsrvlogin?) If not you may want to
> > > > check out http://support.microsoft.com/kb/287932 and
> > > > http://support.microsoft.com/kb/306199/
> > > >
> > > > With a VPN there may be delays introduced in the connection, so increasing
> > > > timeouts may be necessary, also check that you can access the server via an
> > > > IP address, if this works there may be a DNS issue.
> > > >
> > > > John
> > > >
> > > > "Bige" wrote:
> > > >
> > > > > I'm connecting from SQL 2000 SP4 to SQL 2005 SP1 I guess...
> > > > >
> > > > > I don't want to use NT accounts, I need to use SQL authentication.
> > > > >
> > > > > About the ports are the same and both use protocol TCPIP...if I
> > > > > connect a SQL 2000 to 2005
> > > > > in the same domain with my SQL credentials it works.
> > > > >
> > > > > The problem is when I try to connect from another network in VPN
> > > > > mode...
> > > > >
> > > > > Thanks for your help, I appreciate it.
> > > > > Bige
> > > > >
> > > > > Hilary Cotter ha scritto:
> > > > >
> > > > > > Have you tried pass through authentication? This is where you have two NT
> > > > > > accounts with the same account names and passwords in both domains? If you
> > > > > > use the security context of the one account it should work against the
> > > > > > server in the other domain.
> > > > > >
> > > > > > --
> > > > > > Hilary Cotter
> > > > > > Director of Text Mining and Database Strategy
> > > > > > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> > > > > >
> > > > > > This posting is my own and doesn't necessarily represent RelevantNoise's
> > > > > > positions, strategies or opinions.
> > > > > >
> > > > > > Looking for a SQL Server replication book?
> > > > > > http://www.nwsu.com/0974973602.html
> > > > > >
> > > > > > Looking for a FAQ on Indexing Services/SQL FTS
> > > > > > http://www.indexserverfaq.com
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Bige" <ivan.pololi@.interah.com> wrote in message
> > > > > > news:1161088887.857054.124880@.h48g2000cwc.googlegroups.com...
> > > > > > > Hello,
> > > > > > >
> > > > > > > I'm trying to link my SQL 2000 to a SQL 2005 in a different domain.
> > > > > > >
> > > > > > > I use SQL authentication, and both are working on TCPIP port 1433.
> > > > > > >
> > > > > > > It doesn't work, it says "Access denied or not existant Sql"...but the
> > > > > > > credentials are right.
> > > > > > >
> > > > > > > I'm connected through a VPN channel (CISCO client vpn connector) with
> > > > > > > port 1433 (and udp 1434) open...I can use Excel or create an UDL
> > > > > > > connected to 2005 and it works fine.
> > > > > > >
> > > > > > > What it might be? The port is fixed. Do I need to open something else?
> > > > > > >
> > > > > > > Thanx,
> > > > > > > Bige
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>

Linked server on a different domain

linked server null

If I remote on to sql05 and open management studio
and select * from sql2000.dbo.tablename
Where sql2000 is a link from sql server05 to sql server 2000
it works
Same thing happens if I just try Test Connection under Linked Servers on my
local machine.
If on my desktop in ManagementStudio I try the same query I get:
TITLE: Microsoft SQL Server Management Studio
"The test connection to the linked server failed."
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .3042&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476[ /url]
BUTTONS:
OK
Hi Chuck,
I understand that the linked server failed to work on your local machine
with the error:
"TCP Provider: An existing connection was forcibly closed by the remote
host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)"
If I have misunderstood, please let me know.
The error indicates that the linked server login is not associated with a
trusted SQL connection and that the login account did not have the valid
user's password.
I would like to check with you the following questions:
1. How did you add the linked server?
2. Did you use SQL login or Windows login for the linked server?
3. Is your local server in the same domain as the remote server?
4. What is your remote SQL Server login mode, Windows Authentication or
Mixed?
If your servers are in the same domain and your local domain logon account
is allowed on the remote server, I recommend that you run "osql /Stcp:HRSQL
/E" from command prompt to check if you can connect to the remote SQL
Server instance HRSQL via Windows Authentication.
If you can connect to the remote SQL Server instance, remove the linked
server from SSMS:
Server Objects->Linked Servers
Then run the following statements to add the linked server:
sp_addlinkedserver 'HRSQL'
Go
sp_addlinkedsrvlogin 'HRSQL','true'
Go
Then run the query again.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

linked server null

If I remote on to sql05 and open management studio
and select * from sql2000.dbo.tablename
Where sql2000 is a link from sql server05 to sql server 2000
it works
Same thing happens if I just try Test Connection under Linked Servers on my
local machine.
If on my desktop in ManagementStudio I try the same query I get:
TITLE: Microsoft SQL Server Management Studio
--
"The test connection to the linked server failed."
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)
For help, click:
http://go.microsoft.com/fwlink?Prod...54&LinkId=20476
BUTTONS:
OK
--Hi Chuck,
I understand that the linked server failed to work on your local machine
with the error:
"TCP Provider: An existing connection was forcibly closed by the remote
host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
OLE DB provider "SQLNCLI" for linked server "HRSQL" returned message
"Communication link failure". (Microsoft SQL Server, Error: 10054)"
If I have misunderstood, please let me know.
The error indicates that the linked server login is not associated with a
trusted SQL connection and that the login account did not have the valid
user's password.
I would like to check with you the following questions:
1. How did you add the linked server?
2. Did you use SQL login or Windows login for the linked server?
3. Is your local server in the same domain as the remote server?
4. What is your remote SQL Server login mode, Windows Authentication or
Mixed?
If your servers are in the same domain and your local domain logon account
is allowed on the remote server, I recommend that you run "osql /Stcp:HRSQL
/E" from command prompt to check if you can connect to the remote SQL
Server instance HRSQL via Windows Authentication.
If you can connect to the remote SQL Server instance, remove the linked
server from SSMS:
Server Objects->Linked Servers
Then run the following statements to add the linked server:
sp_addlinkedserver 'HRSQL'
Go
sp_addlinkedsrvlogin 'HRSQL','true'
Go
Then run the query again.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

LINKED SERVER NESTED OPENQUERY SAMPLE FROM TIPS AND TRICKS DOESN'T APPEARS TO FAIL WITH MSO

Is it that I have a syntax error in the nested OPENQUERY or is there another issue? Do I need to specify a different provider in the Server Link such as OLEDB? Non-nested OPENQUERYs work fine.

I'm generally following theTips and Tricks article.

"Executing predictions from the SQL Server relational engine". One problem is the sample doesn't actually complete the example query after the second nested OPENQUERY call.

e.g.

SELECT * FROM OPENQUERY(DMServer,

'select … FROM Modell PREDICTION JOIN OPENQUERY…')

The SQL Server server link's provider is configured to allow adhoc access. I appears that the inner OPENQUERY cannot be prepared by Analysis Server or the Server link provider? but I need to return a key value t.[CardTransactionID] for joining to SQL Server data elements.

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Errors in the back-end database access module. The data provider does not support preparing queries.".

Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query

SELECT * FROM OPENQUERY(DMServer,

'SELECT

t.[CardTransactionID],

t.[PostingDate],

[Misuse Abuse Profile].[Even Dollar Purchase],

PredictProbability([Misuse Abuse Profile].[Even Dollar Purchase]) AS Score,

PredictSupport([Misuse Abuse Profile].[Even Dollar Purchase]) AS Suppt,

t.[BillingAmount]

FROM

[Misuse Abuse Profile]

PREDICTION JOIN

OPENQUERY([Athena Dev],

''SELECT

[CardTransactionID],

[PostingDate],

[BillingAmount],

[AccountNumber],

[SupplierStateProvinceCode],

[MerchantCategoryCode],

[PurchaseIDFormat],

[TransactionTime],

[TaxAmountIncludedCode],

[Tax2AmountIncludedCode],

[OrderTypeCode],

[MemoPostFlag],

[EvenDollarPurchase]

FROM

[dbo].[vMisuseAbuseProfile]

'') AS t

[Misuse Abuse Profile].[Account Number] = t.[AccountNumber] AND

[Misuse Abuse Profile].[Supplier State Province Code] = t.[SupplierStateProvinceCode] AND

[Misuse Abuse Profile].[Merchant Category Code] = t.[MerchantCategoryCode] AND

[Misuse Abuse Profile].[Purchase ID Format] = t.[PurchaseIDFormat] AND

[Misuse Abuse Profile].[Transaction Time] = t.[TransactionTime] AND

[Misuse Abuse Profile].[Tax Amount Included Code] = t.[TaxAmountIncludedCode] AND

[Misuse Abuse Profile].[Tax2 Amount Included Code] = t.[Tax2AmountIncludedCode] AND

[Misuse Abuse Profile].[Order Type Code] = t.[OrderTypeCode] AND

[Misuse Abuse Profile].[Memo Post Flag] = t.[MemoPostFlag] AND

[Misuse Abuse Profile].[Even Dollar Purchase] = t.[EvenDollarPurchase]

In desparation I tried returning the case key (CardTransactionID) and the predictive column elements but I get an error when I try that. I assume this is a no-no?

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 15.".

It appears the default Analysis Services Data Source wizard selection of SQL Native Client Data Source provider cannot prepare an OPENQUERY if passed from a linked server? I changed the Datasource to Provider=SQLOLEDB.1 and the query now works.

|||this is very odd - we will look into it - thanks for reporting.|||

Hi Bill,

Could you give more details about the setup. Is the SQL server where you defined the linked server DMTest on the same machine with the Analysis Server it refers too? And also is the SQL server refered on the inner openquery datasource [Athena Dev] on a different machine than the machines with AS and SQL server with added linked server?

Thanks,

Dana Cristofor

Friday, March 23, 2012

Linked Server Issue

I am trying to set up a link between 2 SQLServer databases. One is on
server named ACT-S91 and the other is on server named ACT-S92. I ran the
following statement on ACT-S91 - which said it worked.
sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
So, at this point I should have ACT-S91 and ACT-S92 linked. But when I run
a query from ACT-S91 trying to access the data on ACT-S92 I get the
following error:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'SQLOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
returned 0x8007007f].
I assume I am missing something in setting up the link. But from the
documentation I can't determine what that something might be. Any help?
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
If you look at the properties of the linked Server in Enterprise Manager,
under Security, what context is the connection to the linked server being
made.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I used the one that says Be made using the login's current security context.
Since I can get into both servers I assumed I would have no problems. I
have no specific local logins defined. We use windows authentication on
both machines. So, if I can get into both machines shouldn't this have been
right?
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
|||I tried setting a specific security user id and password and that, also,
provided the same error.
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
|||Figured out the problem appears to be that you can't use the SQLServer
option. But on our server there are no providers loaded so we are
researching why this is and how to fix it. Anyone have any ideas how to get
providers loaded? We have 2 other servers and the providers show up there
fine. The server on which they don't show up is a Server2003 server running
SQLServer2000 (was recently upgraded to Server2003).
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Ed Fultz" <efultz@.yahoo.com> wrote in message
news:%235FnB88UEHA.4092@.TK2MSFTNGP11.phx.gbl...
> I am trying to set up a link between 2 SQLServer databases. One is on
> server named ACT-S91 and the other is on server named ACT-S92. I ran the
> following statement on ACT-S91 - which said it worked.
> sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
> So, at this point I should have ACT-S91 and ACT-S92 linked. But when I
run
> a query from ACT-S91 trying to access the data on ACT-S92 I get the
> following error:
> Server: Msg 7302, Level 16, State 1, Line 1
> Could not create an instance of OLE DB provider 'SQLOLEDB'.
> OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
> returned 0x8007007f].
>
> I assume I am missing something in setting up the link. But from the
> documentation I can't determine what that something might be. Any help?
>
> --
> Edward Fultz
> Sr. Software Engineer
> Pamet Systems
> Acton, Ma
> www.pametsystems.com
>
sql

Linked Server Issue

I am trying to set up a link between 2 SQLServer databases. One is on
server named ACT-S91 and the other is on server named ACT-S92. I ran the
following statement on ACT-S91 - which said it worked.
sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
So, at this point I should have ACT-S91 and ACT-S92 linked. But when I run
a query from ACT-S91 trying to access the data on ACT-S92 I get the
following error:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'SQLOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
returned 0x8007007f].
I assume I am missing something in setting up the link. But from the
documentation I can't determine what that something might be. Any help?
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.comIf you look at the properties of the linked Server in Enterprise Manager,
under Security, what context is the connection to the linked server being
made.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I used the one that says Be made using the login's current security context.
Since I can get into both servers I assumed I would have no problems. I
have no specific local logins defined. We use windows authentication on
both machines. So, if I can get into both machines shouldn't this have been
right?
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||I tried setting a specific security user id and password and that, also,
provided the same error.
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||Figured out the problem appears to be that you can't use the SQLServer
option. But on our server there are no providers loaded so we are
researching why this is and how to fix it. Anyone have any ideas how to get
providers loaded? We have 2 other servers and the providers show up there
fine. The server on which they don't show up is a Server2003 server running
SQLServer2000 (was recently upgraded to Server2003).
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Ed Fultz" <efultz@.yahoo.com> wrote in message
news:%235FnB88UEHA.4092@.TK2MSFTNGP11.phx.gbl...
> I am trying to set up a link between 2 SQLServer databases. One is on
> server named ACT-S91 and the other is on server named ACT-S92. I ran the
> following statement on ACT-S91 - which said it worked.
> sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
> So, at this point I should have ACT-S91 and ACT-S92 linked. But when I
run
> a query from ACT-S91 trying to access the data on ACT-S92 I get the
> following error:
> Server: Msg 7302, Level 16, State 1, Line 1
> Could not create an instance of OLE DB provider 'SQLOLEDB'.
> OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
> returned 0x8007007f].
>
> I assume I am missing something in setting up the link. But from the
> documentation I can't determine what that something might be. Any help?
>
> --
> Edward Fultz
> Sr. Software Engineer
> Pamet Systems
> Acton, Ma
> www.pametsystems.com
>

Linked Server Issue

I am trying to set up a link between 2 SQLServer databases. One is on
server named ACT-S91 and the other is on server named ACT-S92. I ran the
following statement on ACT-S91 - which said it worked.
sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
So, at this point I should have ACT-S91 and ACT-S92 linked. But when I run
a query from ACT-S91 trying to access the data on ACT-S92 I get the
following error:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'SQLOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
returned 0x8007007f].
I assume I am missing something in setting up the link. But from the
documentation I can't determine what that something might be. Any help?
--
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.comIf you look at the properties of the linked Server in Enterprise Manager,
under Security, what context is the connection to the linked server being
made.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I used the one that says Be made using the login's current security context.
Since I can get into both servers I assumed I would have no problems. I
have no specific local logins defined. We use windows authentication on
both machines. So, if I can get into both machines shouldn't this have been
right?
--
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||I tried setting a specific security user id and password and that, also,
provided the same error.
--
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||Figured out the problem appears to be that you can't use the SQLServer
option. But on our server there are no providers loaded so we are
researching why this is and how to fix it. Anyone have any ideas how to get
providers loaded? We have 2 other servers and the providers show up there
fine. The server on which they don't show up is a Server2003 server running
SQLServer2000 (was recently upgraded to Server2003).
--
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Ed Fultz" <efultz@.yahoo.com> wrote in message
news:%235FnB88UEHA.4092@.TK2MSFTNGP11.phx.gbl...
> I am trying to set up a link between 2 SQLServer databases. One is on
> server named ACT-S91 and the other is on server named ACT-S92. I ran the
> following statement on ACT-S91 - which said it worked.
> sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
> So, at this point I should have ACT-S91 and ACT-S92 linked. But when I
run
> a query from ACT-S91 trying to access the data on ACT-S92 I get the
> following error:
> Server: Msg 7302, Level 16, State 1, Line 1
> Could not create an instance of OLE DB provider 'SQLOLEDB'.
> OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
> returned 0x8007007f].
>
> I assume I am missing something in setting up the link. But from the
> documentation I can't determine what that something might be. Any help?
>
> --
> Edward Fultz
> Sr. Software Engineer
> Pamet Systems
> Acton, Ma
> www.pametsystems.com
>

Wednesday, March 21, 2012

Linked server from SQL2005 to SQL2000

I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.

Though the link server is created successfully, I am not able to se the tables under it so could not query anything.

USE [master]

EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation compatible', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'data access', @.optvalue=N'true'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc',@.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'rpc out', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'connect timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'collation name', @.optvalue=null

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'query timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName', @.optname=N'use remote collation', @.optvalue=N'true'

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'

Did you check permissions of remoteUser on the remote server instance? You can do this by just using the remote login to connect to the server using ISQLW and see what tables you can access or check your permissions.|||Yes, remoteUser is able to access RemoteServerName through Query Analizer.|||I didn't mean just accessing the server. That works since you didn't get any errors. What tables can you access? What happens if you try to select from a known table?|||

You have to use the Servername.Database.UserObject.Table on the query..

example,

Select * from [RemoteServer\MSSS2000].NorthWind.dbo.Orders

Friday, March 30, 2012

Linked Server query

Linked Server query

Linked Server query

linked server problems

LINKED server problem..

Wednesday, March 28, 2012

linked server problem

Linked server problem

Monday, March 26, 2012

Linked Server Oracle 10g problem

Linked server on a different domain

Linked server on a different domain

Linked server on a different domain

linked server null

linked server null

LINKED SERVER NESTED OPENQUERY SAMPLE FROM TIPS AND TRICKS DOESN'T APPEARS TO FAIL WITH MSO

Friday, March 23, 2012

Linked Server Issue

Linked Server Issue

Linked Server Issue

Wednesday, March 21, 2012

Linked server from SQL2005 to SQL2000

Sql Linked Server

Blog Archive

About Me