Executing long-running tasks from ASP(author: Adrian Forbes)IntroductionThis document details how to correctly use ASP to asynchronously run EXEs, long-running processes/SQL commands and anything elsenot suited to ASP automation such as the manipulation of Microsoft Office applications. ...

First we will look at using MSMQ and a lot of custom code to create a serialised method of executing tasks from ASP, then at the end we will look at using Queued Components under Win2k to do a similar job (with only a small amount of code and configuration) to asynchronously call our custom COM objects.

Requirements

This code will require an up and running IIS server, MSMQ and Visual Basic. The examples use Visual Basic but any language that can manipulate MSMQ COM objects can be used.

Overview

This technique works by using ASP to send a request to a message queue for a certain task to be completed. A Visual Basic application will then be running on a server to carry out these requests.

The upsides of this technique are that it allows us to spawn from ASP tasks that ASP is not suited for. The messages containing the requests are sent asynchronously and the reading of them is serialised by our application if desired so that only one instance of a certain stored procedure is running at any time, or only one instance of Microsoft Word etc is created at any one time. If you don't particularly need total serialisation then multiple versions of the application can be running on either the same machine or on different machines to simultaneously process the requests.

The downsides are that it requires our custom written application to be running to service the requests. This requires that the server it runs on has an interactive user. Another downside is one inherent with asynchronous processing, namely that there is no return path. ASP issues the request and then continues on with its processing without knowing if the request has succeeded, failed or even been carried out at all.

The Process in Detail

ASP is designed for running short-lived sections of code. If your site needs to execute a task that is time-consuming then running it from pure ASP might not be the best solution. By default IIS only has 10 threads with which to execute your ASP pages. If your pages are hogging threads due to large execution times then you can see that a large website can start to suffer a reduction in performance.

One alternative is to use MSMQ. This was a stand-alone application under NT4, whereas message queuing is actually built in to the operating system for Win2k. Although this article focuses mainly on Win2k, the exact same techniques and code should work under NT4 also.

MSMQ is a "store and forward mechanism" that allows one application to store a message which is then forwarded onto a second application. The advantage of this that we will be leveraging from ASP is that the messages are sent asynchronously, so the ASP code simply sends a message and then continues on with its execution. The content of the message is entirely up to you and you might well need to devise a simple protocol that lets your target application know what you want it to do.

Our example will involve ASP sending messages that will request one of a number of possible actions. These actions will be running an EXE, executing some SQL or manipulating a Word document. Running EXEs and manipulating Word documents are not something you should be doing from ASP regardless of execution time so this technique might still be of use to you if you wish to carry out those actions.

The example is programmed to all run on the same machine but can be altered if you wish to run different parts on different machines. First of all we need a message queue. Once MSMQ is installed (either via Option Pack 4 for NT4 or via Add/Remove Programs on Win2k) you can create a queue via the MMC.

For Win2k:

• Open the Computer Management application via Start->Settings->Control Panel->AdministrativeTools->Computer Management
• Expand Services and Applications
• Expand Message Queuing
• Right-click on Public Queues
• Select New->Public Queue from the context menu
• Enter aspexe in the Name box and leave the Transactional checkbox clear
• Click OK
• Expand Public Queues
• Right-click on aspexe and select Properties from the context menu
• Select the Security tab.
• For simplicity's sake select Everyone from the list of users and check the Allow box for Full Control
• Click OK

Our queue is now set up and ready to be used. If you wish to create the queue via a script then execute the following code

Set objQI = CreateObject("MSMQ.MSMQQueueInfo")     objQI.PathName = ".\aspexe"     objQI.Create False, True     Set objQI = Nothing

The PathName is in the format of machinename\queuename but we are using "." as the machine name to refer to the local machine. The first parameter of the Create function is False indicating we don't want a transactional queue and the second parameter is True indicating we want the queue to be world-readable.

To send a message to this queue we use the following code

' Create an instance of MSMQQueueInfo. This will let us     ' manipulate queues     Set objQI = CreateObject("MSMQ.MSMQQueueInfo")          ' Specify the name of the queue we want to open     objQI.PathName = ".\aspexe"          ' Call the Open method which returns an instance     ' of MSMQQueue which represents the actual queue     Set objQ = objQI.Open(MQ_SEND_ACCESS, MQ_DENY_NONE)          ' Create a new message     Set objMSG = CreateObject("MSMQ.MSMQMessage")          ' Send the message to the queue specifying no transactions     objMSG.Send objQ, MQ_NO_TRANSACTION          ' Tidy up     objQ.Close     Set objMSG = Nothing     Set objQ = Nothing     Set objQI = Nothing

