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! Could anyone please help me fix this problem?
Your help would be much appreciated. Thanks in advance!