Invalid column name on a temp table alter ...

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

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

  • 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?

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

  • 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

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

  • 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]

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

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

  • 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]

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

  • 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

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

  • 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 14 (of 14 total)

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