How to schedule to run SQL script on SQL Server?

Question

I want to use MS SQL Transact-SQL to schedule to run a script C:\script\my_script.sql at 6 pm every day, except Saturday and Sunday.

Then, the result is saved in the folder C:\data\xxx.csv where xxx is the date of running the script, e.g. 2019-2-18.

Here I want to schedule a script (or auto backup a table) which get complete data from a sql table and store it in another table with that day date as database name, which can be use to see records of data on any day.

Furthermore, how to delete the schedule task?

I used server agent but I cannot view the text.

Thank you very much.

Answer 1

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

Have a look here https://learn.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)