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

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

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

tabularyee avatar image tabularyee commented ·
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

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

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 commented ·
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 ·
KenJ avatar image KenJ commented ·
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 ·
Oleg avatar image Oleg commented ·
@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 ·
tabularyee avatar image tabularyee commented ·
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 ·
Tim avatar image Tim commented ·
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 ·
Shawn_Melton avatar image Shawn_Melton commented ·
@oleg +1 for me to I have seen the waittime used but not for "scheduling" something. not to through off the context of this post but @TRAD my response to your interview question: How do you do backups for your SQL Express instances? Then as you hopefully are describing how you DO backups for your SQL Express instances my answer would be to follow the same process to keep your environment consistent :) You might want to re-word your question to include "without using scheduled task".
0 Likes 0 ·

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.