SQL Server 8.0 column error

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • lpizzle
    New Member
    • Oct 2007
    • 4

    SQL Server 8.0 column error

    Hi,
    I am executing a query to a DB running sql server 2000 from a C# program. I am creating a table by copying another table's columns into it, adding 2 columns to the table, and then trying to select the field from that newly created table. For some reason, if I try to do this all with one line of SQL, it fails to recognize the columns that I added (invalid column name error), but if I run the SQL in two seperate executions it recognizes the columns I added. Any help would be much appreciated, here is the SQL:

    Code:
     SELECT * INTO tempjoin FROM TICKETS WHERE RECNUM = 0
         ALTER TABLE tempjoin ADD NOTE varchar(60)
         ALTER TABLE tempjoin ADD TYPE varchar(3)
    
         SELECT NOTE FROM tempjoin
  • ck9663
    Recognized Expert Specialist
    • Jun 2007
    • 2878

    #2
    Originally posted by lpizzle
    Hi,
    I am executing a query to a DB running sql server 2000 from a C# program. I am creating a table by copying another table's columns into it, adding 2 columns to the table, and then trying to select the field from that newly created table. For some reason, if I try to do this all with one line of SQL, it fails to recognize the columns that I added (invalid column name error), but if I run the SQL in two seperate executions it recognizes the columns I added. Any help would be much appreciated, here is the SQL:

    Code:
     SELECT * INTO tempjoin FROM TICKETS WHERE RECNUM = 0
         ALTER TABLE tempjoin ADD NOTE varchar(60)
         ALTER TABLE tempjoin ADD TYPE varchar(3)
    
         SELECT NOTE FROM tempjoin

    looks like you're creating tempjoin based on whatever tickets' structure is. try this:

    Code:
    select tickets.*, cast(space(60) as varchar(60) )as note, 
    cast(space(3) as varchar(3)) as type from tickets
    where 1 = 2

    Comment

    • lpizzle
      New Member
      • Oct 2007
      • 4

      #3
      Originally posted by ck9663
      looks like you're creating tempjoin based on whatever tickets' structure is. try this:

      Code:
      select tickets.*, cast(space(60) as varchar(60) )as note, 
      cast(space(3) as varchar(3)) as type from tickets
      where 1 = 2
      This worked like a charm, thank you very much. I'm still a little but confused as to why I was getting an error, but it works so I can't ask for too much more. Thanks again!

      Comment

      Working...