SQL Server Portal

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

Read Full Post »