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