This is the basics required to send a message. In case you haven't already worked it out, the above code is quite useless as it simply sends an empty message. Still, if you have tried to run this code from VB it will let you know that the queue is working. If you've ran the code then in the Computer Management application, expand Public Queues, aspexe and click on Queue Messages. You should see the message that was sent. Delete this message by right-clicking on Queue Messages and selecting AllTasks->Purge… from the context menu. If you haven't run the code then don't worry, we'll soon be listing the complete ASP page that contains this snipet.

Now let's create something a bit more meaningful to send. Messages can contain almost anything, but we're going to use an ADO recordset. The recordset will contain one or more rows, and each row will contain two fields. One field specifies the action we want to take (we'll call it Action), and another field will specify the details of the action (we'll call it Data). So if we want to run an EXE such as notepad then the two fields will be

Action	Data
EXE	notepad.exe

If we want to execute, for example, two EXEs then we will create two rows

Action	Data
EXE	notepad.exe
EXE	calc.exe

As we add different types of Action we can mix them all up in one recordset, we'll see that in action later.

This is the code we will run to create the recordset.

' Create a recordset to store our data     Set objRS = CreateObject("ADODB.Recordset")          ' Add our fields     objRS.Fields.Append "Action", adVarChar, 20     objRS.Fields.Append "Data", adVarChar, 255          ' Open the recordset and add our data     objRS.Open     objRS.AddNew     objRS("Action") = "EXE"     objRS("Data") = "notepad.exe"

Now let's put it all together in an ASP page

<%@ Language=VBScript %> <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"> <title>Basic aspexe send</title> </HEAD> <BODY> <!-- File: BasicSend.asp --> <!-- Author: Adrian Forbes --> <%     ' Define the constants we are going to use     MQ_SEND_ACCESS = 2     MQ_DENY_NONE = 0     MQ_NO_TRANSACTION = 0     adVarChar = 200          ' Create an instance of MSMQQueueInfo. This will let us     ' manipulate queues     Set objQI = CreateObject("MSMQ.MSMQQueueInfo")          ' Specify the name of the queue we want to open     objQI.PathName = ".\aspexe"          ' Call the Open method which returns an instance     ' of MSMQQueue which represents the actual queue     Set objQ = objQI.Open(MQ_SEND_ACCESS, MQ_DENY_NONE)          ' Create a new message     Set objMSG = CreateObject("MSMQ.MSMQMessage")          ' Create a recordset to store our data     Set objRS = CreateObject("ADODB.Recordset")          ' Add our fields     objRS.Fields.Append "Action", adVarChar, 20     objRS.Fields.Append "Data", adVarChar, 255          ' Open the recordset and add our data     objRS.Open     objRS.AddNew     objRS("Action") = "EXE"     objRS("Data") = "notepad.exe"          objMSG.Label = "aspexe"     objMSG.Body = objRS          ' Send the message to the queue specifying no transactions     objMSG.Send objQ, MQ_NO_TRANSACTION          ' Tidy up     objRS.Close     Set objRS = Nothing     objQ.Close     Set objMSG = Nothing     Set objQ = Nothing     Set objQI = Nothing %> <p>Your message has been sent</p> </BODY> </HTML>

This code does the job, but as you can see we are hard coding everything so it will only ever send a request to execute notepad. Next we'll create the application that will read these requests and execute them, after that we'll come back to this code and refine it.

The process that carries out these requests will be a Visual Basic application. So create a new Standard EXE project. Add a reference to

Microsoft ActiveX Data Objects Library
Microsoft Message Queue Object Library

What versions you have installed will affect the exact names of the references. For example I am using

Microsoft ActiveX Data Objects 2.6 Library
Microsoft Message Queue 2.0 Object Library

This application has to do two things. First it has to respond to new messages appearing on the queue, second it has to get that message, read the recordset inside it and execute the instructions detailed within. Let's get it receiving new message events first of all.

To do this we need to declare two global objects.

Option Explicit ' This object holds a reference to the queue Private m_objQueue As MSMQ.MSMQQueue ' This object notifies us when a message has arrived Private WithEvents m_objEvent As MSMQ.MSMQEvent

The queue object is a reference to our queue, and the event object is used to notify us of new messages as they arrive. When the application loads we want to open our queue and specify that we wish to know of events.

Private Sub Form_Load() Dim objQI As MSMQ.MSMQQueueInfo     ' Open the queue for receive access     Set objQI = New MSMQ.MSMQQueueInfo     objQI.PathName = ".\aspexe"     Set m_objQueue = objQI.Open(MQ_RECEIVE_ACCESS, MQ_DENY_NONE)          ' Create a new event object     Set m_objEvent = New MSMQ.MSMQEvent          ' Tell the queue to use our event object to notify us of     ' new messages     m_objQueue.EnableNotification m_objEvent          ' Tidy up     Set objQI = Nothing End Sub

