October 29, 2007 at 10:56 am
Has anyone else seen this? It was driving me nuts this morning until I finally figured out what SQL Server was doing...
Run the below code all at once:
if (Select Object_ID('tempdb..#Color1')) IS NOT NULL Drop Table #Color1;
if (Select Object_ID('tempdb..#Color2')) IS NOT NULL Drop Table #Color2;
Declare @Year int;
Set @Year = 2007;
Select @Year as MyYear, 'Blue' as color into #Color1;
Select @Year as MyYear, 'Green' as color into #Color2;
Select c1.MyYear, c1.color, c2.Color
from #Color1 c1
join #Color2 c2
on c1.MyYear = c2.MyYear;
After you've run it, change the column name in #Color2 from "Color" to "Color2" and change it in the Select statement of the last select query also. Do this change without rerunning anything.
After you're done with the change, re-run the whole thing. Tell me if you don't get this error "Msg 207, Level 16, State 1, Line 14 Invalid column name 'Color2'."
It looks to me that when SQL is validating the code, it validates everything except the IF statements up at top and refers to the temp tables currently in memory to validate the last query instead of looking at the changed query above it. I had to run the IF statements by themselves to drop the temp tables before I could change one of my column names.
Yikes, that was driving me insane. I don't recall this behavior on SQL Server 2000. Does anyone else? Post your thoughts or comments please. @=)
October 29, 2007 at 11:34 am
Brandie Tarvin (10/29/2007)
Has anyone else seen this? It was driving me nuts this morning until I finally figured out what SQL Server was doing...Run the below code all at once:
if (Select Object_ID('tempdb..#Color1')) IS NOT NULL Drop Table #Color1;
if (Select Object_ID('tempdb..#Color2')) IS NOT NULL Drop Table #Color2;
Declare @Year int;
Set @Year = 2007;
Select @Year as MyYear, 'Blue' as color into #Color1;
Select @Year as MyYear, 'Green' as color into #Color2;
Select c1.MyYear, c1.color, c2.Color
from #Color1 c1
join #Color2 c2
on c1.MyYear = c2.MyYear;
After you've run it, change the column name in #Color2 from "Color" to "Color2" and change it in the Select statement of the last select query also. Do this change without rerunning anything.
After you're done with the change, re-run the whole thing. Tell me if you don't get this error "Msg 207, Level 16, State 1, Line 14 Invalid column name 'Color2'."
It looks to me that when SQL is validating the code, it validates everything except the IF statements up at top and refers to the temp tables currently in memory to validate the last query instead of looking at the changed query above it. I had to run the IF statements by themselves to drop the temp tables before I could change one of my column names.
Yikes, that was driving me insane. I don't recall this behavior on SQL Server 2000. Does anyone else? Post your thoughts or comments please. @=)
I just ran this on SQL Server 200 and 2005 and I can confirm it behaves exactly the same. However, if the connection goes out of scope, then the temp tables are dropped regardless and the problem disappears.
October 29, 2007 at 11:39 am
Well my connection must have been refreshed shortly after posting this because I was demonstrating it for my boss and it worked... DOH. So then I tried a third column name and got the error to come up.
He still told me I was imagining things. @=)
October 29, 2007 at 12:20 pm
Put batch separator GO after dropping the second temp table, it ensures that the temp tables are dropped prior to executing your DML statements.
If you don't separate them, they are all running as a all one batch there fore it's not guaranteeng that previous instance of temp table is dropped.
October 29, 2007 at 12:26 pm
The problem being that my code is going to be a stored procedure once I get done coding it. If I use GO, then I cut my Sproc short and can't process the stuff.
October 30, 2007 at 6:03 am
I don't really see how you would reproduce the issue in a stored procedure.
Your table definition will have to remain the same anyway.
Once this is in a stored procedure, you will also have scope on your side.
You may be able to have this problem if you were not using temp tables.
Try this example:
DROP PROC MyProc
DROP PROC MyProc2
GO
Create Proc MyProc
AS
if (Select Object_ID('tempdb..#Color1')) IS NOT NULL Drop Table #Color1;
if (Select Object_ID('tempdb..#Color2')) IS NOT NULL Drop Table #Color2;
Declare @Year int;
Set @Year = 2007;
Select @Year as MyYear, 'Blue' as color into #Color1;
Select @Year as MyYear, 'Green' as color into #Color2;
Select c1.MyYear, c1.color, c2.Color
from #Color1 c1
join #Color2 c2
on c1.MyYear = c2.MyYear;
GO
CREATE Proc MyProc2
AS
if (Select Object_ID('tempdb..#Color1')) IS NOT NULL Drop Table #Color1;
if (Select Object_ID('tempdb..#Color2')) IS NOT NULL Drop Table #Color2;
Declare @Year int;
Set @Year = 2007;
Select @Year as MyYear, 'Blue' as color into #Color1;
Select @Year as MyYear, 'Green' as color2 into #Color2;
Select c1.MyYear, c1.color, c2.Color2
from #Color1 c1
join #Color2 c2
on c1.MyYear = c2.MyYear;
GO
EXEC MyProc
EXEC MyProc2
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply