Posted by on November 11, 2014

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.

Posted in: Technical


Be the first to comment.

Leave a Reply