Create Windows Task Scheduler to Run SQL Query Periodically

Introduction

When a SQL Agen is not available in a computer or want to execute a SQL Query periodically from a remote computer then Windows Task Scheduler can be used in case of SQL Agent.

Here in this blog post, we will create a Task Scheduler that will periodically execute a SQL query.

Getting Started

The Windows Task Scheduler does so many tasks periodically based on the criteria a developer sets. It enables you to automatically perform routine tasks on a chosen computer. It monitors the criteria that are set by a developer or a computer operator and then executes the tasks when those criteria are met, criteria is referred to as triggers.

Some examples of the tasks that a computer operator can use the Task Scheduler to execute are:

• Start and Close Program
• Send Email Message
• Display Email Message
• Run SQL Query Periodically.
• Restart SQL Server Periodically.
• Restart a computer Periodically

The Windows Task Scheduler is controlled by Task Scheduler service. This service can be find in the service list of Windows services. If this service is stopped or disabled, then all the tasks of Task Scheduler will not run at their scheduled time.

Create a SQL Script File

Create your own SQL script that you want to execute in Task Scheduler and save it as mySqlScript.sql in C drive. Here the following SQL query inserts date and time into time stamp table.

 INSERT INTO TimeStamp values GETDATE()  

Task Scheduler do not support execution SQL Query directly, it can be execute using Task scheduler by following two ways

1. Schedule batch file in Task Scheduler.
2. Run PowerShell from Task Scheduler.

Schedule Batch File in Task Scheduler

Create a batch file in the same directory where the SQL file exists with name mybatchfile.bat, itt will access the above created SQL script file and execute it in the SQL Server. Copy the below batch code and past it inside your batch file. It will run the SQL query in SQL server from the provided path. Visit this blog Run SQL Script from Batch File to know how the batch file works.

 sqlcmd -S "ServerName" -U "Username" -P "Password" -d "Database" -I -i C:\mySqlScript.sql  

Batch Command to Execute SQLCommand

Once you created the file replace value of the parameters:

• Replace the ServerName with your computer name where SQL server is running.
• Replace the DatabaseName with your SQL server database name.
• Replace the UserName with your SQL server login name.
• Replace the Password with your SQL server password.

Test the batch file by running it in command prompt or double clicking on it. if everything is ok then Follow the steps to schedule.

1. Open the Task Scheduler by typing Task in the Windows Search Box.
2. Click on Create a Basic Task from the Actions list on the right.
3. Name the task Run SQL Script and write details in the description (Optional)
4. Click Next and select Daily Trigger or select the option that is suitable for you.
5. Pick a time where there will be no activity.
6. Click Next and for the Action select Start a program.
7. Provide the path of batch file in the Program/Script field, you can use the Browser button to identify the batch file.
8. Click on the Next button. The last window Summary will appear.
9. Select the ‘Open the property dialog for this task when I click Finish’ then click on Finish button.
10. The Properties window will appear.
    • Select the option Run whether user is logged on or not.
    • Select Do not store password, if the Task Scheduler will run on local machine.
    • Select Run with highest privileges
    • Click on OK button. It will ask the system username and password if you do not select the Do not store password.
    • Provide the system username and password.
11. Your Windows Task Scheduler is ready to run.

Run PowerShell from Task Scheduler

Create a PowerShell file with below PowerShell code and save it as PShellSQL as name in C drive. Later we will use this PowerShell file in Windows Task Scheduler.

 sqlcmd -S "ServerName" -U "Username" -P "Password" -d "Database" -I -i C:\mySqlScript.sql  

Powershell and SQL

Do the same thing that you did for batch file to replace the parameters like server name etc. Follow the below steps to create Windows Scheduler.

• Follow the steps from 1 to 6 of section Schedule batch file in Task Scheduler.
• Type PowerShell.exe in the Program/Script field.
• Add below values as argument in the add arguments box.

   //windows task scheduler powershell  
   -file C:\PShellSQL.ps1 -ExecutionPolicy Bypass -NoProfile -WindowStyle Hidden  
  

• Again contrinue with remaining steps of section Schedule batch file in Task Scheduler.
• Now you are done with Task Scheduler PowerShell.

Related Articles

1. Run SQL Script from Batch File
2. Run Sql Command From Command Line
3. Restart SQL Server Periodically using Task Scheduler
4. Create a JOB in SQL Server Management Studio

Summary

This blob post provided all the details to create a Windows Task Scheduler, Create a Powershell run scheduled task and schedule batch file in task scheduler. I hope this blog post is helpful to you.

Thanks