Show the actual query plan causing an error?

Question

It is curious if someone has experienced this problem before and what is the reason. The problem is that when starting in SQL 2012 with the actual query plan enabled, an error is included. It works in 2008 R2 anyway (with or without a plan) and works in 2012 without including the Plan. I discovered this when testing functionality against partitioned views.

--Setup USE master go DROP DATABASE Test GO CREATE DATABASE Test GO USE Test GO CREATE TABLE DD (pkID int IDENTITY(1,1), FullDate date); INSERT INTO DD (FullDate) VALUES ('2013-01-01') INSERT INTO DD (FullDate) VALUES ('2013-01-02') INSERT INTO DD (FullDate) VALUES ('2013-01-03') INSERT INTO DD (FullDate) VALUES ('2013-01-04') INSERT INTO DD (FullDate) VALUES ('2013-01-05') GO CREATE TABLE DC (pkID int IDENTITY(1,1), Filter varchar(32), FilterGroup varchar(32)); INSERT INTO DC (Filter, FilterGroup) VALUES ('one', 'groupone') INSERT INTO DC (Filter, FilterGroup) VALUES ('two', 'grouptwo') INSERT INTO DC (Filter, FilterGroup) VALUES ('three', 'groupone') GO CREATE TABLE FDA1 (pkID int IDENTITY(1,1), fkpID int, fkCID int, fkDateID int) INSERT INTO FDA1(fkpID, fkCID, fkDateID) VALUES (1,1,1) INSERT INTO FDA1(fkpID, fkCID, fkDateID) VALUES (1,2,1) INSERT INTO FDA1(fkpID, fkCID, fkDateID) VALUES (1,1,3) INSERT INTO FDA1(fkpID, fkCID, fkDateID) VALUES (1,3,5) GO CREATE TABLE FDA2 (pkID int IDENTITY(1,1), fkpID int, fkCID int, fkDateID int) INSERT INTO FDA2(fkpID, fkCID, fkDateID) VALUES (2,1,2) INSERT INTO FDA2(fkpID, fkCID, fkDateID) VALUES (2,2,2) INSERT INTO FDA2(fkpID, fkCID, fkDateID) VALUES (2,1,4) INSERT INTO FDA2(fkpID, fkCID, fkDateID) VALUES (2,3,5) GO CREATE VIEW FDA AS SELECT pkID, fkpID, fkCID, fkDateID FROM FDA1 UNION ALL SELECT pkID, fkpID, fkCID, fkDateID FROM FDA2 GO CREATE FUNCTION GetFilter ( @pID int , @filterGroup varchar(32) ) RETURNS @Filter TABLE ( CID int ) AS BEGIN INSERT INTO @Filter SELECT dc.pkID FROM DC dc WHERE dc.FilterGroup = @filterGroup RETURN END GO CREATE PROC test (@ID int) AS BEGIN BEGIN TRY DECLARE @FilterGroup varchar(32) = 'groupone' SELECT CAST(MIN(dd.FullDate) As datetime) as ProjectReviewStartDate FROM dbo.FDA fda INNER JOIN dbo.DD dd On fda.fkDateID = dd.pkID INNER JOIN dbo.GetFilter(@ID, @FilterGroup) ctl on fda.fkCID = ctl.CID WHERE fda.pkID = @ID OPTION (RECOMPILE); RETURN 0; END TRY BEGIN CATCH --Declare variables for error information. Declare @ErrorMessage nvarchar(max), @ErrorSeverity bigint, @ErrorState int; --Populate error information. Select @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); --Re-throw error to calling method. RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); --Failure RETURN -1; END CATCH; END GO 

Now that the setup is done, let me run the actual code, first "Enable the actual execution plan"

 USE Test GO SET STATISTICS IO ON; SET STATISTICS TIME ON; GO DECLARE @id int = 1 DECLARE @tbl table (dt datetime) DECLARE @findate datetime INSERT @tbl EXEC test @id SELECT @findate = dt FROM @tbl SELECT @findate GO 

You should get the result: 2013-01-01 00: 00: 00.000.

Now turn on “Enable actual execution plan” and you will get a NULL result and see an error (in SQL 2012):

Msg 50000, Level 16, State 10, Test Procedure, Line 33 String or binary data will be truncated.

Including the actual execution plan and removing the Try / Catch block from proc removes the error and returns the correct result. However, this works great in 2008R2.

Any ideas?

thanks

Answer 1

I have no answer for you, just more ammunition that you probably ran into a bug with.

First, to prove that this does not include RAISERROR in particular, changing this line to:

 THROW; 

Instead, returns this message:

Msg 8152, Level 16, State 10, Test Procedure, Line 7
String or binary data will be truncated.

Thus, this shows that the error occurs in the SELECT call and has something to do with the TRY/CATCH combination and shows the actual plan.

Next, see what metadata describes as the result for a stored procedure:

 SELECT name, system_type_name FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID('test'), NULL); 

Results:

 name system_type_name ---------------------- ---------------- ProjectReviewStartDate datetime 

Now let's see what he says about metadata (well, actually, an error message is received) if we try to check the metadata for your party:

 SELECT [error_message] FROM sys.dm_exec_describe_first_result_set(N'DECLARE @id int = 1 DECLARE @tbl table (dt datetime) DECLARE @findate datetime INSERT @tbl EXEC test @id', -1, NULL); 

Result:

 error_message ---------------------------------------------------------- Incorrect syntax near '-'. The batch could not be analyzed because of compile errors. 

No in package. And this is with the execution plan turned off. And you will get the same result, even if you comment out the insert in the package above. Even for example:

 SELECT error_message FROM sys.dm_exec_describe_first_result_set(N'EXEC test 1', -1, NULL); 

The error message changes a bit (look carefully):

 error_message ---------------------------------------------------------- Incorrect syntax near '1'. The batch could not be analyzed because of compile errors. 

We removed any showplan involvement or possible truncation, and we can still demonstrate that SQL Server has some kind of problem compiling and / or generating metadata for this simple batch.

This seems to be a pretty simple option, and you have workarounds, but I would definitely write this to Connect .

change

I see that you have made a mistake; anyone who can reproduce this situation must vote and confirm the reproduction:

http://connect.microsoft.com/SQLServer/feedback/details/785151/show-actual-query-plan-causes-error