How to query accross servers using MS SQL Server.

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.

Leave a Reply