Biztalk suspended messages in database

Question

Hi,

I was wondering if some1 knows where I can see the data of a suspended message in the biztalk database.

I need this because about 900 messages have been suspended because of a validation and I need to edit all of them, resuming isn't possible.

I know that info of suspended messages are shown in "BizTalkMsgBoxDb" in the table "InstancesSuspended" and that the different parts of each message are shown in the table "MessageParts". However I can't find the table where the actual data is stored.

Does anyone have any idea where this can be done?

Thx

Answer 1

This is more than likely not supported by Microsoft. Don't risk screwing up your system. If you have a need to have a edit and resubmit, it needs to be built into the orchestration. Otherwise, your best bet is to use WMI to write a script to:

1. pull out all of the suspended messages
2. terminate them
3. edit them
4. resubmit them

Answer 2

I found a way to do this, there's no screwing up my system when I just want to read them.

How I did it is using the method "CompressionStreams" using Microsoft.Biztalk.Pipeline.dll.

The method to do this:

    public static Stream getMsgStrm(Stream stream)
    {
        Assembly pipelineAssembly = Assembly.LoadFrom(string.Concat(@"<path to dll>", @"\Microsoft.BizTalk.Pipeline.dll"));
        Type compressionStreamsType = pipelineAssembly.GetType("Microsoft.BizTalk.Message.Interop.CompressionStreams", true);
        return (Stream)compressionStreamsType.InvokeMember("Decompress", BindingFlags.Public | BindingFlags.InvokeMethod | BindingFlags.Static, null, null, new object[] { (object)stream });
    }

Then I connect with my database, fill in a dataset and stream out the data to string, code:

        String SelectCmdString = "select * from dbo.Parts";
        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(SelectCmdString, "<your connectionstring">);
        DataSet myDataSet = new DataSet();
        mySqlDataAdapter.Fill(myDataSet, "BodyParts");

        foreach (DataRow row in myDataSet.Tables["BodyParts"].Rows)
        {
            if (row["imgPart"].GetType() != typeof(DBNull))
            {
                SqlBinary binData = new SqlBinary((byte[])row["imgPart"]);
                MemoryStream stm = new MemoryStream(binData.Value);
                Stream aStream = getMsgStrm(stm);
                StreamReader aReader = new StreamReader(aStream);

                string aMessage = aReader.ReadToEnd();

                //filter msg
                //write msg
            }
        }

I then write each string to an appropriate "txt" or "xml" depending on what u want, you can also filter out certain messages with regular expression, etc.

Hope this helps anyone, it sure as hell helped me.

Greetings

Answer 3

you can find it through the HAT tool you just need to specify the schema ,port and the exact date with the exact time and it will show you the messages right click on the desired one and save .