Invalid column name on a temp table alter ...

Question

Invalid column name on a temp table alter ...

Adam Bean

One Orange Chip

Points: 26631
More actions
March 24, 2008 at 12:57 pm

#76934

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!!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

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.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

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?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

Yep, ad-hoc that would work great with a GO, but this is a piece of a stored proc ...

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

That works, thank you ... but can anyone explain why mine does not? It works perfect in 2005 ... this is driving me insane.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

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]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

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.

Gordon-265412 SSCrazy Points: 2898 More actions · Answer 8

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... 🙂

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

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]

Level99 Newbie Points: 9 More actions · Answer 10

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!

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 11

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

alex-746374 SSC Eights! Points: 922 More actions · Answer 12

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).

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

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]