Error Inserting Results into Table from a Stored Procedure with Dynamic SQL

Question

I have a stored procedure which does dynamic pivot task. I want the results of this procedure to be loaded into a table. This table either could be emptied/dropped prior to loading.

Here is my code that I used for the task. But I receive the following error.

Dynamic Pivot Error in sql server

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 The metadata could not be determined because statement 'exec sp_executesql @query;' in procedure 'DynamicPivotProcedure' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set

Code:

create procedure dbname.schemaname.DynamicPivotProcedure 
as
begin
DECLARE @cols  AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

SELECT @cols = Stuff((SELECT ',' + Quotename([check])
                      FROM   TEST
                      GROUP  BY [Check]
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = N'SELECT ID, [Total Of Score],' + @cols + N' from 
             (SELECT TEST.[ID],
                     Score,
                     [check],
                     [Total Of Score] = Count(TEST.Score) over(partition by [ID], [score], [check])
             FROM TEST) T
            pivot 
            (
             SUM (T.[score])
             for T.[check] in (' + @cols + N') ) p '

EXEC Sp_executesql @query; 
end

go
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL 
begin
DROP TABLE #MyTempTable

end

SELECT * INTO #MyTempTable FROM 
OPENROWSET('SQLNCLI', 'Server= ABC124;Trusted_Connection=yes;',
     'EXEC DATABASE.Schemaname.DynamicPivotProcedure')

SELECT * FROM #MyTempTable

`'(local)\SQL2008'` is this your instance or server name. what is the result of @@servername. — Pரதீப், Dec 27 '14 at 01:58
@NoDisplayName I edited the question. I used the @@servername and get the same error — WiredTheories, Dec 27 '14 at 17:05
No dude Run this `select @@ServerName` you will get a result you need use that result in `Server=....` — Pரதீப், Dec 27 '14 at 17:07
it should not be `EXEC DATABASE.DynamicPivotProcedure` it should be `EXEC DATABASEName.SchemaName.DynamicPivotProcedure` — Pரதீப், Dec 27 '14 at 17:22
Edited the question , after making the necessary changes. The error is now more to do with the Dynamic SQL. — WiredTheories, Dec 27 '14 at 17:29
@NoDisplayName After researching a little. I come across this [WITH RESULT SETS" clause in SQL 2012 for dynamic column names](https://social.msdn.microsoft.com/Forums/en-US/4e98380a-df92-49a0-97d8-4908307573c4/how-to-use-with-result-sets-clause-in-sql-2012-for-dynamic-column-names?forum=transactsql) — WiredTheories, Dec 27 '14 at 17:42

score 0 · Answer 1 · answered Dec 26 '14 at 18:25

0

You do not need this part :

    go
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

The error bellow looks like a connection error . SSMS is not able to connect to your SQLServer instamce. Open a new SSMS , connect to the SQL server, open a new query and paste the code above (without the sp_configure part)

answered Dec 26 '14 at 18:25

Lan

1,335
1
9
14

I tried that and I still get the same error. I'm running it on the local stance. I'm new to Openrowset. Also dont understand the linking server error – WiredTheories Dec 27 '14 at 17:08

Error Inserting Results into Table from a Stored Procedure with Dynamic SQL

1 Answers1