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!