Now when a message arrives it raises an event which we programme our m_objEvent object to respond to.

Private Sub m_objEvent_Arrived(ByVal Queue As Object, _                                 ByVal Cursor As Long) Dim objQ As MSMQ.MSMQQueue Dim objMSG As MSMQ.MSMQMessage Dim objRS As ADODB.Recordset     ' This event is fired when a new message is in the queue          ' This time we are passed a reference to the queue as a     ' parameter of the event     Set objQ = Queue          ' Get the current message from the queue     ' Note that we are specifying a timeout on the     ' ReceiveCurrent call (1000 milliseconds which is     ' 1 second) as there is no guarantee that the     ' message is still there. For example it might     ' have already been read by another process     Set objMSG = objQ.ReceiveCurrent(ReceiveTimeout:=1000)          If Not objMSG Is Nothing Then         ' There was a message in the queue so let's process it              ' Get the recordset from the message         Set objRS = objMSG.Body                  ' Tidy up         Set objMSG = Nothing              End If          ' Once we have finished we need to re-create our     ' event notification     Set m_objEvent = Nothing     Set m_objEvent = New MSMQ.MSMQEvent          m_objQueue.EnableNotification m_objEvent End Sub

In the above code we get the recordset from the message but do nothing with it. Here is the code we'll be using to process the recordset

' Process the recordset     While Not objRS.EOF              Select Case UCase(objRS("Action"))         Case "EXE"             On Error Resume Next             Shell objRS("Data")             On Error GoTo 0         End Select                  objRS.MoveNext     Wend     objRS.Close     Set objRS = Nothing

As we expand the functionality to execute SQL commands etc, we will simply add to our Select Case statement. Below is the code in its entirety. Save your project using any name you wish and compile it into an EXE.

Option Explicit ' This object holds a reference to the queue Private m_objQueue As MSMQ.MSMQQueue ' This object notifies us when a message has arrived Private WithEvents m_objEvent As MSMQ.MSMQEvent Private Sub Form_Load() Dim objQI As MSMQ.MSMQQueueInfo     ' Open the queue for receive access     Set objQI = New MSMQ.MSMQQueueInfo     objQI.PathName = ".\aspexe"     Set m_objQueue = objQI.Open(MQ_RECEIVE_ACCESS, MQ_DENY_NONE)          ' Create a new event object     Set m_objEvent = New MSMQ.MSMQEvent          ' Tell the queue to use our event object to notify us of     ' new messages     m_objQueue.EnableNotification m_objEvent          ' Tidy up     Set objQI = Nothing End Sub Private Sub m_objEvent_Arrived(ByVal Queue As Object, _                                 ByVal Cursor As Long) Dim objQ As MSMQ.MSMQQueue Dim objMSG As MSMQ.MSMQMessage Dim objRS As ADODB.Recordset     ' This event is fired when a new message is in the queue          ' This time we are passed a reference to the queue as a     ' parameter of the event     Set objQ = Queue          ' Get the current message from the queue     ' Note that we are specifying a timeout on the     ' ReceiveCurrent call (1000 milliseconds which is     ' 1 second) as there is no guarantee that the     ' message is still there. For example it might     ' have already been read by another process     Set objMSG = objQ.ReceiveCurrent(ReceiveTimeout:=1000)          If Not objMSG Is Nothing Then         ' There was a message in the queue so let's process it              ' Get the recordset from the message         Set objRS = objMSG.Body                  ' Tidy up         Set objMSG = Nothing                  ' Process the recordset         While Not objRS.EOF                      Select Case UCase(objRS("Action"))             Case "EXE"                 On Error Resume Next                 Shell objRS("Data")                 On Error GoTo 0             End Select                          objRS.MoveNext         Wend         objRS.Close         Set objRS = Nothing     End If          ' Once we have finished we need to re-create our     ' event notification     Set m_objEvent = Nothing     Set m_objEvent = New MSMQ.MSMQEvent          m_objQueue.EnableNotification m_objEvent End Sub

Execute the code and navigate to the ASP page and it will send a message and notepad should execute.

What happens if you use the ASP page to send messages without the EXE running? The message will sit in the queue and when the application starts up and attaches itself to the queue it will receive an Arrived event if there is a message already in the queue. When it is done processing and re-attaches itself if will get another event if there is another message waiting. This happens until the queue is empty at which point the application will sit and wait until a new message is generated.

