Comman Table Expression in MS-SQL

Hi All,

Recently i had to generate a report on monthly throughput of invoices processed through BizTalk Server

In current architecture we are storing the each invoice information in MS-SQL server as primarily source of information. here we are stamping each batch predefined with standard format and ending with MMDDYY format.

In MS-SQL we have feature called Comman Table Expression(CTE) which better form of TempTable and with the help of this i can achieve the required report.

Here is example,

BATCH_ID
TEST-1-063118
TEST-2-063118
TEST-3-063118
TEST-1-070118
TEST-2-070218
TEST-3-070218
TEST-4-070218
TEST-2-070318
TEST-3-070318
TEST-3-070418
TEST-4-070418

WITH CTE AS (SELECT RIGHT(BATCH_ID,6) AS 'BATCHTRIM' FROM [dbo].BATCH_MASTER (NOLOCK))

SELECT BATCHTRIM, COUNT(BATCHTRIM) AS 'TOTALBATCHES' FROM CTE C
GROUP BY BATCHTRIM ORDER BY BATCHTRIM ASC

Output:
BATCHTRIM       TOTALBATCHES
063118                   3
070118                   1
070218                   3
070318                   2
070418                   2

Hope this Help!!!

BizTalk Debugging and Breakpoint Instances

Hi All,

Recently i had a situation where by mistake of developer some class level debugging point were left selected and thus resulting subsequent service instances were stuck in Break point.

BizTalk provides the great feature of Instance Statistics for both Running and Suspended.

Usually people find states which familiar to them, where as there is one more state which is come in picture when you have enabled debugging.


1  – Ready to run  
2  – Active  
4  – Suspended (resumable)  
8  – Dehydrated  
16 – Zombies (completed with discarded messages)  
32 – Suspended  (not resumable)  
64 – In Breakpoint

Additionally following MS-SQL query will be helpful in getting count of messages (Application wise) in Different states.


SELECT nvcName as ApplicationName,
CASE Instances.nState
WHEN 1 THEN ‘Ready To Run’
WHEN 2 THEN ‘Active’
WHEN 4 THEN ‘Suspended Resumable’
WHEN 8 THEN ‘Dehydrated’
WHEN 16 THEN ‘Completed With Discarded Messages’
WHEN 32 THEN ‘Suspended Non-Resumable’
WHEN 64 THEN ‘In-Breakpoint(Active)’
END as State,
count(Instances.nState) as Count
FROM Instances
LEFT OUTER JOIN InstancesSuspended
on Instances.uidInstanceId = InstancesSuspended.uidInstanceID
LEFT OUTER JOIN [Services]
on Instances.uidServiceID = [Services].uidServiceID
LEFT OUTER JOIN Modules
on Modules.nModuleID = [Services].nModuleID
group by nvcName,Instances.nState


And following MS-SQL query gives the messages count with Instance Names


SELECT distinct
Modules.nvcName as ApplicationName,
CASE Instances.nState
WHEN 1 THEN 'Ready To Run'
WHEN 2 THEN 'Active'
WHEN 4 THEN 'Suspended Resumable'
WHEN 8 THEN 'Dehydrated'
WHEN 16 THEN 'Completed With Discarded Messages'
WHEN 32 THEN 'Suspended Non-Resumable'
WHEN 64 THEN 'In-Breakpoint(Active)'
END as State,
count(Instances.nState) as Count ,
SUBSTRING(Subscription.nvcName,0,CHARINDEX('{',Subscription.nvcName,0)) as Itinerary,
Subscription.nvcApplicationName as Host
FROM Instances WITH (NOLOCK)
LEFT OUTER JOIN InstancesSuspended
on Instances.uidInstanceId = InstancesSuspended.uidInstanceID
LEFT OUTER JOIN [Services] WITH (NOLOCK)
on Instances.uidServiceID = [Services].uidServiceID
LEFT OUTER JOIN Modules WITH (NOLOCK)
on Modules.nModuleID = [Services].nModuleID
LEFT OUTER JOIN [Subscription] WITH (NOLOCK)
on [Services].uidServiceID = [Subscription].uidServiceID
where Modules.nvcName is not null
group by Modules.nvcName,Instances.nState,Subscription.nvcName,Subscription.nvcApplicationName
order by Modules.nvcName desc

Note: Use BizTalkMsgBoxDb to use all above queries.

Happy BizTalking.!!!!

XLANGs.BTEngine.BTXTimerMessages inside BizTalk Message Box

Hi,

I thought i would like to share BTXTimerMessage with BizTalk lovers.

BTXTimerMessage ???

They are messages BizTalk uses internally to control timers.  This includes the delay shape and scope shapes with timeouts.



Where am i resides - BTXTimerMessage ???

You will see these messages in BizTalk Administration Console, They will be associated with running Orchestations. If they show up, they will be in the Delivered, Not Consumed (zombie) message status.



When i born - BTXTimerMessage ???

I see these messages when I am working with parallel actions, atomic scope, and convoys.  My sample, Limit Running Orchestrations, produces these types of message.  I have not been able to pin point the exact shapes or actions that cause these messages to show up.



Why i born BTXTimerMessage ???

Good question.  I have a theory, but it is probably wrong.  It is that the timer message is returned to the Orchestration after if has passed the point of the delay or scope shape.  Thus, it is never consumed by the Orchestration.



Am i useless ???

I think so.  I always ignore them.  They will go away when the Orchestration completes or is terminated.  These do not seem to act like normal zombies in that they do not cause the Orchestration to Suspend.



Still Confused about BTXTimerMessage ???

Let me fill you in.  BTXTimerMessage in the Delivered, Not Consumed status are sometimes seen in HAT when working with Timers.  I have not really determined why they happen, but I suspect they should not show up in HAT at all.  I do not think they hurt anything and I pay little attention to them.  When the Orchestration finally ends or is terminated these messages simply go away.  They are annoying and in long running transactions can start to stack up in BizTalk Administration Console.  Although, if you try to terminate these messages they will take down the running Orchestration with them.


Happy BizTalking !!!