create Scheduler task to invoke SQLCMD

Question

I have a Requirement where i have to run a SQL Script in SQL server on a weekly basis .Result of this script has to be mailed.I want to automate this process.I don't want to go with SSIS Jobs. I have searched i have found few options like Creating a Windows Scheduler task to invoke a SQLCMD.Can Someone Assist on how to create Scheduler task to invoke SQLCMD

Answer 1

sqlcmd is just a command line tool. Put your SQL in a script file, and call it from the tool with right DB server and credentials. We can help you to figure how to make it work if you have a specific problem, but you should make your own tries before and tell us what goes wrong.

You will easily find examples on how to run a script : http://msdn.microsoft.com/en-us/library/ms170572.aspx More details on parameters : http://msdn.microsoft.com/en-us/library/ms162773.aspx

example :

 sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\EmpAdds.txt

For task scheduler, it's easy to use but beyond SO scope, and it depends heavily of your version of windows, so (again), try yourself first.

Please note that you can also use SQL Server Agent to schedule your jobs.

Answer 2

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.