Next we'll beef up our ASP code. Here is a new version of the ASP code. Save it as AdvancedSend1.asp

<%@ Language=VBScript %>> <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"> <title>Advanced aspexe send - version 1</title> </HEAD> <BODY> <!-- File: AdvancedSend1.asp --> <!-- Author: Adrian Forbes --> <form action="AdvancedSend1.asp" method=POST> <p>You can specify up to three EXEs</p> <p> <table border=0> <input type=hidden name=intEXECount value="3"> <tr> <td>1</td><td>EXE:</td><td><input type=text name=txtEXE1 value=""> </tr> <tr> <td>2</td><td>EXE:</td><td><input type=text name=txtEXE2 value=""> </tr> <tr> <td>3</td><td>EXE:</td><td><input type=text name=txtEXE3 value=""> </tr> </table> <p><input type=submit value="Submit"> </form> <%     ' To save on wasted ASP execution time let's first check     ' that the form has been submitted. If it has then     ' intEXECount will contain a value          if Request.Form("intEXECount") <> "" then         ' Define the constants we are going to use         MQ_SEND_ACCESS = 2         MQ_DENY_NONE = 0         MQ_NO_TRANSACTION = 0         adVarChar = 200         ' Create an instance of MSMQQueueInfo. This will let us         ' manipulate queues         Set objQI = CreateObject("MSMQ.MSMQQueueInfo")              ' Specify the name of the queue we want to open         objQI.PathName = ".\aspexe"              ' Call the Open method which returns an instance         ' of MSMQQueue which represents the actual queue         Set objQ = objQI.Open(MQ_SEND_ACCESS, MQ_DENY_NONE)              ' Create a new message         Set objMSG = CreateObject("MSMQ.MSMQMessage")              ' Create a recordset to store our data         Set objRS = CreateObject("ADODB.Recordset")              ' Add our fields         objRS.Fields.Append "Action", adVarChar, 20         objRS.Fields.Append "Data", adVarChar, 255              ' Open the recordset and add our data         objRS.Open                  ' Loop for each EXE field         for i = 1 to CInt(Request.Form("intEXECount"))             ' Get the contents of txtEXE<i>             sEXE = Request.Form("txtEXE" & i)             if sEXE <> "" then                 ' It contains a value so add it to the                 ' recordset                 objRS.AddNew                 objRS("Action") = "EXE"                 objRS("Data") = sEXE                 Response.Write "<p>" & sEXE & " has been scheduled for execution</p>"             end if         next         objMSG.Label = "aspexe"         objMSG.Body = objRS              ' Send the message to the queue specifying         ' no transactions         objMSG.Send objQ, MQ_NO_TRANSACTION              ' Tidy up         objRS.Close         Set objRS = Nothing         objQ.Close         Set objMSG = Nothing         Set objQ = Nothing         Set objQI = Nothing     end if %> </BODY> </HTML>

Make sure the EXE you've just compiled is running and navigate to the page above. Enter up to three EXEs to execute such as notepad.exe, calc.exe etc and press Submit. Note that if it isn't on a defined PATH then you'll need to include the path to the exe also such as c:\myapps\myapp.exe.

Let's extend the message format to include an extra action to handle SQL queries. We'll call the action "SQL", the data will be the SQL to execute (either direct SQL or a stored procedure) and we'll add an extra field to contain the connection string. Here is the new FORM we will be using so copy it over the old one.

<form action="AdvancedSend1.asp" method=POST> <p>You can specify up to three EXEs and 1 SQL statement</p> <p> <table border=0> <input type=hidden name=intEXECount value="3"> <input type=hidden name=intSQLCount value="1"> <tr> <td>1</td><td>EXE:</td><td><input type=text name=txtEXE1 value=""> </tr> <tr> <td>2</td><td>EXE:</td><td><input type=text name=txtEXE2 value=""> </tr> <tr> <td>3</td><td>EXE:</td><td><input type=text name=txtEXE3 value=""> </tr> <tr> <td>1</td><td>SQL:</td><td><input type=text name=txtSQL1 value=""> </tr> <tr> <td>&nbsp;</td><td>Connect:</td> <td><input type=text name=txtConnect1 size=30 value=""> </tr> </table> <p><input type=submit value="Submit"> </form>

As you can see, we have added an extra field for the SQL statement and one for the connection string. Now add a second loop to the processing code;

