Feeds:
Posts
Comments

Posts Tagged ‘Msg 11538 Level 16 State 1’

EXECUTE statement failed because its WITH RESULT SETS clause specified type ‘%ls’ for column #%d in result set #%d, and the corresponding type sent at run time was ‘%ls’; there is no conversion between the two types is one of the new error messages seen in SQL Server 2012. This error message is due to the invalid data type usage in the new feature EXEC WITH RESULT SETS.

Let’s discuss this in detail:
Message Number: 11538

Severity : 16

Error Message: EXECUTE statement failed because its WITH RESULT SETS clause specified type ‘%ls’ for column #%d in result set #%d, and the corresponding type sent at run time was ‘%ls’; there is no conversion between the two types.

Error Generation:
Let me create a sample stored procedure to generate this error.

USE tempdb
GO
Create Procedure Usp_Resultset
As
Select 1 as [Number]
GO
EXEC Usp_Resultset
GO
--OUTPUT

errormessage11538.1.1

You can see that the above stored procedure is executed successfully and it has ONE result sets with int data type column.
Lets try to execute it using WITH RESULT SETS clause.

USE tempdb
GO
EXEC Usp_Resultset
WITH RESULT SETS
(([Number] xml
));
--OUTPUT
Msg 11538, Level 16, State 1, Procedure Usp_Resultset, Line 3
EXECUTE statement failed because its WITH RESULT SETS clause specified type ‘xml’ for column #1 in result set #1, and the corresponding type sent at run time was ‘int’; there is no conversion between the two types.

Ooops…… I am unable to execute it. It returned the error message.

errormessage11538.1.2

Resolution:
Why this error ? Because, the above stored procedure returns one result sets with int data type column but we defined  XML Data type column in WITH RESULT SETS clause. This is true that you can change the data type & column name in result sets, but the issue is, we must look at the data type compatibility as well. And as we know, int & xml data types are not compatible with each other. Lets rewrite the script and define int data type column (or any data type that is compatible with int) in the result sets to avoid this error.

USE tempdb
GO
EXEC Usp_Resultset
WITH RESULT SETS
(([Number] int
));
--OUTPUT

errormessage11538.1.3

Conclusion :
Remember, whenever you use any stored procedure using WITH RESULT SETS clause, make sure you use data type in the result sets that is compatible with the source column data type to avoid such errors discussed above.

Advertisements

Read Full Post »