Problem with temp tables 1

[dnfrantum]

Here is my code. I am working on MS SQL SERVER 2008 R2

IF OBJECT_ID('tempdb..#JAM_TMP_TBL_00','U') is not null
BEGIN
DROP TABLE #JAM_TMP_TBL_00
END;

IF OBJECT_ID('tempdb..#JAM_TMP_TBL_01','U') is not null
BEGIN
DROP TABLE #JAM_TMP_TBL_01
END;

IF OBJECT_ID('tempdb..#JAM_TMP_TBL_02','U') is not null
BEGIN
DROP TABLE #JAM_TMP_TBL_02
END;

IF OBJECT_ID('tempdb..#JAM_TMP_TBL_03','U') is not null
BEGIN
DROP TABLE #JAM_TMP_TBL_03
END;

SELECT DISTINCT T1.ItemCode,T1.Dscription,T3.SlpName,T1.U_BatchNo,T1.U_BatchNotes,
T1.U_Pkging,T1.Price as [Cost],T2.WhsName,
'OnPO' = CASE WHEN T0.DocStatus = 'O' AND T1.LineStatus = 'O' 
              THEN ROUND(T1.OpenQty,0)
              ELSE 0 END,T1.U_Izod,T1.U_Density,T1.U_Meltflow,T1.U_BatchNotes as POBtchNotes,T0.DocDate
INTO #JAM_TMP_TBL_00
FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry
             INNER JOIN OWHS T2 ON T1.WhsCode = T2.WhsCode
             INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode

             
SELECT DISTINCT T1.ItemCode,T1.Dscription,T3.SlpName,T1.U_BatchNo,T1.U_BatchNotes,
T1.U_Pkging,T1.Price as [Cost],T2.WhsName,ROUND(T1.Quantity,0) as Quantity,
T1.U_Izod,T1.U_Density,T1.U_Meltflow,T1.U_BatchNotes as APINVBtchNotes
INTO #JAM_TMP_TBL_01
FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry AND T0.U_POCosttype = 'Inventory'
             INNER JOIN OWHS T2 ON T1.WhsCode = T2.WhsCode
             INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode
WHERE T1.U_BatchNo is not null AND T1.ItemCode not in ('FREIG-H-T','GRIND-I-NG','TOLL-I-NG')
            
SELECT DISTINCT T1.U_BatchNo,T1.Price
INTO #JAM_TMP_TBL_02 
FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocType = 'S' AND T0.U_POCosttype = 'Freight In' AND T1.U_BatchNo is not null



SELECT * FROM #JAM_TMP_TBL_01

The problem is that the

 SELECT * FROM #JAM_TMP_TBL_01

is throwing an error of 'Invalid ObjectName #JAM_TMP_TBL_01' despite the fact that it has been successfully created above. Does anyone understand what could be causing this and how I can remedy it? This is the first time that I have seen this and I am not even sure how to research it.



Thanks in advance,
Donald

 

[markros]

Try saving this script and close SSMS. The re-open it and re-try. By a quick look I don't see anything wrong, so it may be that something got 'hosed', so re-starting SSMS may cure the problem.

PluralSight Learning Library

 

[dnfrantum]

I stopped and restarte SSMS and that didn't take care of the problem. In 2008 R2, I am used to seeing the table names in the autocomplete when I begin typing them, but they aren't showing up. It is as if the system is not recognizing them. They do return values if I Select from them, but I am getting the error as previously defined. Again, if there is anyone out there who has encountered this or could provide some guidance, I would be greatly appreciative.

Thanks in advance,
Donald

 

[dnfrantum]

Update:

I created the temp tables as views and I am having the same issue. What would cause objects not to be recognized within the system?



Thanks in advance,
Donald

 

[gmmastros]

Is there more code involved here that you are not showing? I suspect there is, but would like to make sure.

I find it a bit odd that you are creating a temp table (the 00 one), and then another temp table (the 01 one) and then a 3rd temp table (the 02 one) but only selecting data (at the end) from the 01 temp table. What is the purpose of creating the other 2 temp tables? What's the purpose of creating the 01 temp table if you are going to just select the data from it.

My thoughts here....

Temp tables are nice, and they have their place in code. However, a lot of developers tend to over-use them. Specifically, they will use temp tables when they don't really need to. This code may fall in to that category. If we can eliminate the need for the temp table, we would also eliminate the error associated with it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dnfrantum]

I will do a join later on, but that is part of my problem. Because the temp tables aren't being recognized as objects, the join isn't recognized and I am getting more records than I am expecting, e.g. ItemCode is in both tables, table one has 100 records and table two has 50 records. Instead of getting 100 records on a left join, I am getting 150 records in my results.

This kind of behaviour is also happening when I transform the temp tables to views, so there is something in the system tables that isn't behaving properly. I just don't know what it is.

Thanks in advance,
Donald

 

[gmmastros]

Let me see if I understand this correctly.

You are creating 2 temp tables and then left joining the temp tables together. Are you doing anything else with the temp tables?

If you are not doing anything else with the temp tables, you probably don't need them. If you can remove the temp tables, the query will *probably* execute faster too.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh yeah... one more thing... With only 100 rows in the temp table, you may want to try using a table variable instead.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dnfrantum]

The information you are providing isn't helping my issue. Do you have any indication what might be causing the temp tables to not be recognized by the system? I understand that there are more ways to complete the task, but I am looking for a solution to the objects not being recognized.

Thanks in advance,
Donald

 

[gmmastros]

I apologize for wasting your time. Good luck.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I remember we had a similar problem with one client. I was updating data fixing a problem and I was saving information into #temp tables. All of the sudden, I could not see these same tables trying to use Import Wizard and select from these tables. I made the tables permanent tables and still had some troubles. This was a real mystery, so I posted this question in MSDN forum and Stephanie suggested, that there may be two servers and I was looking for the tables in the wrong server. And yes indeed, there were 2 servers with the same database name and somehow they were switching. We replicated the same problem again the next time I worked with the same client - this time I didn't think that I am getting crazy

So, perhaps there is a similar problem here?

PluralSight Learning Library