How do I prevent my ASP pages from waiting for backend activity?

If you are trying to run an executable or other backend process, see Adrian Forbes' article:

Executing long-running tasks from ASP

If you are trying to run long-running database tasks, then the following is designed for a very specific scenario. Your ASP page should:

initiate a long-running command that may cause a browser to time out; and,
not need to show a user results from the command.

This means it should trigger an event (most likely, executing a stored procedure) that does NOT return a resultset. The following example will use SQL Server, though some methods

So, let's say we have a database, called scratch:

CREATE DATABASE scratch
GO

And we have a table, called TimeRecord:

CREATE TABLE dbo.timeRecord
(
dt DATETIME
)
GO

And a stored procedure that inserts, after an odd five-second delay, the current timestamp.

CREATE PROCEDURE dbo.insertTimeRecord
AS
BEGIN

SET NOCOUNT ON

WAITFOR DELAY '00:00:05'

INSERT dbo.timeRecord(dt)
VALUES(CURRENT_TIMESTAMP)

END
GO

Now, traditionally, in ASP we would call this procedure as follows:

<%
Set conn = CreateObject("ADODB.Connection")

conn.open "<connection string>"

x = timer

conn.Execute "EXEC insertTimeRecord"
' or, as Bob likes, conn.insertTimeRecord

Response.Write "<p>Done" & fmtSec(timer, x)

conn.Close: Set conn = Nothing

function fmtSec(y, x)
fmtSec = " (" & formatnumber(y - x, 3) & " seconds)"
end function
%>

And sure enough, we would see that it takes more than five seconds for the browser to process the results.

One approach would be to use the asynchronous flag (adAsyncExecute — &H00000010, or 16):

<%
Set conn = CreateObject("ADODB.Connection")

conn.open "<connection string>"

x = timer

conn.Execute "EXEC insertTimeRecord", , 16

Response.Write "Done" & fmtSec(timer, x)

function fmtSec(y, x)
fmtSec = " (" & formatnumber(y - x, 3) & " seconds)"
end function
%>

This approach can be used in both Access and SQL Server, though we have not tested it extensively in Access.

Note that, in this case, you must not close the connection object or set it to nothing, or else you receive this error:

ADODB.Connection error '800a0e7f'
Operation cannot be performed while executing asynchronously.

Another approach might be to create a job in SQL Server (for obvious reasons, this approach is not valid for Access). Note that this technique requires elevated privileges...

Here is the code for the job:

USE MASTER
GO

DECLARE @dbname SYSNAME
SET @dbname = N'scratch' -- use an existing DB if you like

BEGIN TRANSACTION
DECLARE @JobID BINARY(16), @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT 1
FROM msdb.dbo.syscategories
WHERE name = N'aspfaq')
BEGIN
EXECUTE msdb.dbo.sp_add_category
@name = N'aspfaq'
END

-- Delete the job with the same name
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'insertTimeRecord')
IF (@JobID IS NOT NULL)
BEGIN
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job
@job_name = N'insertTimeRecord'
SELECT @JobID = NULL
END

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID OUTPUT,
@job_name = N'insertTimeRecord',
@owner_login_name = N'sa',
@category_name = N'aspfaq'

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = N'EXEC insertTimeRecord',
@database_name = @dbname,
@server = N'',
@database_user_name = N'',
@output_file_name = N''

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_update_job
@job_id = @JobID,
@start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID,
@server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:
PRINT 'Success.'

Now, you can have a stored procedure that calls this job (this will cause the call to operate asynchronously):

CREATE PROCEDURE start_job_manually
@jobName SYSNAME,
@jobID UNIQUEIDENTIFIER = NULL
AS
BEGIN
SET NOCOUNT ON

DECLARE @rc INT

SELECT @jobID = job_id
FROM msdb..sysjobs
WHERE name = @jobname

IF @jobID IS NULL
BEGIN
SELECT 1
RETURN
END

CREATE TABLE #foo
(
a UNIQUEIDENTIFIER, b INT, c INT,
d INT, e INT, f INT, g INT, h INT,
i SYSNAME NULL, running INT, k INT,
l INT, m INT
)
INSERT #foo
EXEC master.dbo.xp_sqlagent_enum_jobs
1, N'sa', @jobID

-- make sure the job isn't already running!
IF EXISTS (SELECT 1 FROM #foo WHERE running = 1)
SET @rc = 1
ELSE
EXEC @rc = msdb.dbo.sp_Start_Job
@Job_name = @jobname

DROP TABLE #foo

SELECT rc = @rc
END
GO

The procedure is a little longer than it should be, because we first check that the job isn't already running, in order to avoid an ugly error (we'll handle this in ASP below).

<%
Set conn = CreateObject("ADODB.Connection")

conn.open "<connection string>"

x = timer

set rs = conn.Execute("EXEC start_job_manually 'insertTimeRecord'")

if clng(rs(0)) = 0 then
msg = "Job was started successfully."
else
msg = "Job is already running, or there was an error."
end if

response.write "<p>" & msg & fmtSec(timer, x)

rs.close: set rs = nothing

conn.Close: Set conn = Nothing

function fmtSec(y, x)
fmtSec = " (" & formatnumber(y - x, 3) & " seconds)"
end function
%>

One word of warning: errors get swallowed up by the provider when using asynchronous methods, so you'll want to employ this method carefully.



// home // advanced search // news // categories // sql build chart // downloads // statistics
ASP FAQ

How do I prevent my ASP pages from waiting for backend activity?

Related Articles