The Exec Exec trick

by | Nov 11, 2014

Steven-Ensslen-Orange
SQL Server’s Transact SQL has some weird constraints. One of which is that certain statements must be the first one in their batch. This causes all kinds of head-aches for dynamic SQL.
I start with a statement that I want to execute, that I have generated dynamically. For example:

CREATE SCHEMA ExampleSchema;

But I want to execute this statement in a remote database. So the statement becomes:

USE DatabaseName;
CREATE SCHEMA ExampleSchema;

These statements will not run dynamically as a batch. Error 111 results.
The solution is to create an extra, nested batch. I call this the Exec Exec trick.

DECLARE @CreateSchemaSQL NVARCHAR(1000);
DECLARE @DatabaseName SYSNAME = N'ExampleDatabase';
DECLARE @SchemaName SYSNAME = N'ExampleSchema';
SET @CreateSchemaSQL = 'USE '+ quotename(@DatabaseName) + '; EXEC sp_executesql N''CREATE SCHEMA '+ quotename(@SchemaName) + ' ''; ';
EXEC (@CreateSchemaSQL);

The second EXEC is executed first. It creates a batch that runs the SQL stored in the variable which changes the database with the USE statement. That batch then creates another batch with SP_EXECUTESQL that runs the statement we care about that needs to be the first statement in its batch.
We organise the layers of the Optimal Data flow in different databases, so we use this technique all of the time.
-Steven

1 Comment
  1. Steven Ensslen

    Practically, it makes sense to wrap this logic in a procedure:

    CREATE PROCEDURE dbo.uspExecuteSQLInAnotherDB
    @DatabaseName SYSNAME,
    @RemoteSQL NVARCHAR(max)
    AS
    BEGIN
    /* See http://blog.optimalbi.com/2014/11/11/the-exec-exec-trick/ */
    DECLARE @LocalSQL NVARCHAR(max);
    /* The replace is to double-up any quotes as the EXEC is going to reduce them too early otherwise */
    SET @LocalSQL = 'USE '+ quotename(@DatabaseName) + '; EXEC sp_executesql N'' ' + replace(@RemoteSQL,'''','''''') + ' ''; ';
    EXEC (@LocalSQL);
    END;
    GO

    Reply
Leave a Reply to Steven Ensslen Cancel reply

Your email address will not be published. Required fields are marked *