I recently did something for a project I was on that I thought was pretty cool. The project took a different architectural path, so we didn’t end up using it, but I’m posting it here as I think someone out there will find it very useful….
I got my start on this from the great info Lee Graber posted on the BizTalk Core Engine blog (http://blogs.msdn.com/biztalk_core_engine/archive/2004/09/20/231974.aspx)
This code sample shows how to check the BizTalk MessageBox for suspended messages that were destined for a specific orchestration.
Is this useful? Well… maybe not. Here’s why: in order for a message that was destined for a specific orchestration to be suspended in the MessageBox, it means that a message agent got the message, looked up the subscription, and tried to send it to the orchestration. So, in order for the subscription to exist the orchestration must have been enlisted, and if it was enlisted, then the send should never fail.
So, while perhaps not directly useful, this sample does show some cool techniques that you could re-use elsewhere. Also, with some minor mods, you can check other tables and do things like have one orchestration be aware of pending messages destined for another orchestration.
Caveat: I did a lot of hacking and exploring while doing this. Use of this is purely your own decision. This may or may not be the best way to achieve the stated goal. As always, you should exercise extreme caution when doing *anything* directly affecting the MessageBox. Having said all that, I had fun hacking and reverse engineering, hopefully this helps someone.
How it works… The stored proc will iterate through the Q_Suspended tables for each of the host instances, looking for a uidInstanceID that matches the orchestration ID you pass in as a parameter.
Every orchestration has a unique identifier associated with it that gets created when the orchestration is created. You need to get this instance identifier. In order to do that, put this inside an Expression shape:
OrchestrationInstanceID = System.Convert.ToString(Microsoft.XLANGs.Core.Service.RootService.InstanceId);
Next, you’ll want a helper class that you can call that will check for messages. Here’s a sample (don’t worry about the UDL in the connection string, I’ll talk about that in a future post J).
[Serializable()]
public class MessageBoxHelper
{
// Connection string info is contained in UDL file, which must be present...
protected String connectionString = "Provider=SQLOLEDB;File Name =\\\\" + System.Environment.MachineName.ToString() + "\\c$\\BTS.udl";
public MessageBoxHelper()
{
//
// TODO: Add constructor logic here
//
}
///
/// Calls a stored proc that checks the BTS MessageBox to determine if there are any messages either suspended
/// or waiting to be delivered to a given orchestratrion instance. Usage is to determine if a convoy is done or not.
///
///
///
public bool CheckForSuspendedMessages(string OrchServiceID)
{
bool retval = false;
try
{
OleDbConnection con = new OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand("BL_BTS_CheckForSuspendedMessages", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@OrchServiceID", OrchServiceID);
con.Open();
object ret = cmd.ExecuteScalar();
retval = !(Convert.ToInt32(ret)==0);
}
catch (Exception ex)
{
Logger errorLogger = new Neudesic.Utilities.Logger();
errorLogger.WriteEntry("Neudesic.Utilities.MessageBoxHelper Error", "Error accessing database: " + ex.Message, 5000, System.Diagnostics.EventLogEntryType.Error);
throw new ApplicationException("Critical error accessing BizTalk MsgBox database from Neudesic.Utilities.MessageBoxHelper: " + ex.Message , ex);
}
return retval;
}
}
In my case, I used the helper class as the decision expression in a decision shape as shown below (the orchestration had a variable declared that was of the MessageBoxHelper type):
MessageBoxHelper.CheckForSuspendedMessages(OrchestrationInstanceID)
Then, and this is the last piece, we have the stored proc itself:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[BL_BTS_CheckForSuspendedMessages]
(
@OrchServiceID varchar(36)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW
CREATE TABLE #Temp (Entries int)
DECLARE @nvcAppName sysname
DECLARE MyCursor CURSOR FAST_FORWARD FOR
SELECT nvcApplicationName FROM [BizTalkMsgboxDb]..[Applications] WITH (NOLOCK)
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @nvcAppName
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO #Temp
exec (' SELECT COUNT(*) FROM ' + @nvcAppName + 'Q_Suspended WHERE uidInstanceID = CAST(''' + @OrchServiceID +''' AS uniqueidentifier)')
FETCH NEXT FROM MyCursor INTO @nvcAppName
END
SELECT SUM(Entries) AS TotalReferences FROM #Temp
CLOSE MyCursor
DEALLOCATE MyCursor
DROP TABLE #Temp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO