SQL There is already an object named '#TEMP' in the database after updated to SAP PL-16

Question

I am working with SQL 2008 R2 and we updated it today with a SAP update PL-16. After that we are getting issue in my previous developed application.

We are getting errors in #temp tables.

ERROR:

[SQL Server Native 10.0][SQL Server]There is already an object named '#TEMP' in the database] FMS

We tried with a solution, insert 'drop table #temp' in the beginning of every query...

IF (SELECT object_id('TempDB..#Temp')) IS NOT NULL
BEGIN
    DROP TABLE #Temp
END

It still prompts the same error. Any help?

The Big Query for reference :(

SELECT CASE
           WHEN (CONVERT(FLOAT,$[$38.11]) >0
                 AND convert(float,$[$38.11],2) >= convert(float,t0.[U_FQuantity],2)
                 AND convert(float,$[$38.11],2) <= convert(float,t0.[U_LQuantity],2)) THEN T0.[U_Price2] *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
           WHEN (CONVERT(FLOAT,$[rdr1.U_mishkal],2) >0
                 AND $[rdr1.U_mishkal] >= t0.[U_FWeight]
                 AND $[rdr1.U_mishkal] <= t0.[U_LWeight]) THEN T0.[U_Price2] *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
           WHEN (CONVERT(FLOAT,$[rdr1.U_kmNsiaa],2) >0
                 AND $[rdr1.U_kmNsiaa] >= t0.[U_Fkm]
                 AND $[rdr1.U_kmNsiaa] <= t0.[U_Lkm]) THEN T0.[U_Price2] *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
           WHEN (CONVERT(FLOAT,$[rdr1.U_Nefach],2) >0
                 AND $[rdr1.U_Nefach] >= t0.[U_FVolume]
                 AND $[rdr1.U_Nefach] <= t0.[U_LVolume]) THEN T0.[U_Price2] *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
           WHEN (CONVERT(FLOAT,$[rdr1.U_Point],2) >0
                 AND $[rdr1.U_Point] >= t0.[U_FPoint]
                 AND $[rdr1.U_Point] <= t0.[U_LPoint]) THEN T0.[U_Price2] *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
           WHEN (CONVERT(FLOAT,$[rdr1.U_Hamtana],2) >0
                 AND $[rdr1.U_Hamtana] >= t0.[U_FHamtana]
                 AND $[rdr1.U_Hamtana] <= t0.[U_LHamtana]) THEN T0.[U_Price2] *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
       END AS 'price' INTO #TEMP
FROM [dbo].[@PRICELIST] T0
INNER JOIN [dbo].[OITM] T1 ON T0.[U_ItemCode]=T1.[U_ZPRICELIST]
WHERE T0.[U_Cardcode] = $[ORDR.U_PCARDCODE.0]
  AND T1.[ItemCode] = $[$38.1]
  AND T0.[U_SugMitan] =$[rdr1.U_SugMitan.1]
  AND T0.[U_SugRehev] = $[Rdr1.U_SugRehev.1]
  AND t0.U_SugHishuv='2'
  INSERT INTO #TEMP

  SELECT CASE
             WHEN (CONVERT(FLOAT,$[$38.11]) >0
                   AND convert(float,$[$38.11],2) >= convert(float,t0.[U_FQuantity],2)
                   AND convert(float,$[$38.11],2) <= convert(float,t0.[U_LQuantity],2)) THEN T0.[U_Price2]/(CONVERT(FLOAT,$[$38.11],2)) *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
             WHEN (CONVERT(FLOAT,$[rdr1.U_mishkal],2) >0
                   AND $[rdr1.U_mishkal] >= t0.[U_FWeight]
                   AND $[rdr1.U_mishkal] <= t0.[U_LWeight]) THEN T0.[U_Price2]/(CONVERT(FLOAT,$[$38.11],2)) *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
             WHEN (CONVERT(FLOAT,$[rdr1.U_kmNsiaa],2) >0
                   AND $[rdr1.U_kmNsiaa] >= t0.[U_Fkm]
                   AND $[rdr1.U_kmNsiaa] <= t0.[U_Lkm]) THEN T0.[U_Price2]/(CONVERT(FLOAT,$[$38.11],2)) *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
             WHEN (CONVERT(FLOAT,$[rdr1.U_Nefach],2) >0
                   AND $[rdr1.U_Nefach] >= t0.[U_FVolume]
                   AND $[rdr1.U_Nefach] <= t0.[U_LVolume]) THEN T0.[U_Price2] /(CONVERT(FLOAT,$[$38.11],2)) *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
             WHEN (CONVERT(FLOAT,$[rdr1.U_Point],2) >0
                   AND $[rdr1.U_Point] >= t0.[U_FPoint]
                   AND $[rdr1.U_Point] <= t0.[U_LPoint]) THEN T0.[U_Price2] /(CONVERT(FLOAT,$[$38.11],2)) *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
             WHEN (CONVERT(FLOAT,$[rdr1.U_Hamtana],2) >0
                   AND $[rdr1.U_Hamtana] >= t0.[U_FHamtana]
                   AND $[rdr1.U_Hamtana] <= t0.[U_LHamtana]) THEN T0.[U_Price2] /(CONVERT(FLOAT,$[$38.11],2)) *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
         END AS 'price'
  FROM [dbo].[@PRICELIST] T0
  INNER JOIN [dbo].[OITM] T1 ON T0.[U_ItemCode]=T1.[U_ZPRICELIST] WHERE T0.[U_Cardcode] = $[ORDR.U_PCARDCODE.0]
  AND T1.[ItemCode] = $[$38.1]
  AND T0.[U_SugMitan] =$[rdr1.U_SugMitan.1]
  AND T0.[U_SugRehev] = $[Rdr1.U_SugRehev.1]
  AND t0.U_SugHishuv='1'
  INSERT INTO #TEMP

  SELECT CASE
             WHEN (CONVERT(FLOAT,$[$38.11]) >0
                   AND convert(float,$[$38.11],2) >= convert(float,t0.[U_FQuantity],2)
                   AND convert(float,$[$38.11],2) <= convert(float,t0.[U_LQuantity],2)) THEN T0.[U_Price2] *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
             WHEN (CONVERT(FLOAT,$[rdr1.U_mishkal],2) >0
                   AND $[rdr1.U_mishkal] >= t0.[U_FWeight]
                   AND $[rdr1.U_mishkal] <= t0.[U_LWeight]) THEN T0.[U_Price2] *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
             WHEN (CONVERT(FLOAT,$[rdr1.U_kmNsiaa],2) >0
                   AND $[rdr1.U_kmNsiaa] >= t0.[U_Fkm]
                   AND $[rdr1.U_kmNsiaa] <= t0.[U_Lkm]) THEN T0.[U_Price2] *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
             WHEN (CONVERT(FLOAT,$[rdr1.U_Nefach],2) >0
                   AND $[rdr1.U_Nefach] >= t0.[U_FVolume]
                   AND $[rdr1.U_Nefach] <= t0.[U_LVolume]) THEN T0.[U_Price2] *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
             WHEN (CONVERT(FLOAT,$[rdr1.U_Point],2) >0
                   AND $[rdr1.U_Point] >= t0.[U_FPoint]
                   AND $[rdr1.U_Point] <= t0.[U_LPoint]) THEN T0.[U_Price2] /(CONVERT(FLOAT,$[$38.11],2))*(CONVERT(FLOAT,$[$38.U_KmNsiaa],2)) *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
             WHEN (CONVERT(FLOAT,$[rdr1.U_Hamtana],2) >0
                   AND $[rdr1.U_Hamtana] >= t0.[U_FHamtana]
                   AND $[rdr1.U_Hamtana] <= t0.[U_LHamtana]) THEN T0.[U_Price2] *(1-CAST($[$38.28] AS DECIMAL(18,6)) /100)
         END AS 'price'
  FROM [dbo].[@PRICELIST] T0
  INNER JOIN [dbo].[OITM] T1 ON T0.[U_ItemCode]=T1.[U_ZPRICELIST] WHERE T0.[U_Cardcode] = $[ORDR.U_PCARDCODE.0]
  AND T1.[ItemCode] = $[$38.1]
  AND T0.[U_SugMitan] =$[rdr1.U_SugMitan.1]
  AND T0.[U_SugRehev] = $[Rdr1.U_SugRehev.1]
  AND t0.U_SugHishuv='3'
  SELECT max(T0.PRICE)
  FROM #TEMP T0

  DROP TABLE #TEMP

You're dropping ##Temp not #Temp ... a moment's experimentation in SQL Server shows it treats these as different objects. — Bob Tway, May 01 '15 at 15:28
@MattThrower sorry just copied and posted from reference link: http://stackoverflow.com/questions/3474053/there-is-already-an-object-named-temp-in-the-database but i made necessary changes when i run.. — DonOfDen, May 01 '15 at 15:30
But the source of your error is not the code you've posted. So how are we supposed to help? — Bob Tway, May 01 '15 at 15:31
'In the beginning of every query'. What does that mean? You run this before every single query you run? Give an example of one of your queries where you get this error. I'm assuming multiple statements in either a stored procedure or one after the other — Mark Sinkinson, May 01 '15 at 15:51
@MattThrower can you check my question now.. I have posted one of the query.. — DonOfDen, May 01 '15 at 15:56
If you run your big query in a batch by itself, does it work? — Brian Pressler, May 01 '15 at 15:59
What i mean is, if you copy that big query into management studio into a fresh query window and execute it all by itself does it succeed? The reason I ask is the prefix of your error indicates that you're running this though some program that's using the native client odbc. Who knows what that program is executing before and after that big query. I suspect it's trying to create a table called #temp in the same batch before. — Brian Pressler, May 01 '15 at 16:07
Ok Will check those and update my question.. Can you suggest me what would be the possibility for this error? any clue? so when i search i cant use it.. — DonOfDen, May 01 '15 at 16:10
You could also try surrounding your big query with GO statements and see if that helps. I updated my answer as an example. — Brian Pressler, May 01 '15 at 16:17

Brian Pressler · Accepted Answer · 2015-05-01T16:16:10.063

This seems really strange to me. Temp tables that start with a single # are local to the session. So every session should be able to see it's own #temp table defined independently and have their own data in them. Temp tables with ## prefix can be shared between sessions. They are automatically removed when the last session that used it closes, if it's not dropped explicitly.

Are you creating the temp table, dropping it, and trying to create it again in the same batch? The SQL Parser does not like that. So for example, if i put the following code into management studio and then do a simple syntax check it gives me the same error you are getting even though it seems like it should be valid.

drop table #temp

select 1 x into #temp

select * from #temp

drop table #temp

select 2 x into #temp

select * from #temp

It doesn't like that second attempt at creating the temp table.

You could try making sure that your code that creates and drops the temp table is in it's own batch by surrounding it with the GO statement. SQL seems to have no problem with this:

GO
drop table #temp
select 1 x into #temp

select * from #temp

drop table #temp

GO

select 2 x into #temp

select * from #temp
GO

SQL There is already an object named '#TEMP' in the database after updated to SAP PL-16

1 Answers1