How to query accross servers using MS SQL Server.

28 December, 2006 (17:08) | SQL

Sometimes you need to query across servers, even servers of different type such as doing a table join from MySQL to Oracle. I’m going to show you two ways you could do this if you have access to an SQL Server. There are ways to do this with other servers, and there are more than two ways to do this in SQL Server, but I’m only covering two methods both using SQL Server here.

Using linked servers:

Execute the following two stored procedures (you will need SA privileges):

exec sp_addlinkedserver @server=’server.asdf.com‘;
exec sp_addlinkedsrvlogin @rmtsrvname=’server.asdf.com‘,@useself=false, @rmtuser=’yourusername‘, @rmtpassword=’yourpassword‘;

Test the connection:

select top 1 * from [server.asdf.com].somedatabase.dbo.sometable;

Using OpenDataSource:

No setup is needed for this method, you just need to alter the FROM clause of your SQL statement:

select * from OpenDataSource(’SQLOLEDB‘,’Data Source=server.asdf.com;User ID=yourusername;Password=yourpassword‘).somedatabase.dbo.sometable;

Comments

There are a few notable differences between the two methods:

  • Using OpenDataSource does not require any extra privileges beyond execute query, where Linked Servers requires admin privileges.
  • Using OpenDataSource requires that the username and password be accessible in some way to your code. Linked Servers hides the login credentials, but will allow anyone with SELECT privileges to the server to authenticate and run queries on any linked servers as the user specified when sp_addlinkedsrvlogin was ran. It’s up to you to determine which is more secure for your environment.

Note, the examples I’ve provided use SQL Server as the linked server, but it’s possible to use any database which you have an ODBC driver for. The Microsoft documentation for each method will help you figure out exactly how if you can’t figure it out.

Comments

Comment from John Proctor
Date: November 24, 2008, 3:29 pm

Your statement worked great for:
select top 5 * from opendatasource(’sqloledb’,'data source=neroon;user id=sa;password=wolf359′).archive_siebeldb.dbo.s_order

My problem is I need to do an update using this statement, and get the error message at the end:
update opendatasource(’sqloledb’,'data source=neroon;user id=sa;password=wolf359′).archive_siebeldb.dbo.s_order
SET archive_siebeldb.dbo.s_order.ACCNT_ID = @MOVETO_ROWID
WHERE archive_siebeldb.dbo.s_order.ACCNT_ID = @MOVEFROM_ROWID
The multi-part identifier “archive_siebeldb.dbo.s_order.ACCNT_ID” could not be bound.
Thank you for any help,
John Proctor

Write a comment