Blog

How do you get records from a SQL Server table into a MSMQ queue as separate XML messages?

A commonly suggested way to communicate with MSMQ from within SQL Server is to create a CLR assembly. See for instance this article that has a great coverage of how to use CLR in this scenario. Note however that you have to import the System.Messaging.dll as unsafe for this to work. And you have to enable CLR obviously.

If this is not an option, another way to do it is to use PowerShell. Which is what I will cover in this demonstration.

Intro

In this demonstration I will use a fictive database containing invoices with invoice rows. There will be a column showing if the invoice has been posted to the MSMQ queue or not. There will also be a SQL Agent job looking for unposted invoices, and if there are any, they will be sent to the MSMQ queue. The SQL Agent job will use PowerShell for this.

MSMQ

The first thing to do is to create a new MSMQ queue.

[green_box]If you need to know how to install MSMQ and create queues on your computer, check this tutorial.[/green_box]

Give the queue some meaningful name (I’ll call it tomastest) and make sure to uncheck the “Transactional” checkbox. (You’d probably want to check this in a real world scenario however. But uncheck it to keep the demonstration simple.)

Database

The next step is to create the database we need for the demonstration. Use the following script to create the database.

We also need to add the column that keeps track of when an invoice has been sent to the MSMQ queue or not:

ALTER TABLE dbo.Invoices
ADD MessageSent DATETIME2 NULL

Next, we need to fill the database with some sample data:

INSERT dbo.Invoices (InvoiceNumber, InvoiceDate, XMLPosition)
VALUES (991001, '2017-06-01', 1), (991002, '2017-07-01', 2)

INSERT dbo.InvoiceRows (InvoiceId, Product, Amount)
VALUES (1, 'PROD1234', 1000), (1, 'PROD5678', 100), (2, 'PROD1234', 2000)

Now we have a very simple database that can be used in the demonstration. There is a table for invoices, and a “child” table called that shows one or many invoice rows for each invoice:

Note that NULL in the column MessageSent means the invoice has not been sent yet.

We will use a stored procedure to get unsent invoices. The procedure will return the records as XML documents (one per invoice) and then mark them as sent. Run the following SQL to create the procedure:

CREATE PROCEDURE dbo.GetInvoiceMessages AS

SET NOCOUNT ON

DECLARE @InvoicesToSend TABLE (InvoiceId INT)

INSERT @InvoicesToSend
SELECT InvoiceId FROM dbo.Invoices WHERE MessageSent IS NULL

SELECT ITS.InvoiceId,
	CAST((
		SELECT I.InvoiceId, I.InvoiceNumber, I.InvoiceDate,
			(
				SELECT IR.Product, IR.Amount
				FROM dbo.InvoiceRows IR
				WHERE IR.InvoiceId = I.InvoiceId
				FOR XML PATH ('InvoiceRow'), TYPE, ROOT ('InvoiceRows')
			)
		FROM dbo.Invoices I
		WHERE I.InvoiceId = ITS.InvoiceId
		FOR XML PATH ('Invoice')
	) AS XML) AS RowXML
FROM @InvoicesToSend ITS

UPDATE I
SET MessageSent = GETDATE()
FROM dbo.Invoices I
JOIN @InvoicesToSend ITS
	ON I.InvoiceId = ITS.InvoiceId
GO

PowerShell

The following PowerShell script will connect to both the database and to the queue. Make sure to edit the parameters in the top of the script to match your environment. MSI/SS2016 is the SQL Server instance name, and .\Private$\tomastest is the location of the queue. The . means I’m running on my local computer and that will have to be replaced with IP address if the queue is located somewhere else on the network.

[Reflection.Assembly]::LoadWithPartialName("System.Messaging") | Out-Null
Import-Module SQLPS

$ServerName = "MSI\SS2016"
$queueName = ".\Private$\tomastest"

Set-Location SQLSERVER:\SQL\$ServerName

$queue = new-object System.Messaging.MessageQueue $queueName
$utf8 = new-object System.Text.UTF8Encoding

$Invoices = Invoke-Sqlcmd -Query "EXEC XMLInvoices.dbo.GetInvoiceMessages" | select -expand RowXML

