How to retrieve Message Body from BizTalk Database

[BASKAR BV]

Hi All,

From which table to read the actual xml data sent to the messagebox
database? (e.g)
<root>
<data></data>
</root>
i want to know where the above data is stored in the sql table?

Appreciate your help!

--
B.V. Baskar
BizTalk Developer

[rene.r...@gmail.com]

Hi Baskar
I faced the same question a while ago when I searched directly in the
database, I believe there is a field where the data is stored
(actually two) that are called imgBody and imgContext, but it is not
possible to query them and retrieve the actual xml document. In order
to retrieve the document, what I have done is
a. First option is to code an interface using the biztalk dlls
provided, but it is kind of tricky because you will have to know the
exact message identification. For example, I code a console
application in c# but to retrieve the message it demands the messageid
as a parameter.
b. WMI, sort of the same, but a lot more "user friendly".

I hope that using the biztalk interfaces could help you in your
approach.

[BASKAR BV]

Thanks for the reply. can you send me the table name where the imgbody and
imgcontext exists?

--
B.V. Baskar
BizTalk Developer

[Randal van Splunteren]

Hello BASKAR,

Just curious, but why do you want to read the XML directly from the messagebox?

Randal van Splunteren

> Thanks for the reply. can you send me the table name where the imgbody
> and imgcontext exists?
>

[BASKAR BV]

suppose i have a message which has "cost" as one of the field. i would like
to query all the transaction that happened in a day where cost is some number
or so.. or i would like to query how much messages comes in a day from a
particular vendor. I hope this could be possible thru BAM but wondering how
BAM gets such results?? Correct me if BAM cannot do such request.

--
B.V. Baskar
BizTalk Developer

[rene.r...@gmail.com]

Baskar:

I see your scenario and is kind of different than it was for me. I was
facing timeouts while using the HAT, it was an annoying situation, but
I have to "recover" several messages from the DTADb, I thought you
were thinking of a similar approach to mine. Anyway, the table I was
mentioning is the 'dbo.Tracking_Parts1' that you can find in the
'BizTalkDTADb' database, where all tracked messages are stored. The
fields (do not remember the exact name then) are imgPart and
imgPropertyBag that are related to a MessageID. And this messageid is
what is supposed to use in the interface to retrieve the original
message. But since your scenario is kind of different, I believe this
is not useful for you.
Sorry for misleading your investigation, but I thought you were in the
same/similar trouble.

Good luck !

On 14 feb, 23:09, BASKAR BV <BASKA...@discussions.microsoft.com>
wrote:

[Sander Schutten]

Baskar,

If you want to be able to search for messages in HAT:
1) Promote the properties in the schema you probably want to query on
2) Enable tracking for these promoted properties. Do this in HAT.

If you want to collect message info with BAM:
1) Setup your BAM activity and view and define data items you want to
collect
2) Connect the data items with fields in your message using the
tracking profile editor.

Be careful with promoting too much properties as each will cost you
performance.

[Niriven]

I will not get into the biztalk DB specifics, but:

In the biztalk message box DB, there are two tables:

MessageParts - High level message data (including message id, message type,
and relations to parts table)
Parts - Holds the part data itself

In parts, there is a field called imgPart. This is the message itself. This
is a compressed (encoded) binary format.

Instructions to read imgPart in a plain text (readable) format:

1) Create a new managed project (some sort of .net project)
2) Reference Microsoft.Biztalk.Pipeline.dll
3) Query for the messages you need in the DB and fill a dataset.
4) Write code to read the imgPart field (Snippet below)

foreach (DataRow row in ds.Tables["MyImages"].Rows)
{
SqlBinary binData = new SqlBinary((byte[]) row["imgPart"]);
// encoded imgPartField
MemoryStream stm = new MemoryStream(binData.Value);
Stream aStream = CompressionStreams.Decompress(stm);
//Decompress and decode binary stream
StreamReader aReader = new StreamReader(aStream);

string aMessage = aReader.ReadToEnd(); // Read message in plain format

Console.WriteLine(aMessage);
}

The important part here is the functionality,
CompressionStreams.Decompress(stm), which will create a readable stream.

If you have any questions feel free to contact me at rrl...@gmail.com or
nefa...@hotmail.com