Hi,
I have two queries saved on my SQL Server 2005 and I would like to have them running at certain intervals, e.g. every 24 hours. Is there a way to automate these queries and schedule them via SQL Server Management Studio?
Thanks for all the help in advance.
Ym
PS: New here and have intermediate level knowledge of SQL server
Create a job with a step in which you execute your queries; the job can be scheduled at your needs.
You need to use the SQL Server Agent, installed as part of SQL Server. This is the background service that's responsible for running scheduled maintenance and backup tasks.
Expand the SQL Server Agent node in SQL Server Management Studio, you should see a tree node called "Jobs"
Right-clicking this will give you the option to add a new job. Jobs consist of a series of steps to be executed in order, and when you add a new step to your job, you can choose various types of step, including "Transact-SQL Script"
Create a new job, add a single T-SQL step, put the queries that you want to run into the step properties, and then use the "Schedule" option in the Job properties to create a recurring schedule that'll run the job every 24 hours (or whenever).
At a previous employer the operations department had a task sheduling application. They prefered to use the command line tools that come with sql server to execute jobs (stored procedures) on a scheduled basis. This way the "task scheduling application" could recieve an exit status (pass/fail, ok/error) and hold up dependent jobs.
I'm sorry I don't remember the name of the command line tool, but it was part of the sql server distro. I also do not remember the name of the task scheduling application. It was not the windows task scheduler. It was something enterprise level used to manage the nightly cycle.
Not sure of the scale of your project, but this is another way to go.
You can use the SQL Server Agent, which will allow the server to run the script/stored procedure.
You can also use the Windows Task Scheduler, either on the server or on any other server or workstation to schedule isqlw/sqlcmd to execute your script/stored procedure.
SKapsal's comment on a command line tool for executing SQL commands is a reference to osql (for SQL2000) or sqlcmd (for SQL2005+). You could save your script a file and run it from that command line using Windows Task Scheduler or something similar.
SQL Agent is still the preferred solution, however, as it provides GUI controls for job creation, scheduling, logging and viewing job execution history/results.