•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 401,653 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,692 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Views: 6989 | Replies: 6
 |
•
•
Join Date: Jan 2005
Location: defiance, oh
Posts: 19
Reputation: 
Rep Power: 4
Solved Threads: 1
Newbie Poster
how do I run a "disconnected" stored procedure 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.
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.
•
•
Join Date: Jul 2005
Location: Portugal
Posts: 13
Reputation:
Rep Power: 4
Solved Threads: 0
Newbie Poster
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.
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.
•
•
Join Date: Jun 2005
Posts: 71
Reputation:
Rep Power: 4
Solved Threads: 0
Junior Poster in Training
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
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
Last edited by Kate Albany : Jul 15th, 2005 at 2:31 am. Reason: mistakes
•
•
Join Date: Jan 2005
Location: defiance, oh
Posts: 19
Reputation:
Rep Power: 4
Solved Threads: 1
Newbie Poster
•
•
•
•
Originally Posted by 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
•
•
Join Date: Jan 2005
Location: defiance, oh
Posts: 19
Reputation:
Rep Power: 4
Solved Threads: 1
Newbie Poster
•
•
•
•
Originally Posted by 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.
•
•
Join Date: Jul 2005
Location: Portugal
Posts: 13
Reputation:
Rep Power: 4
Solved Threads: 0
Newbie Poster
G'd evening Kate & sqlchopper!
Also you can donwload the BOL from here.
Good luck
Estuardo
•
•
•
•
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.
Good luck
Estuardo
•
•
Join Date: Jan 2005
Location: defiance, oh
Posts: 19
Reputation:
Rep Power: 4
Solved Threads: 1
Newbie Poster
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.
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.
|
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
- Need Stored Procedure "sp_ValidateUser" for Login Page (ASP.NET)
- Need Stored Procedure "sp_ValidateUser" for Login Page (C#)
Other Threads in the MS SQL Forum
- Previous Thread: Run *.dts from T-SQL proc changing connection parameters
- Next Thread: Changing the datatype of a Column
