BizTalk Artifacts Monitoring

I have to create jobs to monitor send ports, orchestrations and receive locations and this post will be update as i make progress.

Below is a list of things to remember and consider when creating these monitoring jobs:

• All BizTalk databases should not be considered as our usual databases and extreme caution should be practices when using them
• BizTalk installation has 4 databases BizTalkMsgBoxDb, BizTalkMgmtDb, BizTalkTrackingDb and SSODB
• These databases are dependent on each other and data is moved between them 
• Check on using WITH (READPAST): excludes locked rows from result set

To Be Cont....

SELECT TOP from @VAR and DISTINCT TOP @VAR

Simple and useful:

SELECT TOP (@MAX_REC_COUNT)
from DBO.MY_TABLE WITH (NOLOCK);

This way we can get the @max_rec_count from a config table instead of hard coding the record count to retrieve. This is useful when you can modify the config table from a UI instead of making code changes.

I wish there was a better way to get DISTINCT TOP @VAR from a query but unfortunately no better way than:

SELECT TOP @MAX_REC_COUNT 
FROM (
           SELECT DISTINCT COL1, COL2, ...COLN
           from DBO.MY_TABLE WITH (NOLOCK)
          ) XX;


HTH