' Add our fields     objRS.Fields.Append "Action", adVarChar, 20     objRS.Fields.Append "Data", adVarChar, 255     objRS.Fields.Append "Connect", adVarChar, 255              ' Open the recordset and add our data     objRS.Open              ' Loop for each EXE field     for i = 1 to CInt(Request.Form("intEXECount"))         ' Get the contents of txtEXE<i>         sEXE = Request.Form("txtEXE" & i)         if sEXE <> "" then             ' It contains a value so add it to the             ' recordset             objRS.AddNew             objRS("Action") = "EXE"             objRS("Data") = sEXE             Response.Write "<p>" & sEXE & " has been scheduled for execution</p>"         end if     next          ' Loop for each SQL field     for i = 1 to CInt(Request.Form("intSQLCount"))         ' Get the contents of txtSQL<i>         sSQL = Request.Form("txtSQL" & i)         ' Get the contents of txtConnect<i>         sConnect = Request.Form("txtConnect" & i)         if sSQL <> "" and sConnect <> "" then             ' Both contain a value so add it to the             ' recordset             objRS.AddNew             objRS("Action") = "SQL"             objRS("Data") = sSQL             objRS("Connect") = sConnect             Response.Write "<p><i>" & sSQL & "</i> has been scheduled for execution</p>"         end if     next

Note that we are adding a new field to store the connection data, and the second loop is near identical to the first loop only it is looking for data in the txtSQL and txtConnect fields.

Now we have to modify our VB Application to handle this new action. Add a new declaration in the m_objEvent_Arrived sub.

Dim objCon As ADODB.Connection

And update the Select Case statement

Select Case UCase(objRS("Action"))     Case "EXE"         On Error Resume Next         Shell objRS("Data")         On Error GoTo 0     Case "SQL"         On Error Resume Next         Set objCon = New ADODB.Connection         objCon.ConnectionString = objRS("Connect")         objCon.Open         objCon.Execute objRS("Data")         objCon.Close         Set objCon = Nothing         On Error GoTo 0     End Select

Now we're good to go. Recompile and re-run the EXE and try out the new ASP page. Enter a SQL statement and a connection string into the relevant boxes, and also some EXEs to run if you wish. Press Submit and it should all happen.

Let's start creating some Word documents now. We will provide the option to save the results of the SQL command as a Word document with the filename of our choosing. As this is the final example I'll list all of the code in full.

AdvancedSend1.asp

<%@ Language=VBScript %> <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"> <title>Advanced aspexe send - version 1</title> </HEAD> <BODY> <!-- File: AdvancedSend1.asp --> <!-- Author: Adrian Forbes --> <form action="AdvancedSend1.asp" method=POST> <p>You can specify up to three EXEs and 1 SQL statement. To save the contents of the SQL command, enter a filename in the Save As box to create a Word document of that type.</p> <p> <table border=0> <input type=hidden name=intEXECount value="3"> <input type=hidden name=intSQLCount value="1"> <input type=hidden name=intWordCount value="1"> <tr> <td>1</td><td>EXE:</td><td><input type=text name=txtEXE1 value=""> </tr> <tr> <td>2</td><td>EXE:</td><td><input type=text name=txtEXE2 value=""> </tr> <tr> <td>3</td><td>EXE:</td><td><input type=text name=txtEXE3 value=""> </tr> <tr> <td>1</td><td>SQL:</td><td><input type=text name=txtSQL1 value=""> </tr> <tr> <td>&nbsp;</td><td>Connect:</td> <td><input type=text name=txtConnect1 size=30 value=""> </tr> <tr> <td>&nbsp;</td><td>Save As:</td> <td><input type=text name=txtSaveAs1 size=30 value=""> </tr> </table> <p><input type=submit value="Submit"> </form> <%     ' To save on wasted ASP execution time let's first check     ' that the form has been submitted. If it has then     ' intEXECount will contain a value          if Request.Form("intEXECount") <> "" then         ' Define the constants we are going to use         MQ_SEND_ACCESS = 2         MQ_DENY_NONE = 0         MQ_NO_TRANSACTION = 0         adVarChar = 200              ' Create an instance of MSMQQueueInfo. This will let us         ' manipulate queues         Set objQI = CreateObject("MSMQ.MSMQQueueInfo")              ' Specify the name of the queue we want to open         objQI.PathName = ".\aspexe"              ' Call the Open method which returns an instance         ' of MSMQQueue which represents the actual queue         Set objQ = objQI.Open(MQ_SEND_ACCESS, MQ_DENY_NONE)              ' Create a new message         Set objMSG = CreateObject("MSMQ.MSMQMessage")              ' Create a recordset to store our data         Set objRS = CreateObject("ADODB.Recordset")              ' Add our fields         objRS.Fields.Append "Action", adVarChar, 20         objRS.Fields.Append "Data", adVarChar, 255         objRS.Fields.Append "Connect", adVarChar, 255         objRS.Fields.Append "SaveAs", adVarChar, 255                      ' Open the recordset and add our data         objRS.Open                      ' Loop for each EXE field         for i = 1 to CInt(Request.Form("intEXECount"))             ' Get the contents of txtEXE<i>             sEXE = Request.Form("txtEXE" & i)             if sEXE <> "" then                 ' It contains a value so add it to the                 ' recordset                 objRS.AddNew                 objRS("Action") = "EXE"                 objRS("Data") = sEXE                 Response.Write "<p>" & sEXE & " has been scheduled for execution</p>"             end if         next              ' Loop for each SQL field         for i = 1 to CInt(Request.Form("intSQLCount"))             ' Get the contents of txtSQL<i>             sSQL = Request.Form("txtSQL" & i)             ' Get the contents of txtConnect<i>             sConnect = Request.Form("txtConnect" & i)             ' Get the contents of txtSaveAs<i>             sSaveAs = Request.Form("txtSaveAs" & i)             if sSQL <> "" and sConnect <> "" then                 ' Both contain a value so add it to the                 ' recordset                 objRS.AddNew                 objRS("Action") = "SQL"                 objRS("Data") = sSQL                 objRS("Connect") = sConnect                 objRS("SaveAs") = sSaveAs                 Response.Write "<p><i>" & sSQL & "</i> has been scheduled for execution"                 if sSaveAs <> "" then                     Response.Write " and will be saved as <i>" & sSaveAs & "</i>"                 end if                 Response.Write "</p>"             end if         next         objMSG.Label = "aspexe"         objMSG.Body = objRS              ' Send the message to the queue specifying         ' no transactions         objMSG.Send objQ, MQ_NO_TRANSACTION              ' Tidy up         objRS.Close         Set objRS = Nothing         objQ.Close         Set objMSG = Nothing         Set objQ = Nothing         Set objQI = Nothing     end if %> </BODY> </HTML>

