November 2, 2017 at 2:48 pm
I have a stored procedure which uses CTE and a temp table. I want to store the results of this stored procedure into a temp table.
I am using an open query to perform this task.
select * into ##TempTable
from openquery(ServerName,'SET NOCOUNT ON;SET FMTONLY OFF ; exec StoredProcedure;')
The above query is working fine for all the stored procedures but it is failing for the stored procedures which use CTE and a temp table.
Error is: -
Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 10]
The metadata could not be determined because statement 'WITH aa
AS
( SELECT * FROM aa)' uses a temp table.
November 2, 2017 at 3:11 pm
RkKar - Thursday, November 2, 2017 2:48 PMI have a stored procedure which uses CTE and a temp table. I want to store the results of this stored procedure into a temp table.
I am using an open query to perform this task.select * into ##TempTable
from openquery(ServerName,'SET NOCOUNT ON;SET FMTONLY OFF ; exec StoredProcedure;')The above query is working fine for all the stored procedures but it is failing for the stored procedures which use CTE and a temp table.
Error is: -
Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 10]
The metadata could not be determined because statement 'WITH aa
AS
( SELECT * FROM aa)' uses a temp table.
Yes...since SQL Server 2012, openquery started to use sp_describe_first_result_set which doesn't support temporary tables. I would guess that's possibly why you tried to use set fmtonly off. You could try using with result sets to describe the expected columns, result set.
This link is a good article about this issue as well as an example and explanation of using with result sets - check the metadata blues section:
How to Share Data between Stored Procedures
Sue
November 2, 2017 at 3:22 pm
Nevermind, bad advice.
November 2, 2017 at 3:33 pm
Sue_H - Thursday, November 2, 2017 3:11 PMRkKar - Thursday, November 2, 2017 2:48 PMI have a stored procedure which uses CTE and a temp table. I want to store the results of this stored procedure into a temp table.
I am using an open query to perform this task.select * into ##TempTable
from openquery(ServerName,'SET NOCOUNT ON;SET FMTONLY OFF ; exec StoredProcedure;')The above query is working fine for all the stored procedures but it is failing for the stored procedures which use CTE and a temp table.
Error is: -
Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 10]
The metadata could not be determined because statement 'WITH aa
AS
( SELECT * FROM aa)' uses a temp table.Yes...since SQL Server 2012, openquery started to use sp_describe_first_result_set which doesn't support temporary tables. I would guess that's possibly why you tried to use set fmtonly off. You could try using with result sets to describe the expected columns, result set.
This link is a good article about this issue as well as an example and explanation of using with result sets - check the metadata blues section:
How to Share Data between Stored ProceduresSue
Thank you Sue,
But the stored procedure got 500 columns in it.
November 2, 2017 at 3:38 pm
RkKar - Thursday, November 2, 2017 3:33 PMBut the stored procedure got 500 columns in it.
Doesn't change Sue's answer, just going to make it a lengthy change to make.
Or, change the procedure to eliminate the temp table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2017 at 3:44 pm
RkKar - Thursday, November 2, 2017 3:33 PMSue_H - Thursday, November 2, 2017 3:11 PMRkKar - Thursday, November 2, 2017 2:48 PMI have a stored procedure which uses CTE and a temp table. I want to store the results of this stored procedure into a temp table.
I am using an open query to perform this task.select * into ##TempTable
from openquery(ServerName,'SET NOCOUNT ON;SET FMTONLY OFF ; exec StoredProcedure;')The above query is working fine for all the stored procedures but it is failing for the stored procedures which use CTE and a temp table.
Error is: -
Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 10]
The metadata could not be determined because statement 'WITH aa
AS
( SELECT * FROM aa)' uses a temp table.Yes...since SQL Server 2012, openquery started to use sp_describe_first_result_set which doesn't support temporary tables. I would guess that's possibly why you tried to use set fmtonly off. You could try using with result sets to describe the expected columns, result set.
This link is a good article about this issue as well as an example and explanation of using with result sets - check the metadata blues section:
How to Share Data between Stored ProceduresSue
Thank you Sue,
But the stored procedure got 500 columns in it.
Then you need to find a way to not use the temp table. I'd do another post and ask if anyone has any ideas on how to get around using the temp table. You would need to post the DDL, sample data, etc. Not sure how thrilled anyone would be to see a 500 column table or if they all are actually needed - like the reports that return millions and millions of rows. Who knows....there could be a way to rework some of that and there are some incredibly talented folks up here who could help out with that.
Sue
November 2, 2017 at 4:04 pm
Sue,
Data types for the data columns are just varchar, int and date.
My actual requirement is to compare column by column the outputs of below queries
exec StoredProcedure1 '2017-11-02'
exec StoredProcedure1 '2017-11-01'
In order to do that, i wrote a query which will dump the data into two temp tables and compare the column by column data and display as a match or no match.
Outpu1.column1=output.column2 then Match else no Match
November 2, 2017 at 4:07 pm
GilaMonster - Thursday, November 2, 2017 3:38 PMRkKar - Thursday, November 2, 2017 3:33 PMBut the stored procedure got 500 columns in it.Doesn't change Sue's answer, just going to make it a lengthy change to make.
Or, change the procedure to eliminate the temp table.
The fast way of doing this is to
SELECT * INTO dbo.tb1
FROM #TempTable
WHERE 1 = 0
And then get the create script for dbo.tb1, and then plug that back into your WITH RESULT SETS clause.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 2, 2017 at 5:33 pm
Phil Parkin - Thursday, November 2, 2017 4:07 PMGilaMonster - Thursday, November 2, 2017 3:38 PMRkKar - Thursday, November 2, 2017 3:33 PMBut the stored procedure got 500 columns in it.Doesn't change Sue's answer, just going to make it a lengthy change to make.
Or, change the procedure to eliminate the temp table.The fast way of doing this is to
SELECT * INTO dbo.tb1
FROM #TempTable
WHERE 1 = 0And then get the create script for dbo.tb1, and then plug that back into your WITH RESULT SETS clause.
Phil,
I can't create the temp table.
November 2, 2017 at 5:41 pm
RkKar - Thursday, November 2, 2017 5:33 PMPhil Parkin - Thursday, November 2, 2017 4:07 PMThe fast way of doing this is to
SELECT * INTO dbo.tb1
FROM #TempTable
WHERE 1 = 0And then get the create script for dbo.tb1, and then plug that back into your WITH RESULT SETS clause.
Phil,
I can't create the temp table.
Not even on the remote server?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 3, 2017 at 7:34 am
Yes Phil,
I get the below error.
Error is: -
Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 10]
The metadata could not be determined because statement 'WITH aa
AS
( SELECT * FROM aa)' uses a temp table.
since SQL Server 2012, openquery started to use sp_describe_first_result_set which doesn't support temporary tables. Credits to Sue.
November 3, 2017 at 7:43 am
RkKar - Friday, November 3, 2017 7:34 AMYes Phil,
I get the below error.
Error is: -Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 10]
The metadata could not be determined because statement 'WITH aa
AS
( SELECT * FROM aa)' uses a temp table.since SQL Server 2012, openquery started to use sp_describe_first_result_set which doesn't support temporary tables. Credits to Sue.
OK, my apologies for not being 100% clear about what I was suggesting.
You have a proc on a remote server which produces a result set, by selecting from a temporary table which contains many columns.
My suggestion is as follows:
1) Log in to the remove server
2) Paste the guts of your proc into a new query window (so it is now just a script, not a proc)
3) Just after the temp table has been created, add the SELECT INTO code mentioned in my earlier post to create an empty permanent table with the desired column names and data types.
4) Execute the script, up to and including the SELECT INTO.
5) Script the new permanent table as CREATE
6) Move back to your local server and use the results of (5) in your WITH RESULT SETS definition.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 3, 2017 at 12:06 pm
Thank you, Phil. This workaround works for me.
Thank you so much.
1. Executed the query instead of Stored Procedure and dumped data into a temp table.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply