Quick Tip: Invoke A URL From SQL Server Agent

by Jon Davis 10. October 2008 23:10

Sometimes for a web application it makes sense to perform batch operations on the web site itself. For example,

  • Your web app has matured with deep-rooted dependencies upon System.Web.HttpContext.
  • You want to reuse your object libraries that are already available in your web site. 
  • You require business logic that is only available in (misplaced) .ASCX controls.
  • You want to use ASP.NET templating to generate your e-mails.

But there's a problem: ASP.NET does not have an infinite loop to spawn batch processes. Either something has to keep pounding on ASP.NET, or else something has to invoke the web site to get it to perform the batch job.

Several years ago (wow, half a decade ago), as a matter of curiosity about the same problem worth solving (but having no actual instance of the problem, hehe), I pondered and published a prototype of the notion of having an object hosted in the Application collection that has a timer on it that routinely performs a GET operation on a web page to keep the application running indefinitely while allowing a page to be re-invoked frequently. I abandoned the thought because I wasn't sure what the implications would be, and in fact my little disclaimer/warning on the published prototype is nothing but FUD.

In a previous job, I saw regular use of custom C# console applications invoked with the Windows Task Scheduler, which is one way of doing the job. I think, though, that having seen the Task Scheduler for the first time in Windows 98 I can't help but reminisce of crappy OS times whenever I see the same scheduler today. Perhaps my discomfort is lacking merit.

I've also seen plenty of Windows services that are used for this purpose. Sadly, these seem to be as overkill as Task Scheduler seems vulnerable.

But I think this might also be a problem for SQL Server Agent to solve, which exists to be an application-level task scheduler and can simply perform an HTTP GET to spawn the batch processes. I initially considered using VBScript to load an ActiveX object that would perform an HTTP GET operation, but then I realized that I needed to research available COM objects in a Server 2008 context, and COM and specific ActiveX products from Microsoft are so old. I considered writing a COM object in .NET with a CCW (COM-callable wrapper) and go through the hassle of registering it. But then I realized, meh, why not just call wget? Wget is a GNU app that does exactly what the name infers: it does a World Wide Web 'GET' operation, spitting back the results to a file while displaying the HTTP headers to the console (although you can disable all of that). So the following is a configuration that I came up with.

  1. Select a URL that's on a different root branch from your user workflow, where you will store your batch jobs. For example, I like ~/batchjobs/mybatchjob/default.aspx (invoked as "/batchjobs/mybatchjob")
  2. Temporarily increase the timeout to suit your process's needs.
  3. In Page_Load(), slap on a Response.ContentType = "text/plain", invoke a RunXXX() method where you do your stuff, and plan on posting progress status information to the client using "Response.Write(" stuff \r\n"); Response.Flush();" wherever in a console app you'd normally use Console.WriteLine().
  4. Install SQL Server Agent on the database server.
  5. On the same server as SQL Server Agent, install wget for Windows. Add its directory to the %PATH% system environment variable, and grant ACL security rights on wget.exe to the designated SQL Server Agent system user account.
  6. In a new Job, use type CmdExec in a task to invoke: wget http://www.yoursite.com/batchjobs/mybatch1/ --spider -q
  7. The argument --spider drops the saving of the response body, and -q drops the HTTP header rendering on the console. If you want to retain the console output and HTTP response body, you can drop and/or replace the "--spider -q" arguments; use --help for more options.

The web page will be executed on the same schedule as the Agent.

I hope this was helpful.

kick it on DotNetKicks.com

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Tags: ,

Techniques


 

Powered by BlogEngine.NET 1.4.5.0
Theme by Mads Kristensen

About the author

Jon Davis (aka "stimpy77") has been a programmer, developer, and consultant for web and Windows software solutions professionally since 1997, with experience ranging from OS and hardware support to DHTML programming to IIS/ASP web apps to Java network programming to Visual Basic applications to C# desktop apps.
 
Software in all forms is also his sole hobby, whether playing PC games or tinkering with programming them. "I was playing Defender on the Commodore 64," he reminisces, "when I decided at the age of 12 or so that I want to be a computer programmer when I grow up."

Jon was previously employed as a senior .NET developer at a very well-known Internet services company whom you're more likely than not to have directly done business with. However, this blog and all of jondavis.net have no affiliation with, and are not representative of, his former employer in any way.

Contact Me 


Tag cloud

Calendar

<<  November 2014  >>
MoTuWeThFrSaSu
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567

View posts in large calendar