Post reply

Alter Temporary Table, Works in SQL2000, but error in SQL2005

  • May 11, 2007 at 4:21 am

    #174037

    Hi..

    I have script like this

    -------------------------------------

    create proc Test

    as

    create table #TMPTest(

    prodID INT,

    ProdName varchar(30)

    )

    insert into #TMPtest execute SPGetProdInfo

    ALTER TABLE #TMPTest ADD NewCol INT  -- Error Invalid column name 'NewCol'.

    update #TMPTest set NewCol = 1

    Select * from #TMPTest

    Drop table #TMPTest

    -----------------------------------

    When I run in SQL Server 2000, it runs fine, but in sqlserver 2005 i got error

    Error Invalid column name 'NewCol'.

    Does anyone know why and how to fixed this in SQL Server 2005 ??

     

  • May 11, 2007 at 6:41 am

    #705794

    ALTER TABLE #TMPTest ADD [NewCol] INT  >> or change the colname altogether.

     

    I'll submitt to you that the real column name is most likely a reserved keyqord in 2005 and is not in 2000.  I could actually confirm this had you posted the actual script you are using!

  • May 14, 2007 at 7:21 am

    #706210

    i managed to run your script in sql 2005 express without a problem

    Everything you can imagine is real.

Viewing 3 posts - 1 through 2 (of 2 total)

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