HomeASP Code Executing long-running tasks from ASP by A...
Executing long-running tasks from ASP by Adrian Forbes
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")
' 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")
' 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.
' 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> </td><td>Connect:</td> <td><input type=text name=txtConnect1 size=30 value=""> </tr> <tr> <td> </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")
' 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.
ASP Code 
ASP.NET Export to Excel and Word using VB.NET and C#
