SQL Server Performance Forum – Threads Archive

Procedure running recursivly

Please see folowing script CREATE PROC MonitorUserActivity AS SET NOCOUNT ON WHILE 0 = 0 BEGIN WAITFOR DELAY ’00:00:10′ EXEC RefreshUserActivity END i want this script to execute in every 10 sec. I execute this script in QA and running fine. but when i try to close QA, its giving a mesg "whether u want to close ur script" <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> . i want to run it 24/7 .its already runnin in some other servers, donno hw they implemented this script . any idea?

Well, you may have created the procedure by running the script, but you haven’t called the procedure, so the procedure won’t be running at all. The message whether you want to close the script is just to give you the opportunity to save your script as a file. It has nothing to do with procedures that may or may not be running. Next point – not that I would recommend doing so, but: it would only be a recursive procedure if instead of EXEC RefreshUserActivity you put in EXEC MonitorUserActivity. Recursive means that a procedure calls itself. You are probably looking for a way to schedule the procedure as a job, which is best done in the Enterprise Manager – although I’m sure there is a T-SQL command for it as well.

Sorry i used wrong terminology "recursive" . i want this script to call ‘RefreshUserActivity’ each 10 sec.Please tel me procedure how to do it.As i told i saw this in many servers .But i donno how to implememnt it myselft. Please guide me. Thank you

Look up job scheduling in BOL. Create a job through the Enterprise Manager.

No.I cant do that way as other systems executin this script without createing any job. any alternative for this. isql from command prompt wil help?wht is tht for?Sometime i saw ppl executing ISQL.wht is it for?it has anythin to do wiht it? pls advice

If there are no jobs created for the other systems then those systems probably have a client app that sends an instruction every 10 seconds to SQL Server to run the procedure (could also be set as the refresh rate of a form). Basically the same as a recurring job on SQL Server, except that there is some network traffic involved. What is the purpose of the procedure? How does it relate to your client app?

I am sure ther is no job created in server for that. Thsi will call other procedure RefreshUserActivity, which will write user activities in server to a table. i wonderd seein ther is no job created in server for this. I want to make it work even if i close QA . any idea?

Just remove the wait for delay statement and take the time taken to complete this procedure and keep the execution schedule of the job under SQLAgent –> Jobs. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>