Archive for April, 2008

Fast, Efficient Message Archiving for BizTalk with the BizTalk Message Archiving Pipeline Component. Download a Free 14-Day Trial!

Truncating the BizTalk 2006 Tracking Database

In Truncating the BizTalk 2004 Tracking  Database I discussed how to truncate the tracking database in BizTalk 2004. Over on the BizTalk Gurus forums, user Nick Busy wanted to do the same thing for BizTalk Server 2006 – he’s kindly allowed me to repost his instructions for the community on this blog:

0. Before start, ensure you have got the database admin priveleges on the database

1. Stop all BizTalk Server Host Instances

2. Full backup BizTalkDTADb database (just in case)

3. Make scripts to create views (MANDATORY)

dbo.dtav_ServiceFacts
dbo.dtav_MessageFacts
dbo.dtav_FindMessageFacts

4. Run SQL script:

use BizTalkDTADb
GO

– Drop the Views (before you perform this, ensure you take copies of these views!)
– unfortunately, it’s necessary for SQL 2000, but you can skip it for SQL 2005
Drop View dbo.dtav_ServiceFacts
Drop View dbo.dtav_MessageFacts
Drop View dbo.dtav_FindMessageFacts
Go

– Truncate the necessary Tables
Truncate Table dta_CallChain
Truncate Table dta_DebugTrace
Truncate Table dta_MessageInOutEvents

Truncate Table dta_ServiceInstanceExceptions
Truncate Table dta_ServiceInstances

Truncate Table Tracking_Fragments1
Truncate Table Tracking_Parts1
Truncate Table Tracking_Spool1

Truncate Table dta_MessageFieldValues

– end of the script

5. Update statistics on BizTalkDTADb database

– update statistics
exec sp_updatestats

6. Run the saved scripts (see step 3) to recreate the dropped views from your own environment.

7. Shrink BizTalkDTADb database (sometimes it doesn’t work from GUI, so using sql command will help)

– shrink database
dbcc shrinkdatabase (BizTalkDTADb, 10)

8. Start BizTalk Server Host Instances

9. Configure and enable SQL Agent job “DTA Purge and Archive” (to avoid over-growing the database in the future)

P.S. The script above does not truncate Rule Engine related tables.

Thanks Nick, much appreciated.

XmlAssembler TargetCharset Property Error in BizTalk 2006 & 2006 R2

Update: Tomas Restrepo added a comment to this post in which he explains why this particular ‘feature’ is present – I think its worth repeating here:

…the problem was that the Target Charset property of the assembler actually gets written as two separate elements when serialized to the pipeline XML file, and both must be present for the assembler to actually figure out the encoding to use… So what happens is that the metadata in the actual design time properties for the assembler component only actually represents one of those elements in the serialized format, and this is what the per-instance pipeline configuration dialog uses to ask the user to enter the values. So in essence, the dialog only allows you to edit one of the values and not the other, so you always end up with the component incorrectly configured.

Thanks Tomas!

While researching a post about message encoding in BizTalk, I can across this Microsoft Knowledge Base article regarding the TargetCharset property on the XmlAssembler component – it would appear that if the property is set through the Admin Console, it won’t take effect. This is strange, because this bug appears to have been fixed in BizTalk 2004 SP1, but somehow made it back into all versions of BizTalk 2006 & 2006 R2. Furthermore for BizTalk 2006, Microsoft only offer a work-around rather than a Hotfix which is frustrating.

So, what happens when you attempt to set the TargetCharset property? The default encoding when using the XmlTransmit pipeline – without the TargetEncoding property set – is a UTF-8 encoded Xml message (click on the image for a full-size version):

UTF-8 Encoded Message

If you change the TargetCharset property on the XmlAssembler component of the default XmlTransmit pipeline in the BizTalk Admin console (as shown below), the change is not picked-up and the same UTF-8 encoded message (as above) is returned; in this case, we’re trying to change it to UTF-16:

XmlAssembler-AdminConsole-TargetCharsetProperty

To change the encoding, you must create a custom pipeline and use the XmlAssembler with the TargetCharset property set appropriately, as follows:

XmlAssembler Custom Pipline Component TargetCharset Property

Using this new custom pipeline with the TargetCharset property correctly configured to output UTF-16 encoded messages, we acheive our desired encoding, as follows (click on the image for a full-size version):

UTF-16 Encoded Message

Encoding can also be acheived by setting the XMLNORM.TargetCharset property of the message to be output in an Orchestration Message Assignment shape as follows:

<MessageName>(XMLNORM.TargetCharset) = "UTF-16";

Note, this testing was performed on BizTalk 2006 R2.



Get Adobe Flash playerPlugin by wpburn.com wordpress themes