apr 10 blog

Powershell and MSMQ – Query SQL and push data directly into MSMQ in XML format

With the script below it’s possible to push data directly from an SQL Query into a MSMQ:


[Reflection.Assembly]::LoadWithPartialName("System.Messaging");
$queueName = '.Private$test'; ##Define you MSMQ here!
$msgLabel = 'Message label'; ##Define your label here!
#SQL Statement
	$SQLServer = "ANJEP-SQL" #use ServerInstance for named SQL instances!
	$SQLDBName = "Fakedemodata"
	$SqlQuery =

		"
		SELECT TOP 100
			(SELECT TOP 1
				*
			FROM
				FakeNameGenerator AS SUB
			WHERE
				SUB.Number = MAIN.Number
			FOR XML RAW
				('Contact'), root('Contacts'), ELEMENTS XSINIL
				--, XMLSCHEMA //Enable to get the XSD schaema
			) AS XML
			FROM
				FakeNameGenerator AS MAIN
		ORDER BY
		MAIN.Number

		"

	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	$SqlCmd.CommandText = $SqlQuery
	$SqlCmd.Connection = $SqlConnection

	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
	$SqlAdapter.SelectCommand = $SqlCmd

	$DataSet = New-Object System.Data.DataSet
	$SqlAdapter.Fill($DataSet)

	$SqlConnection.Close()

#Send the dataset to MSMQ
	$queue = new-object System.Messaging.MessageQueue $queueName;
	$utf8 = new-object System.Text.UTF8Encoding;
	$tran = new-object System.Messaging.MessageQueueTransaction;
	$msgStream = new-object System.IO.MemoryStream;
	$msg = new-object System.Messaging.Message;
	$i = 1

foreach ($row in $DataSet.Tables[0].Rows)
	{
		write-host $i
		$tran.Begin();
		$msgContent = $row[0].ToString();
		$msgBytes = $utf8.GetBytes($msgContent);
		$msgStream.Write($msgBytes, 0, $msgBytes.Length);

		$msg.BodyStream = $msgStream;
		$msg.Label = $msgLabel;
		$queue.Send($msg, $tran);
		$queue.Send($msg);

		$tran.Commit();

		$i = $i + 1
	}
Share on FacebookTweet about this on TwitterShare on LinkedInShare on Google+