April 27, 2009 at 6:45 am
Hello All,
I wrote a backup procedure which starts with the sp_procoption automatically and loops with WAITFOR.
That works fine when I restart the server. To start this procedure in business I only find the solution with EXEC. But when I close the Management Studio the Procedure is skipped - is there any command to start the procedure in background?
The next question is - can I get the status of a procedure if it's running or not?
and the last thing - can I stop a running procedure, which has been started with sp_procoption?
Thanks a lot!
Stefan
April 27, 2009 at 8:09 am
wagner (4/27/2009)
Hello All,I wrote a backup procedure which starts with the sp_procoption automatically and loops with WAITFOR.
That works fine when I restart the server.
I assume you're using SQL Express and try to schedule backups on a regular schedule. If I'm correct, the easiest option would be using the Windows task scheduler together with sqlcmd.exe.
Another (more advanced) option is using Service Broker. Over at SqlTeam.com they have a good article about it.
http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express
To start this procedure in business I only find the solution with EXEC. But when I close the Management Studio the Procedure is skipped - is there any command to start the procedure in background?
The reason the procedure stops is because when you close SSMS, you close the connection and all runnning transactions will be rolled back.
The next question is - can I get the status of a procedure if it's running or not?
and the last thing - can I stop a running procedure, which has been started with sp_procoption?
The first part is a bit tricky, but can be done using the DMV's sys.dm_exec_session, sys.dm_exec_requests together with sys.dm_exec_sql_text. But be carefull, while it might not apply to your situation, it's always possible that more than one user is executing the same procedure.
To stop a running procedure you have to kill the process. Once you found the SPID of the process using the DMV's just run KILL and the SPID (g.e. KILL 56).
Hope this helps
[font="Verdana"]Markus Bohse[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply