[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Running queries on remote databases from SQL Server

Published on
30,307 Points
4 Endorsements
Last Modified:

0. Introduction

When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database.
Problems start when you try to combine that with some "user input" passed using parameters, usually in conjunction with stored procedures.

For simplifying I won't refer to a Stored Procedure in the below examples, instead use some script that could run "on it's own".

So, consider this rough setup:
A SQL 2005+ instance/database named SQL_DB
Some (remote) Oracle (or whatever type) database named REMOTE_DB
A linked server configured on the MS SQL 2005+ instance to that remote db, named LINKED_DB
On that (remote) database, a table named TEST.ACCOUNT, with a couple of colums with all possible data types
I assume that the reader knows how to setup a Linked Server, otherwise you go to Creating Linked Servers

1. Usual techniques

There are several techniques to query the remote table, for example, to get the balance from the account 12345:

Open in new window

or, with the parameter method:
declare @id int 
set @id = 12345

Open in new window

Syntax note: LINKED_DB..TEST.ACCOUNT is the so-called four-parts name, build as: <linked_server_name>.<database_name>.<schema_name>.<table_name>.
For some linked server drivers or providers, the database_name is not needed or even not supported, and the schema_name might not be needed either, so if the linked server is configured to connect with TEST login, you might even write:

Open in new window

A first issue with this could be that, for whatever reason, the filtering is not occurring on the remote db, but locally; which means that the full table data is fetched locally before filtering. Imagine you happen to get that on a big table!

And here is another issue you might run into, a error message like:
The OLE DB provider "OraOLEDB.Oracle" for linked server "LINKED_DB" supplied invalid metadata for column "LASTRESETDATE". The data type is not supported.

Huh? The column is not actually used in the query at all, still the complaint. I have no explanation yet, but as a workaround there are a couple of methods to examine:
You can create a VIEW on the REMOTE_DB, that returns all the problematic columns with mapped data types. This might not be possible in all cases, or result in other problems.
You could try to change to another Provider/Driver to find the one that works for this query, but eventually will break other things elsewhere.
Try some other query technique from below.
Let's use the also well-known OPENQUERY syntax:

Open in new window

While this should work, and most will know, if the id value is a parameter, you would need to build the sql like this:
Declare @id int -- would be the parameter for the stored procedure, for example
set @id = 12345

Declare @sql varchar(max)
Set @sql = 'SELECT * FROM OPENQUERY(LINKED_DB, ''SELECT balance FROM TEST.ACCOUNT WHERE id = ' + cast(@id as varchar(100)) + ' '') ' 

Open in new window

That will execute fine, but you can surely see how dull this kind of code will look like, especially if the parameter is text and not a number (more quotes to be added …). Another danger here is that if the coder forgets to care about SQL Injection, the whole instance (and via the linked servers, even those remote databases) is at risk!
To get it even more interesting, you often want to actually retrieve some returned value(s), which would make it like this:
Declare @id int -- would be the parameter for the stored procedure, for example
set @id = 12345
Declare @balance int --- output value

Declare @sql nvarchar(max)
Set @sql = 'SELECT @res = balance FROM OPENQUERY(LINKED_DB, ''SELECT balance FROM TEST.ACCOUNT WHERE id = ' + cast(@id as varchar(100)) + ' '') ' 

exec sp_executesql @sql, N'@res int OUTPUT', @balance OUTPUT

Open in new window

This also works to get several columns from the table, but still, the usuage of input parameter @id is not really "nice".

2. Nice way, available starting with MSSQL 2005

Here we go:
Declare @id int --- would be the parameter for the stored procedure, for example
set @id = 12345

Declare @balance int --- output value

Declare @sql nvarchar(max)
Set @sql = 'BEGIN SELECT balance INTO ? FROM TEST.ACCOUNT WHERE id = ?; END; ' 

EXECUTE(@sql, @balance OUTPUT, @id) AT [LINKED_DB]

Open in new window

Much easier to read, and no quoting problems, no data type issues.
Note that the order of the parameters to be passed has to match the order of the ? in the sql string, with the database as first!
The BEGIN/END part is specific to Oracle and the provider used, you might have to change that syntax a bit in your place.

It is still not perfect, but as close as you could come with queries on linked servers with parameters.
Of course, you can run plain sql to fetch multiple records, with parameters:EXECUTE ('SELECT * FROM TEST.ACCOUNT WHERE id >= ? AND ROWNUM <= ? ', 12345, 10) AT [LINKED_DB]
Or insert the results into a local (fixed, temp or variable) table:CREATE TABLE #tmp ( id INT , balance INT )
EXECUTE ('SELECT id, balance FROM TEST.ACCOUNT WHERE id >= ? AND ROWNUM <= ? ', 12345, 10) AT [LINKED_DB]
Select * from #tmp
Drop table #tmp 

Open in new window

3. Does it stop with SELECT?

Unlike the OPENQUERY method, you can perfectly run INSERT, UPDATE or DELETE using the EXECUTE ... AT method:
EXECUTE ('INSERT INTO TEST.ACCOUNT (id, balance) VALUES (?,?)', 54321, 0) AT [LINKED_DB]
EXECUTE ('UPDATE TEST.ACCOUNT SET balance = balance + ? WHERE id = ?', 10, 54321) AT [LINKED_DB]

Open in new window

All you need to take care of is the order of the parameters!

4. Possible issues/errors

Besides the problems noted above, you could run into some other issues.

For example, when running queries with output parameters, you might receive this error message:
Msg 7411, Level 16, State 1, Line 1
Server 'LINKED_DB' is not configured for RPC.

Open in new window

This error just tells you that you have to configure the RPC and RPC OUT in the linked server’s properties.

On execution of INSERT INTO <table> EXECUTE … you might run into this kind of errors:
OLE DB provider "OraOLEDB.Oracle" for linked server "LINKED_DB" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
Msg 7391, Level 16, State 2, Line 16
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "LINKED_DB" was unable to begin a distributed transaction.

Open in new window

This can be solved using the registry tweak described in general in this article: http://support.microsoft.com/kb/922430.
For LINKED_DB being Oracle, you need to include have
Oracle Services for Microsoft Transaction Server
installed on the SQL Server box as part of the Oracle Client installation.

5. Conclusion

As usual, any feedback is welcome, and I hope that this article was helpful to you!
1 Comment
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
Some eventually useful information in this context is the feature WITH RESULT SETS for the EXECUTE statement:

Featured Post

Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month