Hmmm, :)
Can't say for sure, but I think the trigger will have to complete before control is returned (in Synch mode) or the connection becomes available (in Asynch).
Here's another thought: create a job for SQLAgent to run. In your SQLEXEC() statement, call
sp_start_job
. Since not all users may be able to start the job (not everyone is system administrator <g>), you could create a stored procedure that will call sp_start_job with the correct parameters and
GRANT EXEC ON p_storedproc TO PUBLIC AS DBO
.
SQLEXEC() would return weither the job has started, not weither it succeded or not.
I've never tried this, so it's a little of a wild guess...
>Hmmm, this is probably not the best way to get around this (hopefully someone with more experience can give you a better answer). But here is an idea. What about creating a bogus bogus table with say, one field. On this table you could put an insert trigger that holds the code that your stored procedure currenlty has. Change your stored procedure to simply insert a value into your bogus table and then return. This will cause the trigger to fire and run your code while at the same time returning control to your application. Just an idea...
Sylvain Demers