How to Schedule a Job to Run a SQL Query Daily

How can I schedule a job to run a SQL query daily?

1. Expand the SQL Server Agent node and right click the Jobs node in SQL Server Agent and select 'New Job'

2. In the 'New Job' window enter the name of the job and a description on the 'General' tab.

3. Select 'Steps' on the left hand side of the window and click 'New' at the bottom.

4. In the 'Steps' window enter a step name and select the database you want the query to run against.

5. Paste in the T-SQL command you want to run into the Command window and click 'OK'.

6. Click on the 'Schedule' menu on the left of the New Job window and enter the schedule information (e.g. daily and a time).

7. Click 'OK' - and that should be it.

(There are of course other options you can add - but I would say that is the bare minimum you need to get a job set up and scheduled)

How to schedule to run SQL script on SQL Server?

If you don't want to change the existing settings, use the Windows Task Scheduler with SQLCMD..

Have a look here https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-run-transact-sql-script-files?view=sql-server-2017

1. Open a command prompt window.

2. In the Command Prompt window, type: sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\EmpAdds.txt

3. Press ENTER.

You can use the same solution as here: create Scheduler task to invoke SQLCMD

Your best bet would be to create a Console Application which executes
the SQL Command and performs the email.

You can then use setup a task under Task Scheduler to use the Start a
program option and run it on the schedule you prefer

EDIT:
To get rid of the dottet line have a look here:

Sqlcmd to generate file without dashed line under header, without row count

To remove column headers you should use -h parameter with value -1.
See ref (section Formatting Options).

Or from this post:

How to export data as CSV format from SQL Server using sqlcmd?

sqlcmd -S MyServer -d myDB -E -Q "select col1, col2, col3 from SomeTable"
-o "MyData.csv" -h-1 -s"," -w 700

-h-1 removes column name headers from the result

-s"," sets the column seperator to ,

-w 700 sets the row width to 700 chars (this will need to be as wide as the longest row or it will wrap to the next line)

Schedule a daily SQL query and email results as Excel file

Doing so with pure SQL will be challenging, but maybe not impossible. The most straight-forward way of doing this type of thing would be to use SSRS (SQL Server Reporting Services). This would allow you to build formatted reports as well as setup scheduling, distribution (to different formats including Excel), etc... Implementation of SSRS might be overkill for one report, but if you have other, similar requirements, it could make your life a lot easier.

A pure SQL approach would probably involve the following parts:

1. A SQL Agent job could provide the scheduling, etc...
2. SQL Server also has a feature called database mail - this is typically configured for sending alerts, but you could also use it to send the results of a query
3. The tricky part is your excel requirement. sp_send_dbmail (the proc you would use to send the mail) does allow you to attach query results to the message. But, you might have to live with something like CSV (that could be opened in Excel).

There could be some other things I'm not thinking of, but hopefully this will help.

How to auto daily execute queries SQL Server?

Create and schedule a job in sqlserver

I prefer to write SPs , but you can write queries directly.

How to schedule to run a query every day in SQL Server Express and save the result in CSV format?

PS script with task manager is the simplest way to go. however, you will need a service account to set up the task.

I assume you need the task to run whether a user is logged on or not. A service account in the simplest form is a domain account that is not tied to any users. That way, your task will not fail when your password changes or expires or ur account gets deleted (in the event that you leave the company). You can specify to use a domain account in the general tab after creating your task.

---

Related Topics