how do I run a "disconnected" stored procedure

[sqlchopper]

Current app runs a stored procedure which runs 16 nested procedures and takes from 3 to 10 min depending on how must data needs download from the as400. The problem is that there are some other web apps on the web serverr that use old com objects that have memory leaks and/or does not release memory. My apps runs without and problems. However when when enough memory is chewed up we get a memory error. Network services then stops IIS and restarts.
If my app is running the stored procedure it hang when they reset IIS. Is there a way to run the stored procedure and continue on with out waiting on the return code. I would change the stored procedure to update a flag somewhere that I could checkevery x min to see if it has completed.

[Estuardo]

G'd evening sqlchopper!
If that happens often you should consider to run your sproc(s) within transactions. It will not just avoid your application hang, but also and more important it will keep the data integrity.

[Kate Albany]

sqlchopper,

If this SP can be run by something other than the web app, then why not create a batch file which uses 'sqlplus' to connect to the DB to run the SP. This can be scheduled to run automatacally or on demand. That way you can start/stop IIS - hell even uninstall it - and it will not affect your SP.

If you do need to run it from the web app then there is no way to stop it being killed when IIS is shutdown. Depending on what your using to connect to the DB ... OLEDB/ADO etc ... you can execute using the 'asynchronous' flag, this allows you to call into the DB and have it return control to the app immediatly, like you wanted. However if the web app dies (IIS shutdown) your connection to the DB will die along with it, and the DB will terminate your session and everything it was doing, I.E. running your SP. and will rollback any un-committed transactions.

Running the SP out of IIS's control (batch script) is the only way.

I have just realised that 'sqlplus' is for the Oracle DB and this thread is for MS SQL DB, sorry force of habbit, I am more familiar with Oracle. I'm not sure if there is something similar to sqlplus you can use with MS SQL.

Kate

[sqlchopper]

sqlchopper,

If this SP can be run by something other than the web app, then why not create a batch file which uses 'sqlplus' to connect to the DB to run the SP. This can be scheduled to run automatacally or on demand. That way you can start/stop IIS - hell even uninstall it - and it will not affect your SP.

If you do need to run it from the web app then there is no way to stop it being killed when IIS is shutdown. Depending on what your using to connect to the DB ... OLEDB/ADO etc ... you can execute using the 'asynchronous' flag, this allows you to call into the DB and have it return control to the app immediatly, like you wanted. However if the web app dies (IIS shutdown) your connection to the DB will die along with it, and the DB will terminate your session and everything it was doing, I.E. running your SP. and will rollback any un-committed transactions.

Running the SP out of IIS's control (batch script) is the only way.

I have just realised that 'sqlplus' is for the Oracle DB and this thread is for MS SQL DB, sorry force of habbit, I am more familiar with Oracle. I'm not sure if there is something similar to sqlplus you can use with MS SQL.

Kate

thanks, I'll look and see. What I did in the mean time was chopped the stored procedures up and run each one seperatly. it reduced the resources and time to less that 5 min. What I did understand that is all the temp file files created are kept until the procedure is finished. What I did not realize is that when you nest sp's they are all kept untill the top most procedure is done.

[sqlchopper]

G'd evening sqlchopper! If that happens often you should consider to run your sproc(s) within transactions. It will not just avoid your application hang, but also and more important it will keep the data integrity.

how do I do that?

[Estuardo]

G'd evening Kate & sqlchopper!
From BOL (SQL Books On Line)
Transactions
A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.
Also you can donwload the BOL from here .
Good luck
Estuardo

[sqlchopper]

Thanks, just a quick note. After several failed attemps I created a sp that updates a control file record which takes only a second or less. I then created a 3 sp jobs that run ever 5 min and check to sse if falg is turned on. if it is it then executes the sp I need run. since I have 20+ plants that could request it I found the 3 job was sufficent to noy hold anyone up.
I have since discarded that and now run each sp (23 of then) in the web app individuly and the total run time is 5 min or less.