access tables (thru select) from a diff server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

access tables (thru select) from a diff server

Author

Topic

juvethski
Starting Member

46 Posts

Posted - 2006-01-31 : 11:34:44

hi,

is there a way, or what is the syntax of the select statement (sql query analyzer), to access tables in 2 different servers?

thanks.

~~~~~~~~~~~~~~~~
how's your data?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-31 : 11:38:02

Look up sp_addlinkedserver in BOL (Books Online)

juvethski
Starting Member

46 Posts

Posted - 2006-01-31 : 12:44:36

hi,

i looked at that and executed the following statements:

EXEC sp_addlinkedserver 'servername', 'sql server'
EXEC sp_addlinkedsrvlogin 'servername', 'true'

when i ran the select statement, i got this error message:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

the servers are setup to use windows authentication. what could be the reason for the error?

thanks.

~~~~~~~~~~~~~~~~
how's your data?

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-31 : 12:53:14

[code]
EXEC sp_addlinkedserver
@server= 'Server Alias', --Whatever you want to name the server
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='ServerName' --Actual Server name

Go

EXEC sp_addlinkedsrvlogin
Server Alias, --Use the same alias as above
false,
NULL,
Username, --Your username on the remote server
PassWord --Your password on the remote server

go
[/code]

Then when you query it it should be as follows:
[code] Alias.Database.dbo.TableName[/code]

N

The revolution won't be televised!

juvethski
Starting Member

46 Posts

Posted - 2006-01-31 : 13:50:32

i tried this but its not working because my access to the remote server was setup using windows authenticated login. if i supply a username and password, i think the remote server is assuming that the authentication is done by the sql server. thanks.

~~~~~~~~~~~~~~~~
how's your data?

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-31 : 14:20:25

Then the method you used should work

 EXEC sp_addlinkedsrvlogin 'servername', 'true'

Just change the "Server" name to the server alias that you passed in your

sp_addlinkedserver

N

The revolution won't be televised!

juvethski
Starting Member

46 Posts

Posted - 2006-02-01 : 16:30:33

the problem is its not working. i found some discussions related to this and they mentioned named pipes and disabling the tcp/ip protocol. any other suggestions?

many thanks!

~~~~~~~~~~~~~~~~
how's your data?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources