The Exec Exec trick

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.

Read the rest on the Optimal BI blog.

Comments