CODEWITHSUNDEEP
sqlsql-serversql-server-2008sapb1
DonOfDen
DonOfDen

Reputation: 4088

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

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 enter image description here

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

Upvotes: 1

Views: 4130

Answers (1)

Brian Pressler
Brian Pressler

Reputation: 6713

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

Upvotes: 2

Related Questions