Scheduling SQL Job with SSRS

Imagine this: you are  at an technical interview of a SQL developer position. The interviewer asks: tell me someway to schedule an SQL server job without using SQL Server agent. If I am at this kind of situation, I will be surprised by the question.  I probably will miss the question and only focus on find out the reason not to use SQL Server Agent.

The fun thing is: I am going to share our unconventional ways to schedule SQL jobs (so that you won’t be surprised when you are asked about it)

First some background, I am working in an analytics team of about 40 people. We support many processes and need to load data to our SQL server databases on regular base (some jobs are run several times a day,some jobs are run daily, some are weekly and some are monthly).  We are in a process to get our own server for the team. However, we don’t have access to SQL Server Agent. I don’t know why. But things have been like this for several years already. During these several years, some team members come up with a smart way to schedule jobs —-using SSRS server.

We do have full access to our SSRS server, so we can schedule report reported jobs. Then, someone find that could be utilized fully to schedule SQL jobs. Simply  put the SQL command (for our cases, we use exec sp_storeProcedureName) in the Data set query script window. Next, deploy the report SSRS server. Finally, schedule it the way you like. In this way, the SQL command are executed automatically at time we desire.

If you google ‘scheduling SQL jobs with SSRS’, you will only get irrelevant search result (I tried). That makes me want to share our home-made recipe to scheduling SQL job without SQL Server Agent.

Next post, I want to share with you our home-made recipe of monitoring stored procedure execution.

Leave a Reply

Your email address will not be published.