In my previous article, I wrote about sp_describe_first_result_set. In this article we will discuss about a new error message (The metadata could not be determined because the statement ‘%.*ls’ is not compatible with the statement ‘%.*ls’) introduced in SQL SERVER 2012. This error message is somehow relates to sp_describe_first_result_set.
Let’s discuss it in detail:
Message Number: 11509
Severity : 16
Error Message: “The metadata could not be determined because the statement ‘%.*ls’ is not compatible with the statement ‘%.*ls’.“
Error Generation:
Let me create an example to generate this error:
USE AdventureWorks2012 GO Declare @Gender as varchar(10)='Female' If (@Gender='Male') Select BusinessEntityID,NationalIDNumber from [HumanResources].[Employee] where [Gender] ='M' Else Select BusinessEntityID,NationalIDNumber,OrganizationLevel from [HumanResources].[Employee] where [Gender] ='F' GO --OUTPUT
Now, you can see that the above query is executed successfully. Lets pass the above query to sp_describe_first_result_set. to view the metadata information of the first possible result set.
Example 1 :
USE AdventureWorks2012 GO Sp_describe_first_result_set @tsql =N' If (@Gender=''Male'') Select BusinessEntityID , NationalIDNumber from [HumanResources].[Employee] where [Gender] =''M'' Else Select BusinessEntityID , NationalIDNumber , OrganizationLevel from [HumanResources].[Employee] where [Gender] =''F''' GO --OUTPUT
Oops……………
Msg 11509, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because the statement ‘Select BusinessEntityID , NationalIDNumber from [HumanResources].[Employee] where [Gender] =’M” is not compatible with the statement ‘Select BusinessEntityID , NationalIDNumber , OrganizationLevel from [HumanResources].[Employee] wher’.
Example 2 :
USE AdventureWorks2012 GO Sp_describe_first_result_set @tsql =N' If (@Gender=''Male'') Select BusinessEntityID , NationalIDNumber from [HumanResources].[Employee] where [Gender] =''M'' Else Select BusinessEntityID , Convert(varchar(50),NationalIDNumber) from [HumanResources].[Employee] where [Gender] =''F''' GO --OUTPUT
Ooops error again ?????
Msg 11509, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because the statement ‘Select BusinessEntityID , NationalIDNumber from [HumanResources].[Employee] where [Gender] =’M” is not compatible with the statement ‘Select BusinessEntityID , Convert(varchar(50),NationalIDNumber) from [HumanResources].[Employee] whe’.
Resolution:
In the above example 1, we have two queries but the number of columns are different in both queries. In first query we have only two columns (BusinessEntityID , NationalIDNumber) and in the second query we have three columns (BusinessEntityID , NationalIDNumber & OrganizationLevel). So, remember whenever the number of columns are different in the first possible result set of sp_describe_first_result_set, it will generate an error.
In the example 2, we have two queries with the same number of columns but the data type is different, in first and second queries we have only two columns (BusinessEntityID , NationalIDNumber) but in the first query the data type of NationalIDNumber is int and in the second query it is varchar. So, remember whenever the data type is different in the first possible result set of sp_describe_first_result_set, it will generate an error.
Conclusion:
Whenever you use sp_describe_first_result_set. make sure that the first possible result set column and data types do not differ.
Reference : MSDN