Scheduling jobs in SQL Server 2005 Express Edition Alternatives?

Question

question

tabularyee asked May 24, '11

Scheduling jobs in SQL Server 2005 Express Edition Alternatives?

Hi, I have created a synonym to query an overseas database, which results in time out issues when joined with a table that I have in my local database. I've made sure I have as little information as possible to select, but it's still not possible to have an operational joined query without the timeout issues. I have also changed the timeout limit. The solution I made was to download the data from the synonym and store it in a local table, which works fine. However, this data is updated every day. I wanted to use agents to schedule a job to update the table at daily intervals, but agents don't ship with the Express Edition. Is there another way to Schedule tasks in the express edition or avenues I could explore? Thank you very much in advance.

sql-server-2005 sql-agent synonyms

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2011-05-24T09:56:24Z

Tim answered May 24, '11

If your job is just TSQL you could use SQLCMD and use Windows Tasks Scheduler to call the script. You could also use Powershell and schedule it the same way with Windows Tasks Scheduler.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tabularyee commented · May 25, 2011 at 02:22 AM

Thanks a lot!

0 ·

Answer 2 · 2011-05-24T09:56:48Z

KenJ answered May 24, '11

You can schedule your query with sqlcmd and the windows task scheduler or install a 3rd party SQL agent, such as this one on CodePlex - [ http://standalonesqlagent.codeplex.com/][1] [1]: http://standalonesqlagent.codeplex.com/

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2011-05-24T10:17:29Z

Oleg answered May 24, '11

While highly unreliable, another method of "scheduling" jobs exists without the need of using anything outside of the open SSMS query window. It is not to be ever used, but might be worth mentioning because it exists. It is the combination of **waitfor time** and **go N**, i.e.

waitfor time '23:00:00';

print 'Beginning scheduled job, please wait...';

-- some statements go here

print 'Done.';
print '';

go 100

The above "schedules" execution of the batch for 11 PM every day for next 100 days or until someone closes the query window or restarts the service :)

6

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim commented · May 24, 2011 at 10:27 AM

This warranted being its own answer in my opinion. While I would NEVER do this in a production environment I give it a +1 for creativity. Kudo's for thinking outside the box today.

4 ·

KenJ commented · May 24, 2011 at 10:28 AM

set that up in a WHILE 1=1 loop, put it in a startup stored procedure, and you're now independent of the SSMS connection and restarts :)

2 ·

Oleg commented · May 24, 2011 at 10:35 AM

@KenJ This way the method could be pimped as the easiest one to add a Job Agent functionality to the express edition. Add the try/catch blocks and the **send email** snippets to it and the agent is now complete with notifications feature :)

1 ·

tabularyee commented · May 25, 2011 at 02:23 AM

Thanks for this too, I also found this approach. http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express Which uses the service broker.

1 ·

Tim commented · May 24, 2011 at 11:01 AM

You all have just created a new "interview" question for me. I shall now start asking potential candidates, "How would you configure SQL Express Edition to schedule SQL jobs including email notifications".

0 ·

Show more comments

question