SQL Server tips and experiences dedicated to my twin daughters.

Executing Queries against linked servers

Recently, a team at the office asked me how could they fire off a query against a linked server. The condition was that the server where the query would originate from was going to be a Microsoft SQL Server 2008 instance whereas the target was Microsoft SQL Server 2005 instance. The information that they would be getting would be:

SQL Server Instance name

Database Name

Schema Name

Table Name

Based on this information, they wanted to find out if a given table existed on the given database on the given instance. In all cases, the instance name would either be the local instance or a linked server.

The key point here is the usage of the EXECUTE statement. The EXECUTE statement has the provision to execute a query against a linked server when combined with the “AT” clause. More details are available from the MSDN page here.

In Conclusion

Please note that there is one major point that you need to take care before using EXECUTE in this way. EXECUTE is prone to SQL Injections and hence please make sure that you production code has each and every input validated beforehand.

You can, in this way, use the EXECUTE…AT combination to execute a query against a SQL Server of a different version or even against a different data source (the MSDN example has a way to fire off a query against an ORACLE data source).

Do let me know what you, the reader, uses to query a different database on a different SQL Server instance or a different data source.