[danmick]

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.

[richardcrossley]

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.

[danmick]

Thanks for your answer Richard.
It's ok with dynamic SQL, the check test succeed

Mickael