question

tabularyee avatar image
tabularyee asked

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-2005sql-agentsynonyms
10 |1200 characters needed characters left characters exceeded

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

Tim avatar image
Tim answered
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 comment
10 |1200 characters needed characters left characters exceeded

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

Thanks a lot!
0 Likes 0 ·
KenJ avatar image
KenJ answered
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 characters needed characters left characters exceeded

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

Oleg avatar image
Oleg answered
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 comments
10 |1200 characters needed characters left characters exceeded

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

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 Likes 4 ·
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 Likes 2 ·
@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 Like 1 ·
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 Like 1 ·
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 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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