cancel
Showing results for 
Search instead for 
Did you mean: 

create procedure failed because of table exist

Former Member
0 Kudos

---

Sybase version:

Adaptive Server Enterprise/12.5.4/EBF 15432 ESD#8/P/Sun_svr4/OS 5.8/ase1254/2105/64-bit/FBO/Sat Mar 22 14:38:37

2008

---

Hi team,

I have a question regarding the creation of a stored procedure.

I have a sql script that

  1. check the existence of my stored process and if it exists it drops it
  2. create my stored process
    1. My stored process starts by checking the existence of a table and if it exists it drops it, then it creates it again.

The first time I sql script, no problem, the stored procedureis created. The I execute it, no problem, my table is here

Then I change a bit the code so I change my sql script then I execute it and this time the stored process creation failed with the following message

Msg 2714, Level 16, State 1

Server 'MX512ZN', Procedure 'PURGE_LINE', Line 19

There is already an object named 'SST_OUT_SCOPE' in the database.

How can I prevent such messages?

Thanks

Accepted Solutions (1)

Accepted Solutions (1)

former_member188958
Active Contributor
0 Kudos

In 12.5.4, have your script that creates the procedure drop the table before it creates the procedure.

In current versions of 15.x or 16.x, you can use

sp_configure "deferred name resolution", 1

to avoid the error while creating the stored procedure.

-bret

Former Member
0 Kudos

Bret /Andrew,

just read in sybase manuals as this option will helpful in non-warm standby replication environment also .

Can you give me a demo or with set of SQL cmds that will fail(makes threads down ) in replication env while this option is turned off.

It will be helpful to understand for me more .


Answers (3)

Answers (3)

Former Member
0 Kudos

Thanks all for your feedback,

Indeed I'll try by adding the 'exec' function.

Best,

Simon

Former Member
0 Kudos

As Bret said - "deferred name resolution" is the way to avoid compiler messages like this.

In pre "deferred name resolution" versions (12.5.4 is one of these) the only way to overcome this is to rely on execute immediate logic.  But if you have a lot of references to the table in the code your code will become messy.  Unless you use execute('create/drop/select/&c') consistently for your table in the SP body you will keep getting compile error messages "object already exists" and you proc will fail to compile.

HTH,

Andrew

former_member89972
Active Contributor
0 Kudos

Simon

Please check the id, name, uid and crdate from system table sysobjects  to see how and when your procedure and table are created with the steps you have.

Some caveats do apply for drop and create of an object

e.g.

-- Single batch will fail

drop  object if exists

create  object

go

-- Two batches will work

drop object if exists

go

create object

go

HTH

Avinash