SQL Server Portal

sp_describe_first_result_set cannot be invoked when SET STATISTICS XML or SET STATISTICS PROFILE is on is one of the new error messages shipped with SQL Server 2012. This error message is related to sp_describe_first_result_set , a new system stored procedure shipped with SQL Server 2012 as well.

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

Severity : 16

Error Message: sp_describe_first_result_set cannot be invoked when SET STATISTICS XML or SET STATISTICS PROFILE is on

Error Generation:

Given below is the script that I tried to execute but it gave me this error.

Use AdventureWorks2012
GO
Set Statistics XML ON
Set Statistics Profile ON

EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vEmployeeDepartment];'
,@params=NULL,@browse_information_mode=0
GO

Msg 11541, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
sp_describe_first_result_set cannot be invoked when SET STATISTICS XML or SET STATISTICS PROFILE is on.

Ooopps…… I am unable to execute it.

Resolution:
Sometimes , you don’t even see given below two lines but may be you turn it ON  for another query and forget to turn it OFF.

Set Statistics XML ON
Set Statistics Profile ON

What all you need to do is just switch off XML and profile statistics. Given below is the script.

Use AdventureWorks2012
GO
Set Statistics XML OFF
Set Statistics Profile OFF
EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vEmployeeDepartment];'
,@params=NULL,@browse_information_mode=0
GO
--OUTPUT

Conclusion :
Remember, whenever you use sp_describe_first_result_set, make sure XML and profile statistics is turned OFF.