Design Question - calling a stored procedure

[SeanGrebey]

I am going to have an Oracle stored procedure that I am going to need to call from my application. When I call a stored procedure like this, does my application "Stop" and wait for the procedure to complete, which could potentially take hours? Or is there a way I can call and forget it. Basically it is updating data, and I want to tell it to go update that data and that's all I need to know. I don't need to worry about any errors beyond does the procedure exist.

[techgnome]

ooof! Normaly it would simply stop and wait for the SP to finish until the timeout period expires..... afterwhich a Timeout error is generated.

To allow the app to continue is a a bit tricky, BUT is possible.....

What you'll need to do is first create a job to run the SP.
Then you can create another SP with the following code (this was originaly for running a snapshot for replication, so you'll need to make some adjustments to the name and such.

BEGIN TRANSACTION 

DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

/****** Object:  Run the snapshot created earlier ******/
SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = '<snapshot_name,varchar(255),<server_name,varchar(255),>-<database_name,varchar(255),>_Repl>')
IF (@JobID IS NOT NULL)
BEGIN
  EXECUTE @ReturnCode = msdb.dbo.sp_start_job @job_name = '<snapshot_name,varchar(255),<server_name,varchar(255),>-<database_name,varchar(255),>_Repl>'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

COMMIT TRANSACTION

Then call this SP from your code. IT will fire off the job, but because it runs as a separate job, the SP you call will return immediatly and your app can continue on its merry way.

TG

[SeanGrebey]

Thanks alot, I appreciate it.