Hi,

>You'll be glad to hear I don't intend to preach further on this. I
>probably should have stayed "shut up" on the subject.

Thank you for your response but forgive me for asking you to preach further.
The thing is we need to create these stored procedures and canot predict in
advance which ones are required - the number of possible permutations are
very large but for any given usage the number required is fairly small.

In short - I need to understand under what circumstances I can create stored
procedures and then make use of them so that they work correctly and dont
crash the server. We have made some progress with some threading issues and
seem so far to have a non-crashing app but I would like to understand these
issues you describe - so we can ensure things are done safely.

>There are other reasons why compiling SPs and calling them should be
>completely discrete...like, the committing of a SP will be deferred in all
>cases when any objects it refers to are involved in uncommitted work.

Would the compiling of a SP will be deferred in the form of the call to
commit for that transaction blocking until other transactions commit or
throwing deadlock exception - or will it 'appear' to commit but not yet be
usable(even though it is visible to transaction in system table) until other
transactions commit also. Also by 'any objects it refers to' - I assume we
are talking about other metadata objects such as tables and other stored
procedures. Actualy any clarification here would be greatly appreciated.

>If you're using superserver, the only assumption you can make if you're not
>the only user on the system is that your SP isn't going to be available
>until after a database shutdown.

We can gurantee that our process is the only user of the database - it is
intended to run embedded in deployment. So hopefully by understanding these
dependancies fully we can manage this.

>That's a queer assumption. An uncommitted stored procedure isn't yet a
>stored procedure. Creating (and thereby compiling) a SP is a DDL
>operation. Calling it is a DML operation. If you have both of these inside
>the same transaction, you're dancing Les Sylphides on freshly-poured
>concrete. The engine should throw an exception. It doesn't, and that's a
>bug, albeit one of great antiquity.

So just to be clear here - we are saying that a stored procedure must not be
used within the transaction in which it was created ?

Can we safeley create a stored procedure in a transaction(where no DML
occurs), commit this transaction then start a new transaction and do DML
using this stored procedure in this new transaction(assuming above mentioned
dependancies are managed and procedure 'compiles' when it is commited). And
at the same time have other DML operations occuring in seperate transaction
?

Thankyou for your time and patience
Ryan