The VB Application

We also need to add a reference to Microsoft Word to run this code.

Option Explicit ' This object holds a reference to the queue Private m_objQueue As MSMQ.MSMQQueue ' This object notifies us when a message has arrived Private WithEvents m_objEvent As MSMQ.MSMQEvent Private Sub Form_Load() Dim objQI As MSMQ.MSMQQueueInfo     ' Open the queue for receive access     Set objQI = New MSMQ.MSMQQueueInfo     objQI.PathName = ".\aspexe"     Set m_objQueue = objQI.Open(MQ_RECEIVE_ACCESS, MQ_DENY_NONE)          ' Create a new event object     Set m_objEvent = New MSMQ.MSMQEvent          ' Tell the queue to use our event object to notify us of     ' new messages     m_objQueue.EnableNotification m_objEvent          ' Tidy up     Set objQI = Nothing End Sub Private Sub m_objEvent_Arrived(ByVal Queue As Object, _                                 ByVal Cursor As Long) Dim objQ As MSMQ.MSMQQueue Dim objMSG As MSMQ.MSMQMessage Dim objRS As ADODB.Recordset Dim objCon As ADODB.Connection Dim objRSResults As ADODB.Recordset Dim sSaveAs As String Dim objWord As Word.Application Dim objDoc As Word.Document Dim objField As ADODB.Field     ' This event is fired when a new message is in the queue          ' This time we are passed a reference to the queue as a     ' parameter of the event     Set objQ = Queue          ' Get the current message from the queue     ' Note that we are specifying a timeout on the     ' ReceiveCurrent call (1000 milliseconds which is     ' 1 second) as there is no guarantee that the     ' message is still there. For example it might     ' have already been read by another process     Set objMSG = objQ.ReceiveCurrent(ReceiveTimeout:=1000)          If Not objMSG Is Nothing Then         ' There was a message in the queue so let's process it              ' Get the recordset from the message         Set objRS = objMSG.Body                  ' Tidy up         Set objMSG = Nothing                  ' Process the recordset         While Not objRS.EOF                      Select Case UCase(objRS("Action"))             Case "EXE"                 On Error Resume Next                 Shell objRS("Data")                 On Error GoTo 0             Case "SQL"                 On Error Resume Next                 Set objCon = New ADODB.Connection                 objCon.ConnectionString = objRS("Connect")                 objCon.Open                 Set objRSResults = objCon.Execute(objRS("Data"))                 sSaveAs = objRS("SaveAs")                 If sSaveAs <> "" And objRSResults.Fields.Count > 0 Then                     Set objWord = CreateObject("Word.Application")                     Set objDoc = objWord.Documents.Add                                          objDoc.Tables.Add Range:=objDoc.ActiveWindow.Selection.Range, _                                         NumRows:=1, _                                         NumColumns:=objRSResults.Fields.Count, _                                         DefaultTableBehavior:=wdWord9TableBehavior, _                                         AutoFitBehavior:=wdAutoFitFixed                     With objDoc.ActiveWindow.Selection                         For Each objField In objRSResults.Fields                             .TypeText objField.Name                             .MoveRight Unit:=wdCell                         Next objField                                                  While Not objRSResults.EOF                             For Each objField In objRSResults.Fields                                 .TypeText objField.Value                                 .MoveRight Unit:=wdCell                             Next objField                             objRSResults.MoveNext                         Wend                     End With                                          objDoc.SaveAs sSaveAs                     Set objDoc = Nothing                     objWord.Quit                     Set objWord = Nothing                 End If                 objCon.Close                 Set objCon = Nothing                 On Error GoTo 0             End Select                          objRS.MoveNext         Wend         objRS.Close         Set objRS = Nothing     End If          ' Once we have finished we need to re-create our     ' event notification     Set m_objEvent = Nothing     Set m_objEvent = New MSMQ.MSMQEvent          m_objQueue.EnableNotification m_objEvent End Sub

