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.
Here’s the solution I proposed to them:
Step 01: Add a Microsoft SQL Server 2005 linked server
USE master; GO EXEC sp_addlinkedserver 'VPCW2K3', N'SQL Server' GO
Step 02: Write a stored procedure that builds and executes a dynamic SQL statement
Here is the stored procedure that I wrote as a POC to help them out:
CREATE PROCEDURE dbo.proc_CheckTableExistance @tInstanceName NVARCHAR(200), @tDatabaseName NVARCHAR(100), @tSchemaName NVARCHAR(100), @tTableName NVARCHAR(100) AS BEGIN DECLARE @stmnt NVARCHAR(MAX) --Check if the provided server is a linked server or not IF EXISTS (SELECT data_source FROM sys.servers ss WHERE ss.data_source = @tInstanceName AND ss.is_linked = 0) BEGIN SET @stmnt = 'SELECT IST.TABLE_NAME FROM [' + LTRIM(RTRIM(@tDatabaseName)) + '].' + 'INFORMATION_SCHEMA.TABLES IST WHERE IST.TABLE_CATALOG = ' + '''' + LTRIM(RTRIM(@tDatabaseName)) + '''' + ' AND IST.TABLE_SCHEMA = ' + '''' + LTRIM(RTRIM(@tSchemaName)) + '''' + ' AND IST.TABLE_NAME = ' + '''' + LTRIM(RTRIM(@tTableName)) + '''' END ELSE IF EXISTS (SELECT data_source FROM sys.servers ss WHERE ss.data_source = @tInstanceName AND ss.is_linked = 1) BEGIN SET @stmnt = 'EXECUTE (''SELECT IST.TABLE_NAME FROM ' + @tDatabaseName + '.INFORMATION_SCHEMA.TABLES IST WHERE IST.TABLE_CATALOG = ? AND IST.TABLE_SCHEMA = ? AND IST.TABLE_NAME = ?'', ''' + @tDatabaseName + ''', ''' + @tSchemaName + ''',''' + @tTableName + ''') AT [' + @tInstanceName + ']' END ELSE BEGIN SET @stmnt = 'SELECT ''Required SQL instance not found in sys.servers catalog view.''' END EXEC sp_executesql @stmnt END GO
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.
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.
Be courteous. Drive responsibly.