Tuesday, November 11, 2008

How to use data of another SQL Server?

If you are inside a database server and want to use data from another database server, then, it is possible through linked servers. Link server links two servers. Through the use of link server, you can execute any queries in another server. You will have to map a local login (or all) to remote user to connect.

 

To create a link server, you can use sp_addlinkedserver procedure provided by SQL Server. Its syntax and example is shown below.

 

Syntax : exec sp_addlinkedserver <remote server>, <server product>, <provider>, <data source>, <location>, <provider string>, <catalog>

Example : exec sp_addlinkedserver 'testserver', 'sql server'

 

The above example uses default provider i.e. SQLOLEDB and other default values. You can get a list of linked servers by running a procedure sp_linkedservers. Only adding link server is not enough. Now, you will have to map at least a local login to remote user for connecting. It can be done by using procedure sp_addlinkedsrvlogin. The syntax and example is shown below.

 

Syntax : exec sp_addlinkedsrvlogin <remote server name>, <use self>, <local login>, <remote user>, <remote password>

Example : EXEC sp_addlinkedsrvlogin 'testserver', 'false', 'suraj', 'admin', 'NewPassword'

 

This is all done. Now you can access data from remote server i.e. 'testserver' like shown below provided that table tbl_test_id exists in 'testserver'.

 

Select   *

From    testserver.testdb.admin.tbl_test_id

 

The syntax used is.

Select   *

From    <remote server>.<database>.<user>.<table name>.

 

This is all done. The same format can be used for all queries to be executed. All executions are of course subjected to limitations of remote user mapped to the login.

 

The whole process can be done through SQL Server Enterprise Manager. For that, go to Enterprise Manager -> SQL Server Group -> "Server Name" -> Security -> Linked Servers -> Right click and select New Linked Server. In the new pop-up window, type the name of linked server, select SQL Server as server type. In "Security" tab map a local login to remote user with password. Also, you can map all local logins to a particular remote login. Finally, click "OK" to save and you are done. Now, you can begin using linked server.

 

Last but not the least, you can use linked server to connect to other data sources besides SQL Server like Oracle, Microsoft Excel and even text files.

2 comments:

Anonymous,  July 9, 2012 at 12:33 PM  

Very Useful.. Thanks alot