April 8, 2008 at 10:14 am
Hi,
I want a stored procedure to be executed every 10 minutes. I'm new to SQL so I don't know much at the moment. I looked around on the net and it seems that jobs are the way to go. So I've attempted to created a job that will execute my stored proc every ten mins. However, it doesn't actually work. Any ideas what is wrong with this.....
USE NetLicenses;
GO
CREATE PROCEDURE sp_CheckLicenseStatus
AS
BEGIN
UPDATE LicenseInfo SET TimeData = GETDATE()
END
GO
USE msdb ;
GO
EXEC dbo.sp_add_job
@job_name = N'LicenseStatusCheck';
GO
EXEC sp_add_jobstep
@job_name = N'LicenseStatusCheck',
@step_name = N'Checking status of licenses',
@database_name = N'NetLicenses',
@command = N'exec sp_CheckLicenseStatus';
GO
EXEC dbo.sp_add_schedule
@schedule_name = N'RunEveryMinute',@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 10;
GO
EXEC dbo.sp_add_jobserver
@job_name = N'LicenseStatusCheck' ;
GO
Thanks in advance for any help........
April 8, 2008 at 3:02 pm
You have not assigned the schedule to the job. You need to run sp_attach_schedule so your code should look like this:
USE NetLicenses;
GO
CREATE PROCEDURE sp_CheckLicenseStatus
AS
BEGIN
UPDATE LicenseInfo SET TimeData = GETDATE()
END
GO
USE msdb ;
GO
-- declare variables to hold the job and schedule ID's for later use
Declare @job_id uniqueidentifier,
@schedule_id int
EXEC dbo.sp_add_job
@job_name = N'LicenseStatusCheck'
@job_id = @job_id Output;
EXEC sp_add_jobstep
@job_name = N'LicenseStatusCheck',
@step_name = N'Checking status of licenses',
@database_name = N'NetLicenses',
@command = N'exec sp_CheckLicenseStatus';
EXEC dbo.sp_add_schedule
@schedule_name = N'RunEveryMinute',@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 10
@shedule_id = @schedule_id Output;
EXEC dbo.sp_add_jobserver
@job_name = N'LicenseStatusCheck' ;
Exec dbo.sp_attach_schedule @job_id = @job_id, @schedule_id = @schedule_id;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 9, 2008 at 2:29 am
Hi Jack,
Thanks for your reply. I tried what you suggested, but it still didn't work. I was getting an error about the SQL Server Agent. I didn't mention in my original post that I'm using SQL Server 2005 express - and I've just read that the SQL Server Agent does not ship with the express edition!
Does this mean that what I want to do is actually impossible in the express edition? Or is there another approach I could use?
Thanks again for your help!
April 9, 2008 at 3:56 am
Yes, SQL Server Agent is not included in SQL Server 2005 Express. To run stored procedures regularly you can use Windows Task Scheduler to invoke the sqlcmd command line utility. This utility can call a T-SQL batch file containing the stored procedure.
April 9, 2008 at 4:53 am
Thanks for the reply. I've thought of another approach that is easier, i think. I've created a strored procedure which executes automatically from startup. In the procedure I simply use an infinite while loop with WAITFOR DELAY and then execute what I want. Here's the code....
CREATE PROCEDURE [dbo].[sp_LicenseStatusCheck]
AS BEGIN SET NOCOUNT ON
WHILE 1=1
BEGIN
WAITFOR DELAY '00:01'; UPDATE NetLicenses.dbo.LicenseInfo SET Timestamp = GETDATE()
END
END
GO
EXEC sp_procoption N'[dbo].[sp_LicenseStatusCheck]', 'startup', '1'
As far as I know, this will give me identical behaviour to using a 'job'. I was worried that the WAITFOR DELAY would block everything else, i.e. I couldn't execute another procedure until this one finishes (and of course it never will). However, I've tested this using another procedure running from startup and they both seem to run simultaneously, which I guess tells me that procedures running automatically from startup do so on different threads.
April 9, 2008 at 6:40 am
But you don't need to read data from that table while it's running do you? It looks like, from what you have, that it's going to lock at least a row, the whole time it's running. Unless you do dirty reads from the table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2008 at 6:46 am
The only time WAIT FOR blocks anything is when it's inside a transaction (it doesn't block, it causes the blocks to stay open longer). Of course, it keeps a SPID open and will need to be restarted if the server is bounced or the service is stopped for any reason.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 9, 2008 at 7:04 am
Hi Grant,
Thanks for the reply. The database is for controlling licenses on a client site. The database contains all the available licenses to the user, when then select a license, it is marked in the database as being "Checked out". The client then polls the database every few minutes to inform the database they are still using the license. If the client pc goes down, then the database needs some mechanism to release the license and mark it as "Free" for other users.
This is what I need the stored proc running every few minutes for. It's purpose is to compare the time the user last polled the database to the current time, if the difference is greater than some amount, it will mark the license as being free.
So in answer to your question, yes I still need to allow reads from the table, every single row in fact. So while this stored proc is running, if a user wants a license, I need to do something like SELECT License FROM LicenseTable WHERE LicenseStatus = FREE.
So am I likely to run into trouble with the approach I've used? I don't understand how the method will cause a lock.
Thanks again for your help.....
April 9, 2008 at 7:27 am
But the constant updates from a single spid that is always connected won't act like a transaction & hold that lock open? Just asking, not arguing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2008 at 8:46 am
Grant Fritchey (4/9/2008)
But the constant updates from a single spid that is always connected won't act like a transaction & hold that lock open? Just asking, not arguing.
Nope... not if implicit transactions are turned on. Another story altogether if it's turned off.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply