SQLTeam.com | Weblogs | Forums

SP Migration Problem (2008 to 2014): Metadata could not be determined


#1

I have the following stored procedure that calls a stored procedure passed as a parameter (@SPCall) and saves the result set returned by the called stored procedure into a temp table passed as a parameter (@TableName):

ALTER PROCEDURE [dbo].[SPtoTable]
(
	@TableName VARCHAR(70),
	@SPCall VARCHAR(MAX)
)
AS
BEGIN
	DECLARE @ProviderName VARCHAR(25)
	DECLARE @ConnectionString VARCHAR(700)
	DECLARE @RowsetSQL VARCHAR(MAX)
	DECLARE @Cmd VARCHAR(MAX)

	SET @ProviderName = '''SQLOLEDB.1'''

	SET @ConnectionString =
		'''server=' +
			Convert(VarChar(70),SERVERPROPERTY('ServerName')) +
			IsNull('\' + Convert(VarChar(70),SERVERPROPERTY('InstanceName')), '') +
		';trusted_connection=yes'''

	SET @RowsetSQL = '''EXEC ' + REPLACE(@SPCall, '''', '''''') + ''''

	SET @Cmd = 'DROP TABLE ' + @TableName
	
	WHILE EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(@TableName) AND type = 'U')
		EXEC (@Cmd)

	SET @Cmd = 'SELECT * INTO ' + @TableName + ' FROM OPENROWSET(' + @ProviderName + ',' + @ConnectionString + ',' + @RowsetSQL + ')'

	EXEC (@Cmd)
END

The stored procedure runs fine in SQL Server 2008, but now that I've migrated it to SQL Server 2014, I get the following error:

Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 81
The metadata could not be determined because statement 'EXEC (@Cmd)' in procedure 'SPtoTable' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

This error happens only when the called store procedure passed in @SPCall is also calling the stored procedure SPtoTable. When the called stored procedure is a simple SELECT statement, the error does not happen. For example, let's say I do this command:

EXEC SPtoTable 'UnworkedAccts', 'ABS.dbo.INSURANCE_UNWORKEDACCTS'

And the stored procedure ABS.dbo.INSURANCE_UNWORKEDACCTS in itself has the following command:

EXEC SPtoTable 'ClaimPaid', 'ABS.dbo.INSURANCE_CLAIMPAID_REPORT'

That's when the error occurs.

I am so stumped! :frowning: Could anyone please help me fix this problem?

Your help would be much appreciated. Thanks in advance!


#2

Doesn't a #TEMP table have the same problem?

We rarely use @TableVariable ones any more ... we got all excited when they first came out, converted lots of code, and then found there was rarely any advantage but in place big disadvantages.


#3

Thanks for the suggestion, Kristen. Unfortunately, using a #Temp table gives me the same error.

Actually, the @TableName parameter in the stored procedure is a varchar, not a table variable. It is simply used to specify the name of the table to which the result set of the called stored procedure will be saved. This table is first deleted if it exists, then a SELECT INTO this table is done to create the table and save to it the result set of the called stored procedure (@SPCall) invoked by OPENROWSET.

I found the following discussion which relates to a similar problem but in the context of SSIS, and I'm not sure if it applies to my particular situation:

https://www.sqlservercentral.com/Forums/Topic1430451-3077-1.aspx


#4

Add the following to the stored procedures:

SET NOCOUNT ON;

That should remove the 'additional' result sets being returned from the stored procedure calls. Essentially - SQL Server cannot determine which result set you want because the call is returning the number of rows affected.

With that said...I would recommend that you don't create a stored procedure and then call something like this to get a table. If you need a table then you might as well use a table valued function - and if you can write the code simply enough to be just returning a SELECT statement then an inline-table valued function will perform better than this process.


#5

Adding SET NOCOUNT ON did not fix the problem. The same error is being returned.

Thanks for the suggestion though!


#6

Sorry, misunderstood your O/P.

I agree with Jeff that it all looks a bit complicated - often there is more code / complexity than a simplified post-for-help conveys, but: can it be simplified / done differently?

Whether that is necessary, or not, if this is some sort of bug, or a change of how-it-works side effect, related to the newer version then recoding will be a better solution than waiting for a SQL Patch!


#7

It's not really complicated. It's a very handy stored procedure and an example will illustrate that. Suppose I have five stored procedures, spReport1, spReport2, spReport3, spReport4, and spReport5, that produce record sets for five different reports. I would like to make a consolidated summary of the data from the five reports. I could easily do it this way:

EXEC SPtoTable 'TempReport1', 'ABS.dbo.spReport1'
EXEC SPtoTable 'TempReport2', 'ABS.dbo.spReport2'
EXEC SPtoTable 'TempReport3', 'ABS.dbo.spReport3'
EXEC SPtoTable 'TempReport4', 'ABS.dbo.spReport4'
EXEC SPtoTable 'TempReport5', 'ABS.dbo.spReport5'

SELECT AccountNo, AccountName, Sum(Sales) AS TotalSales
FROM
(
SELECT AccountNo, AccountName, Sales FROM TempReport1
UNION ALL
SELECT AccountNo, AccountName, Sales FROM TempReport2
UNION ALL
SELECT AccountNo, AccountName, Sales FROM TempReport3
UNION ALL
SELECT AccountNo, AccountName, Sales FROM TempReport4
UNION ALL
SELECT AccountNo, AccountName, Sales FROM TempReport5
) Reports
GROUP BY AccountNo, AccountName
ORDER BY AccountNo, AccountName

Thus, I am able to produce the consolidated summary with just a few lines of code. And the beauty of this is that if ever I make changes to any of the five stored procedures, I don't have to change the code above that produces the consolidated summary.

Oh well, I guess I'll have to recode to avoid calling SPToTable with a stored procedure (parameter) that itself calls the SPToTable stored procedure (a recursive call). But before I do that, I wonder if I could change the @ProviderName in the SPToTable stored procedure from "SQLOLEDB.1" to some other provider which, hopefully, will not give me this problem that I'm having. Can you please suggest what other provider names I could try?

Thanks! :slight_smile:


#8

Sorry, I wasn't clear in my description. It was the use of OpenRowSet and having to have a connection string, that caused me to use the word "complicated".

I'd prefer to do it a more direct way, but of course if the columns returned by SomeSProc cannot be known ahead of time that is a problem. For example, I would give the SProc an extra @Parameter with the name of the table I want it to save the results to (if NULL then just output them as a SELECT). That could be extended to SProcs that want to return 2+ resultsets.