Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    FRANCE (Paris)
    Posts
    23

    Unanswered: Check if a table already exists

    Hi all,

    I've got this problem :

    Server Message: Number 2714, Severity 16
    Server 'FRDEV', Line 2:
    There is already an object named 'test' in the database.

    when I run this sql statement (the first one succeed but after) :

    if not exists (select 1 from sysobjects where type='U' and name = 'test')
    create table test (col1 int, col2 char(2)) lock datapages


    but this statement can be run as many time as we want :

    if exists (select 1 from sysobjects where type='U' and name = 'test') drop table test

    without the message "Cannot drop the table 'test', because it doesn't exist in the system catalogs" so the check succeed.

    a explanation ?

    Thanks in advanced for your help.

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159
    Hi,

    I think your problem has to do with the Sybase parse tree. When it parses your SQL batch

    if not exists (select 1 from sysobjects where type='U' and name = 'test')
    create table test (col1 int, col2 char(2)) lock datapages

    The first time round the table doesn't exists (assumption) and ASE parses your statement and says OK, I'll build that.

    Subsequent executions generate the error because ASE is only parsing batch, not actually executing the SQL. The parser sees the "create table test" and checks the system catalogue to see if it already exists, which it does and so it generates the error.

    The drop statement does seem to perform this kind of checking, so it doesn't generate an error message until the batch is executed rather than parsed.

    How do get around this?

    Try something like this if your on a recent (ASE 12.5 or above)

    if not exists (select 1 from sysobjects where type='U' and name = 'test')
    exec(" create table test (col1 int, col2 char(2)) lock datapages ")

    By doing this you get two things.

    1) The create table statement is executed in a different session

    2) The parser sees...

    if not exists (select 1 from sysobjects where type='U' and name = 'test')
    exec( some string )

    ... so it doesn't throw a wobbler at the "create table" statement

    HTH

    Richard.

  3. #3
    Join Date
    Dec 2003
    Location
    FRANCE (Paris)
    Posts
    23
    Thanks for your answer Richard.
    It's ok with dynamic SQL, the check test succeed

    Mickael

Posting Permissions

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