Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The Question:

As far as I can tell, sp_executesql adds statements to the beginning of submitted dynamic SQL script. But, a SQL Profiler trace does not capture the extra statements, and neither does DBCC OUTPUTBUFFER. So:

  1. Is there any way to see the extra statements added to submitted dynamic SQL batches by sp_executesql?
  2. Can anyone confirm definitively that my conclusions about the extra statements are correct/incorrect?

Background

I have a database where some objects (views, synonyms, SPs) are rewritten based on data in a Script table. If the database is moved to another server, a stored procedure loops through the rows of the Script table, replaces certain key values in the supplied SQL script with those defined for the new server context, and runs the script.

Everything was working fine until I made a few tweaks to add support for scripting permissions through this same mechanism. The database integrates with a vendor's product, and in each environment the vendor's database can have a different user that must be given permission to a particular view in my database for reporting purposes. So, I have to query for that user (from the vendor's database) then use that name to create the user in my database if it doesn't exist and finally grant SELECT permission. This required more lengthy scripting and doing dynamic-sql inside of dynamic-sql, so I wanted to pass in my outer script's @Debug parameter so I could see the extra script that was being generated and confirm its correctness before trying to execute it.

Other than changing what object types could be scripted and making the DROP script optional, the only material change I made to accommodate the @Debug parameter was to change this:

EXEC (@CreateSQL);

to this:

EXEC sp_executesql @CreateSQL, N'@Debug bit', @Debug;

Then I ran into a problem: the one stored procedure in my Script table could no longer be created, though the DROP just before it still worked okay. The result I got was this:

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'PROCEDURE'.

This was very confusing, but after wrangling with it for quite some time, I finally figured out the problem: sp_executesql binds parameters to dynamic SQL by secretly adding a DECLARE statement to the top before executing. Since CREATE PROCEDURE must be the only statement in the batch, but there is now an extra statement before the CREATE PROCEDURE line, it throws an error. It does say Line 1--which further misled me--but this is obviously tweaked by the engine so people don't get confused about the line numbers of their own script when dealing with errors.

The solution to the problem was to detect which type of object was being worked with and NOT pass in the @Debug parameter so script that must have no other statements works okay. A quick change did the job:

IF @ScriptType IN ('Procedure', 'View', 'Function') BEGIN
   EXEC sp_executesql @CreateSQL;
END
ELSE BEGIN
   EXEC sp_executesql @CreateSQL, N'@Debug bit', @Debug;
END;

I could also have nested my dynamic SQL one level deeper, to create the procedure inside dynamic sql (again, inside the script in the table) but that was a less optimal solution in my case.

I suspect that using OUTPUT variables with sp_executesql would also add one or more statements to the end of the script to enable the engine to capture them, most likely in a SELECT statement that is silently swallowed up.

share|improve this question
    
The extra statement in your batch is probably DECLARE @Debug bit = 0;. I don't think sp_executesql can internally do an RPC call like a TDS call would, so it must fake it by editing the batch. I'm speculating. –  Remus Rusanu May 28 '13 at 19:02

2 Answers 2

Prior to the sp_executesql you should see an sp_prepare statement in Profiler that would give more insight: http://msdn.microsoft.com/en-us/library/ff848808(v=sql.110).aspx

Sometimes this can be hard to find, but was made a bit easier with extended events in 2008R2 - specifically causality tracking.

http://msdn.microsoft.com/en-us/library/bb630284.aspx

share|improve this answer
    
There's no statement preparation going on here, though in response to your post I did further experimenting with Profiler on a different server and this finally showed me what's going on. Do you want to elaborate or shall I post my findings? –  ErikE May 28 '13 at 18:39
    
+1 for contributing –  ErikE May 30 '13 at 2:25
    
are you optimizing for ad-hoc queries at the server level? –  Richard Schweiger May 30 '13 at 15:34
    
No, these are administrative queries submitted once in a blue moon, but absolutely necessary to have 100% right when needed. Performance is not an issue. It's all explained in my post. –  ErikE May 30 '13 at 16:14
up vote 1 down vote accepted

I discovered that the server doesn't put the injected script content into the batch, but into the cached execution plan. So, you can only see this happening in profiler if the executed dynamic SQL is not in the cache. This threw me off originally because I only started tracing after running my test once, so the statement was already in the cache. I finally found this event when I switched DB servers and had the trace running on the first execution.

Here's the batch I ran:

DECLARE @SQL nvarchar(4000), @Debug bit;
SET @SQL = 'WAITFOR DELAY ''00:00:20'';';
EXEC sp_executesql @SQL, N'@Debug bit OUTPUT', @Debug OUTPUT;

And here's what I found in SQL Profiler 10.0.1600.22 running against Microsoft SQL Server 2008 R2 (SP2) - 10.50.4263.0 (X64):

Event: SP:CacheInsert
Submitted SQL text: WAITFOR DELAY '00:00:20';
Text placed in cache: (@Debug bit OUTPUT)WAITFOR DELAY '00:00:20';

So I was right that there is text being injected, but wrong on a few points:

  1. It is not a normal DECLARE statement. It's something else. What that is... stay tuned.

  2. When there are OUTPUT variables, it is treated more like a stored procedure with an OUTPUT variable. There is no hidden SELECT or other content added to the end like database provider drivers often do to submitted batches to collect return values or rowcounts.

  3. There is no line-number tweaking going on. The extra script is added to the beginning without line breaks.

If you try to submit the full cached text directly, you get an error:

(@Debug bit OUTPUT)WAITFOR DELAY '00:00:20';

Msg 1050, Level 15, State 1, Line 1

This syntax is only allowed for parameterized queries.

So it appears that the correct term is a "parameterized query". While the term normally refers to front-end coding, apparently there is an internal SQL Server kind that is used in SQL cached in execution plans.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.