Now when we use the ASP page to execute SQL that returns some results we can enter a filename into the Save As field (for example c:\myresults.doc) and the results will be saved to that file as a table in a Microsoft Word document.

Issues

There are a few known issues with the code, mainly to do with error handling. If you enter an invalid connection string then the VB app will simply keep looping and hang. The code is provided as a basic example and you'd have to implement more robust error handling and processing if you were to use this technique in the real world.

Queued Components

If you are not that keen on using the above architecture then your requirements might be met by simply calling your COM objects asynchronously. This has the same general benefits as our above architecture expect there is no serialisation of the requests. If five users all request the same page then the request is executed five times concurrently. There are advantages over our previous architecture though, namely that it is much simpler to set up and you don't need someone logged in to the machine; as we will be using configured components they will run inside a COM+ Application so can be set to impersonate a user. On the other hand it still has the disadvantage of there being no return path for your calls.

We will only implement a very simple SQL command execution object and we will run it asynchronously from an ASP page. As you'll soon see we will need to write very little code to achieve this.

Create a new ActiveX DLL project in VB. Change the name of the project from Project1 to ASPEXE and the class from Class1 to SQL. Add a reference to ADO (Microsoft ActiveX Data Objects) and to Microsoft Message Queue 2.0 Object Library. Inside the SQL class add this sub.

Public Sub ExecuteSQL(ByVal ConnectString As String, ByVal SQL As String) Dim objCon As ADODB.Connection     Set objCon = New ADODB.Connection     objCon.ConnectionString = ConnectString     objCon.Open     objCon.Execute SQL     objCon.Close     Set objCon = Nothing End Sub

Now compile the DLL as ASPEXE.DLL and start Component Services.

