How to schedule SQL query to run in Microsoft SQL Server

5 pts.
Tags:
Scheduled Tasks
SQL Query
SQL Server
I have a query that I want to automatically schedule to run daily. How do I do this?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Basically you have two ways to do this:

The first one is scheduling an operative system task (start-accessories-system tools-scheduled tasks) where you call the “SQLCMD” shell command to perform a query

The second one is programming a new job on SQL server management studio (SQL server agent – jobs new) with a transact-sql step with the command.

cheers

——————————-

If you choose the second option, here are some details (assuming 2005 or 2008 version, since you did not specify):

Creating the job (in SQL Server Management Studio)
:

  • In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
  • Expand SQL Server Agent, create a new job or right-click an existing job, and then click Properties.
  • In the Job Properties dialog, click the Steps page, and then click New.
  • In the New Job Step dialog, type a job Step name.
  • In the Type list, click Transact-SQL Script (TSQL).
  • In the Command box, type the Transact-SQL command batches, or click Open to select a Transact-SQL file to use as the command.
  • Click Parse to check your syntax.
  • The message “Parse succeeded” is displayed when your syntax is correct. If an error is found, correct the syntax before continuing.
  • Click the Advanced page to set job step options, such as: what action to take if the job step succeeds or fails, how many times SQL Server Agent should try to execute the job step, and the file or table where SQL Server Agent can write the job step output. Only members of the sysadmin fixed server role can write job step output to an operating system file. All SQL Server Agent users can log output to a table.
  • If you are a member of the sysadmin fixed server role and you want to run this job step as a different SQL login, select the SQL login from the Run as user list.

Scheduling the job (in SQL Server Management Studio):

  • In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
  • Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and click Properties.
  • Select the Schedules page, and then click New.
  • In the Name box, type a name for the new schedule.
  • Clear the Enabled check box if you do not want the schedule to take effect immediately following its creation.
  • For Schedule Type, select one of the following:
  • Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started.
  • Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition.
  • Click Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the Frequency, Daily Frequency, and Duration groups on the dialog.
  • Click One time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog.

If your version is 2000, is an example on how to do it in enterprise manager (the procedure is not very different)

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Technochic
    Can you please tell us what version and patch level of SQL? Thanks!
    57,010 pointsBadges:
    report
  • Michael Tidmarsh
    For more tidbits on Microsoft SQL Server, check out Mr. Denny's SQL Server blog.
    34,040 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following