SQL Server 2005 Service Broker Plays Well with Older Relatives
ITransactedMessageMediatorSQL Server 2000 and MSMQITransactedMessageMediator requires a Translate and an Init function. Translate accepts an SSBMessage class. TransactedMessageMediator_SQL2000 implements the ITransactedMessageMediator interface and writes SSBMessage to a table on the external SQL Server 2000 database. The following is an excerpt from the Translate function:
SqlParameter param; SqlCommand cmd; string query; XmlDocument xml = new XmlDocument(); SqlTransaction tran; tran = _conn.BeginTransaction(); cmd = _conn.CreateCommand(); cmd.Transaction = tran; query = "EXEC InsertTestSSB @body "; cmd.CommandText = query; param = cmd.Parameters.Add("@body", SqlDbType.Text); xml.Load(msg.Body); param.Value = xml.OuterXml; cmd.ExecuteNonQuery(); tran.Commit();
Ideally, the SqlConnection would enlist the existing transaction rather than implementing its own transaction. Unfortunately, for reasons discussed previously, enlisting was not possible. Therefore, if you build on the example, consider the impact of duplicate messages in your solution.
Like SQL Server 2000, MSMQ can perform transactions. TransactedMessageMediator_MSMQ implements ITransactedMessageMediator for MSMQ. The following is the Translate function implementation:
XmlDocument xml = new XmlDocument(); xml.Load(msg.Body); _msgQueue.Send(xml.OuterXml);
New Asynchronous Messaging TechnologySee, you can leverage SQL Server 2005 Service Broker with existing technologies such as SQL Server 2000 and MSMQ. BizTalk and SSB external activation are certainly options to consider, but an easier solution is using some of the new features in .NET 2.0 to build a simple polling solution. Whichever solution you choose, pay close attention to distributed transactions and duplicate messages.
Download the Code
To download the accompanying source code for the example, click here.
About the Author
Jeffrey Juday is a software developer with Crowe Chizek in South Bend, Indiana. He has been developing software with Microsoft tools for more than 12 years in a variety of industries. Jeff currently builds solutions using BizTalk 2004, ASP.NET, SharePoint, and SQL Server 2000. You can reach Jeff at firstname.lastname@example.org.
Page 2 of 3