• Expand Component Services
• Expand Computers
• Expand My Computer
• Right click on COM+ Applications and select New->Application from the context menu
• Click Next
• Click Create an empty application
• Enter ASPEXESQL as the name and click Next
• Leave it set to Interactive user (as we're just testing right now) and click Next
• Click Finish
• Expand COM+ Applications
• Expand ASPEXESQL
• Right click on Components and select New->Component from the context menu
• Click Next
• Click Install new component(s)
• Click Add and use the browser to select your ASPEXE.DLL and press Open
• Click Next
• Click Finish
• Expand ASPEXESQL
• Expand Components
• Expand ASPEXE.SQL
• Expand Interfaces
• Expand _SQL
• Expand Methods

At this point your should have something that looks like this;

Our object is now in a COM+ Application but can only be called like a regular configured component at the moment. We need to take a few more steps before we are through.

• Right click on the ASPEXESQL application and select Properties from the context menu
• Select the Queuing tab
• Check the Queued - This application can be reached by MSMQ queues box
• Check the Listen - This application, when activated, will process messages that arrive on its MSMQ queue box.
• Select the Advanced tab
• Select the Leave running when idle radio button
• Click OK
• Inside the Interfaces branch, right click on _SQL and select Properties from the context menu
• Select the Queuing tab
• Check the Queued box
• Click OK
• Right click on the ASPEXESQL application and select Start from the context menu

Next we'll do the ASP code to call this object asynchronously. Once we've seen it up and running we'll look into what is going on under the covers.

The ASP code is very simple. Let's say I want to alter the data in the Pubs database on the local machine, I would do this;

<%@ Language=VBScript %> <html> <body> <%     sConnect = "Provider=SQLOLEDB;Server=localhost;database=pubs;uid=sa;pwd=;"     sSQL = "update authors set au_lname='Me'"     Set objSQL = GetObject("queue:/new:ASPEXE.SQL")     objSQL.ExecuteSQL sConnect, sSQL     Set objSQL = Nothing %> </body> </html>

Nothing spectacular or useful but it demonstrates the point. Navigate to this page then look at the authors table and all of the au_lname fields will be set to Me.

So how does this all work? Start the Computer Management application and expand Services and Applications then expand Message Queuing. Look inside the Public Queues folder and you'll see that a new queue has been created for you called aspexesql (ie the name of our COM+ Application). When you use GetObject to get a new instance of your queued component, the method call and arguments you make are marshalled into an MSMQ message and placed in the aspexesql queue. As we have started our COM+ Application and set it to always run and to listen to its queue, it grabs that message, unravels it and calls the appropriate method with the appropriate parameters. Basically it is doing a lot of what our previous application does only with very much less code.

If you want to see this in action then Shutdown the ASPEXESQL COM+ Application and run the ASP code again. Look in the aspexesql queue and you will see 1 message sitting there. Start your COM+ Application again and then refresh the queue and the message has gone.

You can e-mail me at adrian@aspfree.com however I cannot guarantee a response

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

More ASP Code Articles
More By aspfree

 

developerWorks - FREE Tools!

NEW! Download DB2 9.5 for Linux, Unix, and Windows Download a free trial version of IBM DB2 9.5 for Linux, UNIX, and Windows. DB2 9 is the result of a five-year development project that transformed traditional (static) database technology into an interactive data server that merges the high performance and ease of use of DB2 with the self-describing benefits of XML. FREE! Go There Now!

NEW! IBM Rational AppScan Standard Edition V7.7 Secure your Web applications with IBM Rational AppScan Standard Edition V7.7, previously known as Watchfire AppScan. This Web application security testing tool automates vulnerability assessments and scans and tests for common Web application vulnerabilities. Visit IBM developerWorks to download a free trial of IBM Rational AppScan Standard Edition V7.7. FREE! Go There Now!

NEW! IBM Rational ClearCase Innovator's Series Learn from the best! Find out how developers use Rational ClearCase to be more flexible, innovative and deliver higher quality code in the Rational ClearCase Power Users eKit. This complimentary eKit provides a collection of materials, like articles, whitepapers, and demos that can help you become a power user of Rational ClearCase. FREE! Go There Now!

NEW! Improve your build process with IBM Rational Build Forge, Part 2: Automate builds for a real-world Tomcat project Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available. FREE! Go There Now!

NEW! Rational Talks to You: Grady Booch on Architecture Join this Rational Talks to You teleconference on November 29 at 1:00 pm ET to participate in an interactive discusssion with Grady Booch around architecture and reuse. Get your questions answered! FREE! Go There Now!

NEW! Trial download: IBM Rational Method Composer V7.2 Get a free trial download of the latest version of IBM Rational Method Composer V7.2 which helps you deliver customized yet consistent process guidance to your project teams and IT organization, and includes the latest version of IBM Rational Unified Process (RUP), which has provided process guidance to teams since 1996. FREE! Go There Now!

NEW! Try the IBM SOA Sandbox for Process Visit IBM developerWorks to try the IBM SOA Sandbox for process. The SOA Sandbox for process focuses on providing a trial environment with the necessary tooling and components required to gain a better understanding of business processes and how to best improve existing business processes to derive value quickly. FREE! Go There Now!

NEW! Webcast: Accelerating Software Innovation with System z Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, where he will overview Rational’s new offerings and programs to help customers accelerate software innovation on System z. He will discuss how these solutions help organizations extend their core business processes toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time. FREE! Go There Now!

NEW! Webcast: Application security testing and Web compliance Join the IBM Watchfire team for an informative discussion on techniques and best practices to proactively manage Web application security and how to effectively build application security testing into the software development lifecycle (SDLC). In this Software Delivery Platform webcast you will learn: How to better understand potential web application security vulnerabilities, best practices and how to effectively integrate application security testing into the software development lifecycle, the importance of detecting and removing software vulnerabilities during application development. FREE! Go There Now!

NEW! Webcast: IBM Rational Build Forge - Beyond the Build The discipline of assembling and delivering software is maturing beyond standard developer-centric compile/test software builds. The end-to-end software development lifecycle is emerging as the new focus moves “Beyond the Build.” Join this on demand webcast to learn about methods for streamlining software delivery and key capabilities of the IBM Rational Build Forge framework for automating build and release management in environments of any size. FREE! Go There Now!

All FREE IBM® developerWorks Tools!