1

I am getting an error in my stored procedure: "Invalid object name '##{myTempTableName}' for 3 global temporary tables. There are several temp tables used in the procedure, but I am getting this error only for those three - I ran the procedure several times.

The procedure follows a similar pattern for all the temp tables as in the example below but other temp tables work as expected:

DROP TABLE IF EXISTS ##TempTable1

DECLARE @TempTable1 NVARCHAR(MAX) = ''
SELECT
  @TempTable1 =@TempTable1 + 'SUM(CASE WHEN '+AttrName+' IS NULL then 1 else 0 end) as '+AttrName+','+char(13)
FROM  [SYNERISE].[ClientAttrListOfAttr]
WHERE AttrName<>'GroupToExlude'
ORDER BY AttrName

SET @TempTable1=LEFT(@TempTable1, LEN(@TempTable1) - 2)
SET @TempTable1='SELECT '+CHAR(13)+@TempTable1+CHAR(13)+'INTO ##TempTable1 FROM  [SYNERISE].[ClientAttr_RawData_PreviousSnapshot] WHERE ValidToSend=1'

EXEC(@TempTable1)

and later in the same procedure, I am selecting from the temp table to create another one:

DROP TABLE IF EXISTS ##TempTable2

declare @TempTable1Unpivot varchar(max)

set  @TempTable1Unpivot='

SELECT ColumnName, ValueCount 
INTO ##TempTable2
FROM   
   (SELECT '+@AttrList+'
   FROM ##TempTable1) p  
UNPIVOT  
   (ValueCount FOR ColumnName IN   
      ('+@AttrList+')  
) AS unpvt;  
'

I do not understand how it is possible that the ##TempTable1 object is not found, when it is created in the same procedure and there is no delete statement between the creation and selecting from the table.

I checked that this is the only procedure that refers to the temporary tables by the names which I am getting in the error message and there was no other process running the same procedure in parallel.

The database is Azure SQL Hyperscale.

Has anyone encountered this error before? Could you suggest other troubleshooting steps?

2
  • Looks like it may not be supported? See blog.pragmaticworks.com/… Jan 30, 2023 at 16:05
  • It was not supported in the past but not anymore. My procedure worked until recently with the global temporary tables without issues. And the strange thing is that only three tables are causing the error but there are more of them inside the procedure and they seem to be OK. Feb 2, 2023 at 8:04

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.