Solved

Having a SQL job constantly run

Posted on 2011-02-21
4
259 Views
Last Modified: 2012-05-11
Is it possible to program a SQL job to run constantly?  I have a very simple SQL update script that I would like to have run as a job, literally, every second.  Unfortunately, it appears that the most that SQL can do is to run a job every 60 seconds.
0
Comment
Question by:apitech
  • 2
4 Comments
 
LVL 11

Expert Comment

by:rajvja
ID: 34943195
Hi,

   Are you sure that you want to run for every second????
Establishing the connection and execute statement will take a while(typically more than a second).
And also burden on server if you run like that
0
 
LVL 4

Expert Comment

by:rocky_lotus_newbie
ID: 34943330
If you are convinced, you need to run it every second - There is an alternative, to include the script in an infinite while loop (while 1=1) with a Waitfor command (SYNTAX: WAITFOR DELAY '000:00:01').

This does not run the script for every second but will ensure that there is a time gap of 1 ssecond between successive executions.

Please consider implementing this only if you think it meets ur requirements as running something for every 1 second is not advisable.

Thanks,
0
 
LVL 1

Author Comment

by:apitech
ID: 34943733
Hi rocky_lotus_newbie:

I don't mind a gap of one second.  If I use the syntax that you used, such as WAITFORDELAY '000:00:01', would that do what you're suggesting (i.e. have a gap of one second) and allow for a safe run of my script?
0
 
LVL 4

Accepted Solution

by:
rocky_lotus_newbie earned 500 total points
ID: 34944149
Yes, Below is the Syntax. Make sure you test it few times before implementing.

while (1=1)
BEGIN
-- Write your Query/Script here
WAITFOR DELAY '000:00:01'
END
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question