Wednesday, February 08, 2006

Has Anybody Seen My Code?

About 6 years ago, I wrote some great code as part of an ASP site. There was a SQL Server stored procedure that ran for a long time, an because of this, it would periodically update a status table with percent complete (it was performing cursor-based operations that applied a set of rules to a set of data, so it could accurately report how far long in the set of rules it was at any one time). The stored proc was kicked off from ASP/ADO script, and that web page would keep refreshing itself in order to update the % complete.

I had a very similar need today: to kick off a stored procedure from an OSASPADO (Old-School ASP/ADO) site and load a status page that kept refreshing while the proc continued to execute on the server. You know what? I can't find my old code, and I couldn't find a way to recreate it!

In the world of classic ASP running on IIS 6 (Windows 2003 Server) using a SQL Server 2000 backend, connections appear to be recycled shortly after the script finishes executing. I tried probably 6-10 different ways of starting the stored procedure, but it would always stall out within a minute.

I finally gave up, and went with the out-of-band approach: The ASP script would insert some parameters into a "jobs" table, and then a SQL Server scheduled job (via the SQL Agent) would check this table, and execute the stored procedure using these parameter values if data existed in the "jobs" table.

I realize that this description is a little bit on the abstract side, but how the heck was I able to accomplish this task in the past on IIS 5/Windows 2000/SQL Server 7.0???