1

I have the below SQL statement that I was using to get data from a stored procedure into a temp table 

 SET @sql = N'SELECT * INTO #CustomTable3HTML      
             FROM
                 OPENROWSET(''SQLNCLI'',''Server='+ @@SERVERNAME +';UID='+@UserName+';PWD='+@Password+';Database=SalesDeals'',
      ''SET NOCOUNT ON;SET FMTONLY OFF;EXEC  dbo.prGetDealProposalDetail ''''' + @DealID + ''''',''''' + @FeeType +''''''')
     '     
EXEC sp_executesql @sql

This was working fine in SQL Server 2008, but after the server got upgraded to SQL Server 2014, I started getting this error:

The metadata could not be determined because statement 'Insert #TempDetail exec prExcelCalculations_GetPricingSummary @Dealno,'FSA'' in procedure 'prGetDealProposalDetail' uses a temp table.

Which is because in the stored procedure I call in the Openrowset calls another procedure and uses temp tables..The columns are dynamic.

Can someone please let me know what can I do to fix it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SP1
  • 1,182
  • 3
  • 22
  • 47
  • There are multiple suggestions in comments if the answer isn't sufficient... https://stackoverflow.com/questions/18346484/ssis-package-not-wanting-to-fetch-metadata-of-temporary-table – Eric J. Price Nov 05 '18 at 19:15

0 Answers0