Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2003
    Posts
    73

    Unanswered: System Catalog Question

    Hey there,
    I'm trying to run the following query:

    Code:
    if exists(select * from shistory.dbo.sysobjects where name='STOCK_815')
    drop table [shistory.dbo.STOCK_815]
    and I get the error:
    "Cannot drop table 'shistory.dbo.STOCK_815', because it does not exist in the system catalog."

    The table IS there, DOES exist, but I cannot run that query. The only way I can run it is by changing the name of the table to
    STOCK_815 like this:

    Code:
    if exists(select * from Stock_History.dbo.sysobjects where name='STOCK_PRE_815')
    drop table [STOCK_PRE_815]
    Why?

    Thx

  2. #2
    Join Date
    Oct 2003
    Posts
    60
    Check whether the dbo is the owner of the table.
    jaraba

  3. #3
    Join Date
    Oct 2003
    Posts
    73
    thx for the reply jaraba,
    yes, dbo is the owner of that table.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The brackets have to go around each piece of the three part name like this:

    [shistory].[dbo].[STOCK_815]

    With the brackets around the whole three part name, SQL Server is looking for a table that is named "shistory.dbo.STOCK_815" in the local database.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can only use the brackets around a single element of a name, not around the whole thing. Think of the brackets something like quotation marks, so what you really were executing was (note my addition in red):
    Code:
    if exists(select * from shistory.dbo.sysobjects where name='STOCK_815')
    drop table dbo.[shistory.dbo.STOCK_815]
    That isn't at all what you intended.

    When you changed the name in the second example, you were only quoting one element, so that worked as you expected.

    -PatP

    Sniped!

  6. #6
    Join Date
    Oct 2003
    Posts
    73

    Thumbs up

    thx Pat and MCrowley,
    that makes sense. I tested it and it worked fine when I put the brackets around each piece of the 3 part table name.

    Thx a ton for your help and replying so quickly!

  7. #7
    Join Date
    Oct 2003
    Posts
    73
    Well, I have another question for you guys. The issue regarding how to specify a table name in SQL really came from some VB 6 code I was looking at. In the VB6 code, I'm trying to drop a table using code that generates this sql:

    Code:
    if exists(select * from shistory.dbo.sysobjects where name='STOCK_815')
    drop table [shistory.dbo.STOCK_815]
    Now, this works sometimes, and doesn't work other times. I was told that (and haven't researched yet btw) that VB6 takes care of the conversion of
    Code:
    if exists(select * from shistory.dbo.sysobjects where name='STOCK_815')
    drop table [shistory.dbo.STOCK_815]
    to
    Code:
    if exists(select * from shistory.dbo.sysobjects where name='STOCK_815')
    drop table [STOCK_815]
    You think there might be a vb6 compiler issue?
    Anyone with similiar experience?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't believe that VB does any kind of conversion like that.

    -PatP

  9. #9
    Join Date
    Oct 2003
    Posts
    73
    hmm, that is interesting because it works sometimes and doesn't work other times. I'm gonna have to do more research. Thx for the reply

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It can't possibly work "sometimes" unless you create a table like [dbo].[shistory.dbo.STOCK_815]
    But even then VB will NEVER translate it to just [STOCK_815]

    What is your FE code that generates the DROP statement?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm very reluctant to use the word "never" with VB... I've seen too many "kinky" things done "under the covers" to rule much of anything out completely! With that said, I agree wholeheartedly with rdjabarov in that I can't see VB every "automagically" making that change, and in thinking that there might sometimes be a [dbo].[shistory.dbo.STOCK_815] table that causes the code to work occaisionally.

    -PatP

  12. #12
    Join Date
    Oct 2003
    Posts
    73
    thx again for helpen me out yall,
    Well, I'm gonna try to take a look at the code in more detail later. It doesn't make sense to me at all either. I'll be researching the problem more when I have time (and it most likely will make perfect sense then). If I come up with the solution to this rediculous problem, I'll post it at a later date.

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I don't think you'll have much luck with the solution, unless you change your code, which you should
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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