Download Source Code
If you've ever found yourself scratching your head, wondering how to queue a message from MS SQL Server, then you might find yourself writing your own plumbing. (Microsoft will most certainly be dealing with this boggle in future version). And I don 'T Know How Your Boss Operates, But I Usually Need It Done Like - Yesterday.
Using some of my, "Nothing is impossible with Visual Studio .NET" theory, we will create a console application to queue a message in Microsoft Message Queuing (MSMQ). Using the magic of an extended stored procedure, we can call our console application from an MS SQL trigger. We create our table in Enterprise Manager and then use the trigger to call our console application. You could also call a COM object for this example, but I choose a console application because calling in SQL syntax is straight forward.
Message queues are very cool because you can queue a message to a queue server, and it will remain there waiting to be processed. There are systems in place to make sure that message gets to where it belongs. If for some reason the message doesn ' t arrive, it will be sent again until it arrives at the intended destination So for valuable mail-like checks from the lotto commission -. we want to ensure those messages get to where they need to go So, what we want to do. IS build a process to check the queue and then process the message in it.
Message queues are very underutilized. But Visual Studio .NET makes the process of creating and using this technology much simpler. You can configure queues on your machine by right clicking on My Computer and then selecting Manage. Under the Services and Applications node you can find the "Message Queuing Node" Note:. if you do not see a "Messaging Queuing Node" then you will need to install it from your windows CD.To tell you the truth, it actually makes me feel warm inside when I fire up VisualStudio .NET. It is also moments like these that I know why Microsoft added the Win32 Console Application project type. It's a command shell application and looks like an MS-DOS window. (We'll never be without it). and since we HE CAUE Server., We can use the.
Installing a queue server is pretty straightforward, but I've found it can be difficult to write to Public queues from a Windows 2000 client if you are on an Active Directory (AD) network-that is, unless you have the AD installed on the domain controller. (The look on the network guy's face when you talk about touching the AD is priceless). We're fortunate that Windows XP and Microsoft Windows Server 2003 present no issues with writing to queues on other servers. If you are using Windows 2000, you can use private queues pretty easy public queues with a 2000 client, but there is become more footwork involved.
On the SQL side, we've set-up a trigger within our table of e-mail addresses. Upon updating a record, it will get the ID of the changed record and then call the console application by using xp_cmdshell. Xp_cmdshell is an extended stored procedure. This is a special DLL written in Microsoft Visual C .NET. It is comes standard with MSSQL Server. in SP3 for MSSQL Server you need to give the user calling this stored procedure rights to call console applications. for the purpose of this Application, I Made the SQL User A Sysadmin. this will call our application and pass the Updated id of the record that we want to work with.create trigger go on [dbo]. [email]
For update
AS
Declare @cmd sysname
Declare @ID INT
SELECT @ID = id from inserted
Set @cmd = 'c: /temp/queuethunder.exe' cast (@ID as varchar (10))
Exec master..xp_cmdshell @cmd
When creating a console application in Visual Studio, the Sub Main routine is the start-up code for the application. It assesses the parameters passed to it in the args parameter. (These are actual command line parameters passed to the application). This code The id.
Sub main (byval args () AS String)
'DIM SQUEUEPATH AS STRING = "Your_Machine_name / Private $ / Happy"
'DIM SQUEUEPATH AS STRING = "Your_Machine_name / Happy"
DIM SQUEUEPATH AS STRING = "CalvinLaPxp4 / Happy"
Dim myenumerator as system.Messaging.MessageEnumerator
Dim myQueue as new system.Messaging.MessageQueue (SqueuePath)
Dim ohappyqueue as system.Messaging.MessageQueue = getQueue (SqueuePath)
DIM OOBJECT AS STRING
Try
OOBJECT = args (0)
'Send id to queue
OhappyQueue.send (OOBJECT)
OhappyQueue.close ()
Catch exception
Console.writeline ("System Error" EX.MESSAGE)
Console.writeline ("System Error" EX.Source)
Console.writeline ("Sub-System Error" EX.INNNEREXCEPTION.MESSAGE)
END TRY
OhappyQueue = Nothing
End Sub
On the server side, we pick things back up in the queue to validate the e-mail addresses. First we'll need to create another console application and call it ProcessQueue. The Sub Main code will open the queue, cycle through each message, and then process it. Because the messages are queued, our SQL process runs asynchronously with this process. This means that SQL will be finished processing when the validate e-mail function is concurrently being processed in a different process.
DIM SQUEUEPATH AS STRING = "CalvinLaPxp4 / Happy"
Dim myenumerator as system.Messaging.MessageEnumerator
Dim myQueue as new system.Messaging.MessageQueue (SqueuePath)
DIM SRESULT AS STRING
Dim oprojectthundercom as new com.projectthunder.www.validateEmail
Dim Odata As New DataAccessLayer.DataAccess.sql Server ("Server",
"Database", "Username", "Password")
'Get Enumerator So We Can Go THROUGH The Queue
Myenumerator = myQueue.getMessageEnumerator ()
'we are going through each message so we can so we can get the value
From The Message Then Want To Remove It So it isn't Processed Again
While myenumerator.movenext ()
'Resulting id from e-mail record
SResult = StringFromMMessage (MyEnumerator.current)
Dim Semail As String
'Go Get Email Address from Record
Dim Ods as dataset = odata.runsqldataset ("Select * from emailwhere id = '" SRESULT "")
IF ods.tables (0) .rows.count> 0 THEN
Semail = ODS.TABLES (0). ROWS (0) .Item ("email")
Else
Console.writeline ("Record Not Found" SResult)
END IF
'Going Out To Get The Result of Testing The E-mail Address
DIM statusResult as string =
Oprojectthundercom.ChatmailServer (Semail)
'Update The Record with result
Odata.Runsqldataset ("Update Email Set Emailstatus = '"
StatusResult "'where id ='" SRESULT "'")
'Remove The Message from the Queue So We don't process it again
Myenumerator.removecurrent ()
ODS = Nothing
End while
MyQueue.close ()
MyQueue = Nothing
OProjectthundercom = Nothing
Odata = Nothing
Catch exception
Console.writeline ("System Error Message" EX.MESSAGE)
Console.writeline ("System Error Source" EX.Source)
Console.writeline ("System Error Trace" EX.STACKTRACE
Console.writeline ("Sub-System Error" EX.INNNEREXCEPTION.MESSAGE)
END TRY
