March 24, 2008 at 12:57 pm
Can someone please explain why I can't retrieve a column of a temp table that I add after the creation of a temp table? This problem is only in 2000 and works fine in 2005 ...
SELECT TOP 1 * INTO #sysobjects FROM [dbo].[sysobjects]
ALTER TABLE #sysobjects ADD[schemaname] [VARCHAR] (48) NULL
DELETE FROM #sysobjects
INSERT INTO #sysobjects
EXEC('USE [master] SELECT *, USER_NAME(uid) FROM [dbo].[sysobjects] WHERE [type] = ''U''')
--SELECT * FROM #sysobjects
SELECT [schemaname] FROM #sysobjects
DROP TABLE #sysobjects
Now if you run the SELECT *, the schemaname column is definitely there ... I don't get it, and this is driving me crazy!!
March 24, 2008 at 1:24 pm
It would appear that it is parsing the SQL before hand and determining that the column does not yet exist ... this is truly got me at a loss.
March 24, 2008 at 1:28 pm
break it up into 2 batches. Put the alter in its own batch, and then it should compile and execute.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 24, 2008 at 1:29 pm
Yep, ad-hoc that would work great with a GO, but this is a piece of a stored proc ...
March 24, 2008 at 1:32 pm
SELECT TOP 0
*,
[schemaname] = convert(varchar(48),null)
INTO
#sysobjects
FROM
[dbo].[sysobjects]
EXEC('
use master
INSERT INTO #sysobjects
SELECT
*,
USER_NAME(uid)
FROM
[dbo].[sysobjects]
WHERE
[type] = ''U''
')
SELECT [schemaname] FROM #sysobjects
DROP TABLE #sysobjects
March 24, 2008 at 1:37 pm
That works, thank you ... but can anyone explain why mine does not? It works perfect in 2005 ... this is driving me insane.
March 24, 2008 at 1:44 pm
Adam Bean (3/24/2008)
That works, thank you ... but can anyone explain why mine does not? It works perfect in 2005 ... this is driving me insane.
You had it right about it parsing before-hand. SQL 2005 parser is just a little bit more tolerant of schema changes mid-batch than SQL 2000 is. Still lots of cases where SQL 2005 complains too.
The SOP way around this in a store procedure is to use Dynamic SQL to nest and/or switch batches as Micheal demonstrated.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 24, 2008 at 3:28 pm
Adam Bean (3/24/2008)
That works, thank you ... but can anyone explain why mine does not? It works perfect in 2005 ... this is driving me insane.
You made it too complex. There was no reason to alter the table, and it just confused the query optimizer.
March 26, 2008 at 8:26 am
rbarryyoung (3/24/2008)
You had it right about it parsing before-hand. SQL 2005 parser is just a little bit more tolerant of schema changes mid-batch than SQL 2000 is.
Actually, it's not the parser, it's the fact that you cannot ALTER a temp object in the same batch as you create it. A verrrry annoyying 'feature' of 2000.
Micheals approach (as always) is one that I hadn't thought of, or seen elsewhere.
Your friendly High-Tech Janitor... 🙂
March 26, 2008 at 9:51 pm
Michael's solution also works for me on both Sql2000 & 2005:
SELECT TOP 1 *, Cast('' as VARCHAR(48)) as [schemaname]
INTO #sysobjects
FROM [dbo].[sysobjects]
DELETE FROM #sysobjects
INSERT INTO #sysobjects
EXEC('USE [master] SELECT *, USER_NAME(uid) FROM [dbo].[sysobjects] WHERE [type] = ''U''')
SELECT [schemaname] FROM #sysobjects
DROP TABLE #sysobjects
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 23, 2008 at 10:15 am
I had the exact same problem... trying to reference a column added to a temp table in the same batch using SQL Server 2000, within a stored proc.
What I got out of this thread that solved my problem was using "EXEC" for my references:
EXEC('SELECT [myNewColumn] FROM [#TempTable] (NOLOCK)')
Resolved my error from the original:
SELECT [myNewColumn] FROM [#TempTable] (NOLOCK)
Thanks!
October 23, 2008 at 11:24 am
problem solved.
SELECT TOP 1 *
INTO #sysobjects
FROM [dbo].[sysobjects]
where 0 = 1;
ALTER TABLE #sysobjects ADD [schemaname] [VARCHAR] (48) NULL ;
-- DELETE FROM #sysobjects
INSERT INTO #sysobjects
EXEC('USE [master] SELECT *, USER_NAME(uid) FROM [dbo].[sysobjects] WHERE [type] = ''U''') ;
-- solve the missing column problem
Select *
into #sysobjectsnew
from #sysobjects;
SELECT [schemaname]
FROM #sysobjectsnew ;
drop table #sysobjects ;
drop table #sysobjectsnew ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 22, 2009 at 7:51 am
Gordon-265412 (3/26/2008)
rbarryyoung (3/24/2008)
You had it right about it parsing before-hand. SQL 2005 parser is just a little bit more tolerant of schema changes mid-batch than SQL 2000 is.
Actually, it's not the parser, it's the fact that you cannot ALTER a temp object in the same batch as you create it. A verrrry annoyying 'feature' of 2000.
Micheals approach (as always) is one that I hadn't thought of, or seen elsewhere.
Are you sure it's not the parser? I have a temp table created in a SP. Later in the SP I add a column using ALTER TABLE, and if I go:
select * from #mytemptable
I can see the column. but if I go:
select newlyaddedcolumn from #mytemptable
I get the error (which is fixable by doing the ALTER in an EXEC).
September 22, 2009 at 10:24 am
I am pretty sure that we were actually talking about the same thing.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply