In my earlier article, I had written 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’ in procedure ‘%.*ls’ is not compatible with the statement ‘%.*ls’ in procedure ‘%.*ls’.) introduced in SQL SERVER 2012. This error message is somehow related to sp_describe_first_result_set.
Let’s discuss it in detail:
Message Number: 11512
Severity : 16
Error Message: “The metadata could not be determined because the statement ‘%.*ls’ in procedure ‘%.*ls’ is not compatible with the statement ‘%.*ls’ in procedure ‘%.*ls’.“
Error Generation:
Let me create an example to generate this error:
USE tempdb
GO
--DROP TABLE tbl_customer
--GO
CREATE TABLE tbl_customer
(
[Customer ID] varchar(6),
[Customer Name] nvarchar(100),
[Customer Address] varchar(250)
)
GO
--DROP TABLE tbl_vendor
--GO
CREATE TABLE tbl_vendor
(
[Vendor ID] int,
[Vendor Name] nvarchar(100)
)
GO
--DROP PROCEDURE usp_sample_1
--GO
CREATE PROCEDURE usp_sample_1
@Type varchar(10)
AS
IF @Type='customer'
BEGIN
SELECT [Customer ID],[Customer Name],[Customer Address]
FROM tbl_customer
END
ELSE
BEGIN
SELECT [Vendor ID],[Vendor Name] FROM tbl_vendor
END
GO
In the above example, we created one (usp_sample_1) stored procedure. Let’s view metadata information of the first possible result set using sp_describe_first_result_set. But do not forget that its has two result sets.
Example:
USE tempdb
GO
EXEC sp_describe_first_result_set @tsql= N'usp_sample_1'
GO
--OUTPUT
Ooops error
Msg 11512, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because the statement ‘SELECT [Customer ID],[Customer Name],[Customer Address]
FROM tbl_customer’ in procedure ‘usp_sample_1’ is not compatible with the statement ‘SELECT [Vendor ID],[Vendor Name] FROM tbl_vendor’ in procedure ‘usp_sample_1’.
Resolution:
In the above example, we tried to find metadata of the first possible result set of the Stored procedure (usp_sample_1) and came across this error. The reason behind this error is, this procedure has two result sets and the first problem with this stored procedure is that both result sets have different number of columns and secondly the data type in both result sets is also different. When scrutinised, we can see that the first and second result sets have three columns ([Customer ID],[Customer Name],[Customer Address]) & two columns ([Vendor ID],[Vendor Name]) respectively and also the first column of the first result set data type is [Customer ID] varchar and the first column of the second result set data type [Vendor ID] is int. So, remember whenever the data type & number of columns are different in the first possible result set of sp_describe_first_result_set, it will generate this error.
Let’s rewrite the procedure again and view its metadata. Given below is the script.
--DROP PROCEDURE usp_sample_1
--GO
CREATE PROCEDURE usp_sample_1
@Type varchar(10)
AS
IF @Type='customer'
BEGIN
SELECT [Customer ID],[Customer Name]--,[Customer Address]
FROM tbl_customer
END
ELSE
BEGIN
SELECT Convert(varchar(6),[Vendor ID]),[Vendor Name] FROM tbl_vendor
END
GO
EXEC sp_describe_first_result_set @tsql= N'usp_sample_1'
GO
--OUTPUT
Conclusion:
Whenever you use sp_describe_first_result_set. make sure that the first possible result set column and data type do not differ.
Reference : MSDN
Read Full Post »
SQL Server Portal 