Invalid object Name #TempTable - Sproc error

SQL Agent job which executes a powershell snippet which calls a sproc, it was running fine for a while. Now its started to complain about the #temp table drop statement in the beginning of the Sproc.

PowerShell is: 'Invalid object name '#OwnerIds'. Invalid object name '#OwnerIds'. '. Process Exit Code 0. The step succeeded.

Any ideas please....

Thanks

Would be useful if you can post the statement(s). My initial guess would be that your statement needs to check if the table exists before dropping it, especially since you say that is the first statement in the powershell script.

DROP TABLE IF EXISTS #OwnerIds

However, usually when you try to drop a table that does not exist, the error message from T-SQL is different - something to the effect that the table does not exists.

  • SQL Agent job execute a powershell snippet which in turn calls a sql script

  • sql script excutes a Sproc

  • Sproc has the following code on the top bit:

IF object_id('tempdb..#OwnerIds','u') IS NOT NULL
BEGIN
DROP TABLE #OwnerIds
END
IF object_id('tempdb..#PMIDs','u') IS NOT NULL
BEGIN
DROP TABLE #PMIDs
END
IF object_id('tempdb..#SavedReportIds','u') IS NOT NULL
BEGIN
DROP TABLE #SavedReportIds
END
IF object_id('tempdb..#NoteIds','u') IS NOT NULL
BEGIN
DROP TABLE #NoteIds
END
IF object_id('tempdb..#OwnerEmailAttachments','u') IS NOT NULL
BEGIN
DROP TABLE #OwnerEmailAttachments
END

This was running fine for a while and started failing last two days.

Point to note: when the SPROC run manually works fine in SSMS. Only fails via the SQL Agent job.
SQL Server version 2016 on 2008 compat mode. Running ok since last April 22.