$msg = new-object System.Messaging.Message

foreach($Invoice in $Invoices)
{
    $msgStream = new-object System.IO.MemoryStream
    $msgBytes = $utf8.GetBytes($Invoice)
    $msgStream.Write($msgBytes, 0, $msgBytes.Length)
    
    $msg.BodyStream = $msgStream
    $msg.Label = "Invoice from the database XMLInvoices"
    $queue.Send($msg)

    Get-Date -Format G
    $Invoice + "
    "
}

If you run this and everything is working as expected you will now see two new messages in your queue after a refresh:

SQL Agent

The last part in this solution is to automate the execution of the PowerShell script which can be done by using SQL Agent. Depending on the requirements the job can then be run at some scheduled interval, for instance every 10:th second. In SQL Agent, we can use notification to get a message when the job fails. Also, we can use the built in logging feature to get a log of each execution, which can be very useful if something goes wrong. When the job is executed, a log file will be created in the SQL Server error log folder path. The name of the file will be “SQLtoMSMQ_YYYYMMDD_HHMMSS.txt”.

[red_box]
Note that the job runs every 10:th second, so there will be a lot of log files eventually. Also, make sure SQL Agent job history is truncated regularly.
[/red_box]

Run the following T-SQL to create the job:

USE msdb
GO

DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job
    @job_name = 'Demo from SQL to MSMQ using PowerShell',
    @enabled = 1,
    @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep
	@job_id = @jobId,
	@step_name = 'Run PS script',
	@step_id = 1,
	@subsystem = N'PowerShell', 
	@command = N'[Reflection.Assembly]::LoadWithPartialName("System.Messaging") | Out-Null
Import-Module SQLPS

$ServerName = "MSI\SS2016"
$queueName = ".\Private$\tomastest"

Set-Location SQLSERVER:\SQL\$ServerName

$queue = new-object System.Messaging.MessageQueue $queueName
$utf8 = new-object System.Text.UTF8Encoding

$Invoices = Invoke-Sqlcmd -Query "EXEC XMLInvoices.dbo.GetInvoiceMessages" | select -expand RowXML

$msg = new-object System.Messaging.Message

foreach($Invoice in $Invoices)
{
    $msgStream = new-object System.IO.MemoryStream
    $msgBytes = $utf8.GetBytes($Invoice)
    $msgStream.Write($msgBytes, 0, $msgBytes.Length)
    
    $msg.BodyStream = $msgStream
    $msg.Label = "Invoice from the database XMLInvoices"
    $queue.Send($msg)

    Get-Date -Format G
    $Invoice + "
    "
}
', 
	@database_name = N'tempdb',
	@output_file_name = N'$(ESCAPE_SQUOTE(SQLLOGDIR))\SQLtoMSMQ_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt'

EXEC msdb.dbo.sp_add_jobschedule
	@job_id = @jobId,
	@name = N'Schedule_SQL_to_MSMQ',
	@enabled=1, 
	@freq_type=4, 
	@freq_interval=1, 
	@freq_subday_type=2, 
	@freq_subday_interval=10, 
	@freq_relative_interval=0, 
	@freq_recurrence_factor=0

EXEC msdb.dbo.sp_add_jobserver
    @job_id = @jobId,
    @server_name = N'(local)'

Notes

The PowerShell version used above is 4. The SQL Server version is 2016.

Note that all the code in this post is just for demonstration purposes. For a production environment, the following issues must be addressed:

Error handling

Both the PowerShell script and the procedure should use try/catch to deal with errors. In the scripted SQL Agent job above there are no notifications which also should be added.

Transactions

There is no transaction handling in this demonstration. Ideally, the transaction should be started in the PowerShell script and span the call to the procedure as well as the call to the queue. In this demonstration we unchecked the Transactional checkbox, but I would not do that in a real world scenario. If you send a message without using transaction to a transactional queue you won’t get an error message, instead the message will never appear in the queue simply because it is never committed.

Isolation level

Right now, there is a risk that we might read invoices that are in the middle of creation by some other process, so that they might be without invoice rows for instance. To deal with such scenarios, an appropriate isolation level should be used. In this example READ COMMITED might work.