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:

1. SQL Server Instance name
2. Database Name
3. Schema Name
4. 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.

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.

Be courteous. Drive responsibly.