Tuesday, April 28, 2009

How to access Remote sql server’s database table

Recently I got a requirement to migrate data from other sql server’s database to my local sql server’s database. Then I tried querying like this.

select * from ServerName.DataBaseName.dbo.TableName

When I ran the query “select * from FilterDB011.FilterWebDB.dbo.SiteUser” I got an error saying

“Could not find server ‘FilterDB011′ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.”

Then I ran the query ” execute sp_addlinkedserver FilterDB011″ as the error message suggested.

Then It worked fine.

Now I am able to access the tables and databases from that remote database server.

The syntax to add a remote sql server to sys.servers is:

execute sp_addlinkedserver sqlserverName

The syntax to access a table in remote sql server is:

SeverName.DatabaseName.dbo.TableName

2 comments:

Cannerald said...

You might be interested in RHUB’s http://www.rhubcom.com remote access solution. It is a completely non-download system and gives complete control of the Remote PC. Multiple platforms are supported and there are some impressive features such as instant remote control, faster remote access, remote reboot, file transfer, interactive chat, and recording.

Kiran said...

the correct syntax to use remote server is that

execute sp_addlinkedserver RemoteSqlserverName

than
SELECT * FROM OPENQUERY([RemoteSqlserverName],'select * from DatabaseName.dbo.TableName')