Getting Custom Queries into HAT

You probably know that you can write your own custom HAT queries apart from the default HAT Queries that are available . I have seen people write their own little queries that they write or get from somewhere and use it to run some specific query often by copying into the text box of a show query window .

Did you know you can get your custom queries into HAT .See Here



If you want to do that it is not so hard. All the tracking queries are stored in
C:\Program Files\Microsoft BizTalk Server 2004\Tracking\Queries
or
C:\Program Files\Microsoft BizTalk Server 2006\Tracking\Queries as the case maybe or wherever you have your Biztalk installation directory .

You can make a copy of one of the templates and rename it to yourQueryName.trq and open the file in a notepad then you will see an
Modify the second value for the name you want to appear on the drop down.
and the fourth with your new Query .
don't change any of the other parameters .

Always make sure you test your query first in the HAT window before you do this .




Now the Pivot Table Part . This is the part where you are going to describe your result set and you start with the which you can leave as it is and
then come to the . You have to modify the PivotField each of which describes your result set each has certain elements

Name -- This is the Name of your Column
DataType !-- This is Data Type Date , Integer ,Number
PLDataOrientation !-- leave empty a.k.a No idea ,if U know leave comment :)
PLPosition !-- The column number like 1 ,2
EncodedType !-- Type adInteger,adVarChar
CompareOrderedMembersBy !-- UniqueName

You need to have < for < and > for > which is XML encoded text and namespace of x: , basically follow the template

Save and close and open your HAT . You should see your new query in HAT if everything went well . In case you get any error Close HAT and reopen the file and fix it . HAT actually locks the file . Hope that was of help