How to query table Data from the remote 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 2008 Forums

Transact-SQL (2008)

How to query table Data from the remote server

Author

Topic

misserwell
Starting Member

2 Posts

Posted - 2010-12-03 : 03:22:48

HI ,ALL
I met a problem about sql server 2008 which need query table data of remote server's database without logining the remoteserver, for instance: I am in Server A , I want to query as below :
select * from remoteserverB.databasename.tablename

what should I do ? BTW , I know I must execute ahead ,
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

Exec sp_addlinkedserver 'anothernameofdatabasename','','SQLNCLI','remoteserverB'
Exec sp_addlinkedsrvlogin 'anothernameofdatabasename','false',NULL,'MyDomain\MyAccount','MyAccount','MyPassword'
select * from anothernameofdatabasename.databasename.dbo.tablename

but I always meet the error as below:

Msg 18456, Level 14, State 1, Line 1
Login failed for user ''MyDomain\MyAccount'.

Please note : I must login remote server DB with using Windows Authentication mode , thanks a lot

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 04:27:52

MyDomain\MyAccount needs to be given access to the remote server.

try
sp_addlinkedserver @server=N'myserver', @srvproduct=N'SQL Server'
sp_addlinkedsrvlogin @rmtsrvname=N'myserver',@useself=N'True'

That will use the curent windows login to the local server to connect to the remote server.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

misserwell
Starting Member

2 Posts

Posted - 2010-12-06 : 03:37:46

Thank you reply , but I wanna to know how to query data successfully with the below T- SQL clause:

Exec sp_addlinkedserver 'anothernameofdatabasename','','SQLNCLI','remoteserverB'
Exec sp_addlinkedsrvlogin 'anothernameofdatabasename','false',NULL,'MyDomain\MyAccount','MyAccount','MyPassword'
select * from anothernameofdatabasename.databasename.dbo.tablename

Maybe I have any error ? couldyou give an example ?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-06 : 05:04:18

'MyAccount','MyPassword'
needs to be a login with access to the remote server.
'MyDomain\MyAccount'
needs to have access to the local server.

Why use sqlncli instead of sqlncli10 if you have v2008?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources