Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    13

    Unanswered: Using "Alter Table" after "Select Into"

    Using "Alter Table" after "Select Into" a temporary table does not seem to work. Here's my code in VB:

    rstemp.Open "Select Top 0 " & mstrPrimaryKey & " into #tmpDumpReport From " & mstrTableToProcess & "", gcnProj, adOpenStatic, adLockReadOnly
    gcnProj.Execute "Alter Table #tmpDumpReport " & _
    "ADD ColumnNm VARCHAR(30) NULL, CurrentValue VARCHAR(255) NULL, SequenceNbr INT NULL "

    This generates an error at the "Alter Table" statement saying that #tmpDumpReport does not exist in the database. I don't understand since "Select Into" should've created a table in the database.
    The following works though.

    rstemp.Open "Select Top 0 " & mstrPrimaryKey & " into tmpDumpReport From " & mstrTableToProcess & "", gcnProj, adOpenStatic, adLockReadOnly
    gcnProj.Execute "Alter Table tmpDumpReport " & _
    "ADD ColumnNm VARCHAR(30) NULL, CurrentValue VARCHAR(255) NULL, SequenceNbr INT NULL "

    If I do not use temporary tables (#tmp) and use tmp instead, then it works. Has anyone else seen a similar thing or has an explanation.............................

    Thanks

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Script below works just fine.

    drop table #tmp
    drop table #tmp10
    go
    create table #tmp(id int)
    select * into #tmp10 from #tmp
    alter table #tmp10 add code2 varchar(10)
    select * from #tmp10

    Do you try to alter table from the same connection?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, I suspect that your VB code is using a separate connection for the ALTER command. Temporary tables are only visible to the connection that creates them (unless created as global, which I don't recommend).
    I'm not a VB expert, but make sure you are using a single consistent connection to the database.

    blindman

  4. #4
    Join Date
    Sep 2003
    Posts
    13
    I am using the same connection but I am not explicitly creating temporary table by Create Table. I thought Select Into creates the table. I will try create table, select into and then alter.

    thanks.


    Originally posted by snail
    Script below works just fine.

    drop table #tmp
    drop table #tmp10
    go
    create table #tmp(id int)
    select * into #tmp10 from #tmp
    alter table #tmp10 add code2 varchar(10)
    select * from #tmp10

    Do you try to alter table from the same connection?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Select into will create the table, though explicit creation is better programming practice.

    blindman

  6. #6
    Join Date
    Sep 2003
    Posts
    13
    Yes, I guess so. When I use:

    drop table #tmp
    drop table #tmp10
    go
    select * into #tmp10 from #tmp
    alter table #tmp10 add code2 varchar(10)
    select * from #tmp10

    in a Query Analyzer, it seems to work even without the Create Table #tmp. But when I try using the same thing in VB, it just fails at the Alter Table saying the table does not exist.
    It sounds pretty inconsistent.

    Archna

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Temporary tables are available only to the connections that create them and are dropped as soon as the connection is broken.

    blindman

  8. #8
    Join Date
    Sep 2003
    Posts
    13
    I know but I am using the same connection, it is not broken and "Select Into" and "Alter Table" statements are right next to each other. There's nothing in between them. That's why I am confused

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm no VB expert, but I'd bet dollars to doughnut holes that this is what is going on. Find a VB guru to check your code. As you said, it works fine in Query Agonizer, so your problem has to be in the VB code.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •