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!

Check out the new Jazz space on developerWorks <a href="http://zeus.developershed.com/shonuff.php?blackbird=3853&zoneid=442&source=&dest=http%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fspaces%2Fjazz%3FS_TACT%3D105AGY31%26S_CMP%3DDEVSHED&ismap="><img src="http://images.devshed.com/corp/img/news/jazz01.gif" alt="developerWorks Jazz space" align="left"></a>You've heard the buzz about Jazz... want to know more about it from a developer's perspective? Check out the Jazz space on developerWorks. This space is an up-to-date resource for developers, including technical information about Jazz and products built on Jazz, like Rational Team Concert Express. The Jazz space includes content from a wide variety of sources, including links, feeds, and comments from experts. FREE! Go There Now!

NEW! Calling all CC Power Users – and those that would like to be! Join this Rational Talks to You teleconference, featuring Paul Boustany and Mark Krasovich, to speak to the experts about becoming a Rational ClearCase power user. Get a chance to ask your questions and learn tips and tricks for using Rational ClearCase in Agile development FREE! Go There Now!

NEW! Achieving True Agility -- How process can change the behavior of your tools Achieving true agility is a never-ending effort. We will showcase how you can become agile incrementally, a few practices at the time.Which practices should any agile team strive to adopt? What additional practices should you consider based on your needs to scale? Adopting practices are however made much easier with the right tool support. What about if your tools adapt to your practices? We will take a look at how the Jazz technology can be leveraged to make your process change the behavior of your tools. FREE! Go There Now!

NEW! Applying lean thinking to the governance of software development Effective governance for lean development isn’t about command and control. Instead, the focus is on enabling the right behaviors and practices through collaborative and supportive techniques. Hear from Scott Ambler on how it is far more effective to motivate people to do the right thing than it is to force them to do so. Learn how to form a lightweight, collaboration-based framework that reflects the realities of modern IT organizations. FREE! Go There Now!

NEW! Download IBM Rational Developer for System z Download a free trial version of IBM Rational Developer for System z, software that can help you deliver core development capabilities; the power of Java Platform, Enterprise Edition (Java EE); and rapid application development support to diverse enterprise application development teams. With comprehensive development tools to help create, deploy and maintain traditional enterprise and composite applications, Rational Developer for System z enables developers with different technical backgrounds to easily participate in important technology projects. 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! Project and Portfolio Management Executive Resource Kit Portfolio Management is about effectively managing portfolio value by aligning portfolio investments with business goals. This complimentary e-kit provides a collection of materials that can help you understand how IBM Rational enables and automates best practices for improved governance and clear visibility into portfolio and project performance across the entire IT project lifecycle. FREE! Go There Now!

NEW! Successful Change and Release Management for .NET Join this webcast to discover the key requirements for successful change and release management. Learn how to extend your .NET environment to improve productivity and collaboration, and address core problems afflicting team development. In this webcast, we’ll review typical challenges faced by customers and how to resolve them with the IBM Rational Change and Release Management solution, including Rational ClearCase, Rational ClearQuest and Rational Build Forge. Replay is available for 9 months. FREE! Go There Now!

NEW! Try the IBM SOA Sandbox for Connectivity Visit IBM developerWorks to try the IBM SOA Sandbox for connectivity. The SOA Sandbox for connectivity provides a trial environment with the tooling and components to help you explore how to effectively connect your infrastructure and integrate all of the people, processes and information in your company. Use the hosted sandbox to explore SOA techniques that streamline connecting existing IT assets together, as well as learn how to connect them to new business logic. FREE! Go There Now!

NEW! Webcast: Calling All Testers! Find Application Vulnerabilities Early in the Development Process Where they are Easier to Fix and Less Risky to your Business In this webcast, IBM Rational will discuss the importance of Web application security and will share techniques and best practices to introduce application security testing into current QA processes including: understanding common security vulnerabilities and techniques to integrate security testing with defect tracking and remediation systems in an effort to safeguard sensitive online information. FREE! Go There Now!

All FREE IBM® developerWorks Tools!