When running the following script in ElevateDB Manager e.g.

SCRIPT
BEGIN
Use "my_db";

CALL my_sproc();

-- loads more queries

EXECUTE IMMEDIATE 'DROP PROCEDURE my_sproc;';

-- more queries

END

On the Drop Procedure line, I get :

ElevateDB Error #300 An error occurred with the statement at line 2347 and column 19 (Cannot lock the procedure my_sproc in the schema Default for exclusive access)

Can I work around this with some query to unlock exclusive access before the DROP PROC command? Or do i have to do the DROP PROC in a seperate script ?

Using ElevateDB 2.05 Build11

Thanks in advance
Tony Wood

Hmm...  works for me.  Make sure any scripts in EDB Mgr that refer to that
procedure are Unprepared.

David Cornelius
Cornelius Concepts

Hi David

I forgot to say that this was happening in local mode.

The only other script which refers to that SProc is the one which creates it. It has previously finished successfully in ElevateDB manager. When the tab for the creation script is clicked, the Prepare icon between Close and Execute icons says 'Prepare' so presumably it is Unprepared. Nothing else calls the SProc within the procedures and functions in those scripts or any other.

Could it be a timing  issue ?
Tony Wood

"David Cornelius" wrote:

Hmm...  works for me.  Make sure any scripts in EDB Mgr that refer to that
procedure are Unprepared.

David Cornelius
Cornelius Concepts

Well, it sounds like it should work for you.  That "Prepare" icon is what I
was referring to and you're right with it being unprepared.

Can't imagine it being a timing issue.  Can you delete the stored proc
manually?

I've run my test script a couple of times, even when the SP's create script
was still prepared and it works (which actually surprised me).  I'm in local
mode as well, but I also tried this against a server database with the same
success.

I'm running 2.05 b10.

David Cornelius
Cornelius Concepts

Tony,

<< Can I work around this with some query to unlock exclusive access before
the DROP PROC command? Or do i have to do the DROP PROC in a seperate script
? >>

It has to be a separate script.  When any script/procedure/function is
prepared, all referenced procedures/functions are opened/compiled and kept
that way until the current script is un-prepared.  Therefore, any DROP will
always fail because it can't get exclusive access to the procedure.

--
Tim Young
Elevate Software
www.elevatesoft.com