SQL Server Portal

Today, I installed a SQL Server instance in one of my labs, and tried to run a query using OPENROWSET and got an error. Given below are the details of the error.

Message Number: 15281

Severity : 16

Error Message: SQL Server blocked access to %S_MSG ‘%ls’ of component ‘%.*ls’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘%.*ls’ by using sp_configure. For more information about enabling ‘%.*ls’, see “Surface Area Configuration” in SQL Server Books Online.

Error Generation:

Given below is the script that I tried to execute but it resulted with the following error:

USE AdventureWorks2012
GO
SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=(local);uid=test;pwd=test',
'EXEC AdventureWorks2012.[dbo].[uspGetManagerEmployees] 16');

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

Ooopps…… I am unable to execute the OPENROWSET query.

Resolution:
Basically, ‘Ad hoc Distributed Queries’ are disabled by default in SQL Server due to security configuration and you cannot use either OPENROWSET or OPENDATASOURCE and if you cannot execute these two rowset functions, then you cannot access any remote data sources. So how to fix this issue?
Given below is the script to enable ‘Ad hoc Distributed Queries’.

USE master
GO
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
--OUTPUT

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Ad hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.

As you can see above, the ‘Ad hoc Distributed Queries’ setting changed from 0 to 1. Now you can easily execute any ‘Ad hoc Query’

Note : By default, this option is set to 0 and you need to change it to 1 to activate this feature.

Read Full Post »

We usually use temporary table to keep temporary data in the stored procedure, it can be local, global or a table variable. Recently, I was working on the stored procedures’ metadata in a client database using sp_describe_first_result_set (a new system stored procedure shipped with SQL Server 2012). Ooopps….. there’s an error, let me explain the error in detail. This error message is available in SQL Server 2012 and above.

Message Number: 11526

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ in procedure ‘%.*ls’ uses a temp table.

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

USE tempdb
GO
--DROP PROCEDURE [dbo].[usp_sample]
--GO
CREATE PROCEDURE [dbo].[usp_sample]
AS
BEGIN
CREATE TABLE #temp_Sample
(
[ID] INT,
[NAME] VARCHAR(32)
)
INSERT INTO #temp_Sample VALUES (1,'Bob')

SELECT * FROM #temp_Sample
END
GO

EXEC [dbo].[usp_sample]
GO
--OUTPUT

You can see that I have created a stored procedure and executed it as well and got the result set. Now I would like to view the metadata of the above stored procedure. Given below is the script to view the metadata using sp_describe_first_result_set

USE tempdb
GO
Sp_describe_first_result_set @tsql =N'usp_sample'
GO
--OUTPUT

Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘INSERT INTO #temp_Sample VALUES (1,’Bob’)’ in procedure ‘usp_sample’ uses a temp table.

Ooopps…… I am unable to execute it.

Resolution:
The reason behind this error is, you used a local temporary table in the stored procedure and you cannot view the metadata using sp_describe_first_result_set. So what to do ? Very simple guys, just convert local temporary table to table variable and view its metadata.
Given below is the correct script.

USE tempdb
GO
--DROP PROCEDURE [dbo].[usp_sample]
--GO
CREATE PROCEDURE [dbo].[usp_sample]
AS
BEGIN
DECLARE @temp_Sample TABLE
(
[ID] INT,
[NAME] VARCHAR(32)
)
INSERT INTO @temp_Sample VALUES (1,'Bob')

SELECT * FROM @temp_Sample
END
GO

Sp_describe_first_result_set @tsql =N'usp_sample'
GO
--OUTPUT

Conclusion :
Remember, whenever you use temporary table in the stored procedure and would like to view the metadata of that stored procedure using sp_describe_first_result_set,  always convert local temporary table to table variable.

Read Full Post »