|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Weird problem with temp tablesI'm using SQL Server 2000 and I have a strange problem with a temporary table I'm creating in a stored procedure. I use the following code: SELECT * INTO #TempTable FROM tblUsers ALTER TABLE #TempTable ADD RecPosition INTEGER IDENTITY This creates a copy of the tblUsers table and then I add an integer field to number the rows. Now when I do a SELECT * FROM #TempTable in the output, I see my RecPosition field with the correct values (1...n), but when I try to use the field, whether to order or filter the data, it spits out that the RecPosition field does not exist. Why is that and is there another way to do this without creating the table by specifying my RecPosition and each of the fields and then copying the record? In SQL Server 2005, I won't have to go through all this to implement paging in my stored procedure, but I fear we will still be using 2000 for a while. Any input will be appreciated! Eric. Eric,
I took your code and ran it in Query analyzer. No problems. I could see the rec position, filter against it and order by it. Are you by any chance trying to create the table in one session and then alter it or query it from another session? Local temp tables are tagged with the session id and not visible to other sessions. Show quote "Eric Caron" wrote: > Hi all, > > I'm using SQL Server 2000 and I have a strange problem with a temporary > table I'm creating in a stored procedure. I use the following code: > > SELECT * INTO #TempTable FROM tblUsers > ALTER TABLE #TempTable ADD RecPosition INTEGER IDENTITY > > This creates a copy of the tblUsers table and then I add an integer field to > number the rows. Now when I do a > > SELECT * FROM #TempTable > > in the output, I see my RecPosition field with the correct values (1...n), > but when I try to use the field, whether to order or filter the data, it > spits out that the RecPosition field does not exist. Why is that and is > there another way to do this without creating the table by specifying my > RecPosition and each of the fields and then copying the record? In SQL > Server 2005, I won't have to go through all this to implement paging in my > stored procedure, but I fear we will still be using 2000 for a while. > > Any input will be appreciated! > > Eric. > > > > I took your code and ran it in Query analyzer. No problems. I could see Really? When I run this:> the rec position, filter against it and order by it. SELECT * INTO #TempTable FROM (SELECT a = 1 UNION SELECT 2) x; ALTER TABLE #TempTable ADD RecPosition INT IDENTITY; SELECT RecPosition FROM #TempTable; SELECT * FROM #TempTable ORDER BY RecPosition; DROP TABLE #tempTable; I get this: (2 row(s) affected) Server: Msg 207, Level 16, State 3, Line 5 Invalid column name 'RecPosition'. Server: Msg 207, Level 16, State 1, Line 6 Invalid column name 'RecPosition'. That is because the parser does not execute the ALTER TABLE before everything is run. It sees, okay, #TempTable has an INT column called a. So when you try to mention the column explicitly, since the parser doesn't know about RecPosition because it has not been added yet, it throws an error. I'd love to see your repro where you create a temp table, alter it by adding a column, and then use that new column explicitly. Here's what I ran. I used sysusers as input:
SELECT uid INTO #TempTable FROM master..sysUsers ALTER TABLE #TempTable ADD RecPosition INTEGER IDENTITY SELECT * FROM #TempTable where RecPosition >= 11 order by RecPosition I see your point, though. Initially, I ran these commands in query analyzer one at a time. When packaged together as a proc, I got the same error you reported. Sorry about that. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > I took your code and ran it in Query analyzer. No problems. I could see > > the rec position, filter against it and order by it. > > Really? When I run this: > > SELECT * INTO #TempTable FROM (SELECT a = 1 UNION SELECT 2) x; > > ALTER TABLE #TempTable ADD RecPosition INT IDENTITY; > > SELECT RecPosition FROM #TempTable; > SELECT * FROM #TempTable ORDER BY RecPosition; > > DROP TABLE #tempTable; > > > I get this: > > > (2 row(s) affected) > > Server: Msg 207, Level 16, State 3, Line 5 > Invalid column name 'RecPosition'. > Server: Msg 207, Level 16, State 1, Line 6 > Invalid column name 'RecPosition'. > > > That is because the parser does not execute the ALTER TABLE before > everything is run. It sees, okay, #TempTable has an INT column called a. > So when you try to mention the column explicitly, since the parser doesn't > know about RecPosition because it has not been added yet, it throws an > error. > > I'd love to see your repro where you create a temp table, alter it by adding > a column, and then use that new column explicitly. > > > execute the alter on another context...
SELECT uid INTO #TempTable FROM master..sysUsers exec('ALTER TABLE #TempTable ADD RecPosition INTEGER IDENTITY') SELECT * FROM #TempTable where RecPosition >= 11 order by RecPosition -- Show quote-oj "tthrone" <tthr***@discussions.microsoft.com> wrote in message news:8016E28C-41B6-4572-AF9F-23FFA7CEF3E4@microsoft.com... > Here's what I ran. I used sysusers as input: > > SELECT uid INTO #TempTable FROM master..sysUsers > ALTER TABLE #TempTable ADD RecPosition INTEGER IDENTITY > SELECT * FROM #TempTable where RecPosition >= 11 order by RecPosition > > I see your point, though. Initially, I ran these commands in query > analyzer > one at a time. When packaged together as a proc, I got the same error you > reported. Sorry about that. > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> > I took your code and ran it in Query analyzer. No problems. I could >> > see >> > the rec position, filter against it and order by it. >> >> Really? When I run this: >> >> SELECT * INTO #TempTable FROM (SELECT a = 1 UNION SELECT 2) x; >> >> ALTER TABLE #TempTable ADD RecPosition INT IDENTITY; >> >> SELECT RecPosition FROM #TempTable; >> SELECT * FROM #TempTable ORDER BY RecPosition; >> >> DROP TABLE #tempTable; >> >> >> I get this: >> >> >> (2 row(s) affected) >> >> Server: Msg 207, Level 16, State 3, Line 5 >> Invalid column name 'RecPosition'. >> Server: Msg 207, Level 16, State 1, Line 6 >> Invalid column name 'RecPosition'. >> >> >> That is because the parser does not execute the ALTER TABLE before >> everything is run. It sees, okay, #TempTable has an INT column called a. >> So when you try to mention the column explicitly, since the parser >> doesn't >> know about RecPosition because it has not been added yet, it throws an >> error. >> >> I'd love to see your repro where you create a temp table, alter it by >> adding >> a column, and then use that new column explicitly. >> >> >> [Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.] Aaron Bertrand [SQL Server MVP] (ten.xoc@dnartreb.noraa) writes: Show quote > Really? When I run this: I get this on SQL 2000, but not on SQL 2005.> > SELECT * INTO #TempTable FROM (SELECT a = 1 UNION SELECT 2) x; > > ALTER TABLE #TempTable ADD RecPosition INT IDENTITY; > > SELECT RecPosition FROM #TempTable; > SELECT * FROM #TempTable ORDER BY RecPosition; > > DROP TABLE #tempTable; > > > I get this: > > > (2 row(s) affected) > > Server: Msg 207, Level 16, State 3, Line 5 > Invalid column name 'RecPosition'. > Server: Msg 207, Level 16, State 1, Line 6 > Invalid column name 'RecPosition'. The key is that the error is happening at recompilation. First time the batch is compiled there is no error, because the table does not exist. Due to deferred name resolution, SQL Server is quiet about this. When SQL Server hits the ALTER TABLE statement, this statement needs to be recompiled, because it was deferred last time. SQL 2000 then recompiles the entire batch. But deferred name resolution is for tables only. Once the table exists, the columns must also exist. Thus you get a compilation error. On SQL 2005, recompilation is per statement, and thus the error is avoided. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.seBooks Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > I get this on SQL 2000, but not on SQL 2005. Thanks Erland, I hadn't mentioned or tested 2005, because the OP said 2000.
Show quote
"Eric Caron" <ecaron@nospam.quebecaffaires.com> wrote in message I don't see any other solutions besides the one you gave yourself:news:OWtVAu2TGHA.4452@TK2MSFTNGP12.phx.gbl... > Hi all, > > I'm using SQL Server 2000 and I have a strange problem with a temporary > table I'm creating in a stored procedure. I use the following code: > > SELECT * INTO #TempTable FROM tblUsers > ALTER TABLE #TempTable ADD RecPosition INTEGER IDENTITY > > This creates a copy of the tblUsers table and then I add an integer field > to number the rows. Now when I do a > > SELECT * FROM #TempTable > > in the output, I see my RecPosition field with the correct values (1...n), > but when I try to use the field, whether to order or filter the data, it > spits out that the RecPosition field does not exist. Why is that and is > there another way to do this without creating the table by specifying my > RecPosition and each of the fields and then copying the record? In SQL > Server 2005, I won't have to go through all this to implement paging in my > stored procedure, but I fear we will still be using 2000 for a while. > > Any input will be appreciated! > > Eric. Creating the table with that column. A bit more work but you can do it. Don't get lazy on us. :-) Maybe this can give you other paging options. http://www.aspfaq.com/show.asp?id=2120 > SELECT * INTO #TempTable FROM tblUsers Well, why do you need to do this in two steps?> ALTER TABLE #TempTable ADD RecPosition INTEGER IDENTITY SELECT RecPosition = IDENTITY(INT,1,1), x.* INTO #TempTable FROM ( SELECT a = 1 UNION SELECT a = 2 ) x SELECT RecPosition FROM #TempTable SELECT * FROM #TempTable ORDER BY RecPosition DROP TABLE #tempTable Keep in mind that the order that the identity values are generated is not guaranteed, even if you bothered to add ORDER BY to your query.  
Other interesting topics
|
|||||||||||||||||||||||
