Ping command utility with datetime stamp

Dear All,

Sometimes using the external client FTP/SFTP or shared folder path it has been observed that files getting missed or file un-properly copied with data loss. to avoid such circumstances we have a nice feature in windows called PING, we can utilize the capability of ping to monitor the network availability. so i have added some more concrete logging with the help of datetime command. here is the entire script which one can save as .BAT file and continue to run in background.


@echo off
    ping -t ftp.yourftp.com|cmd /q /v /c "(pause&pause)>nul & for /l %%a in () do (set /p "data=" && echo(!date! !time! !data!)&ping -n 2 ftp.yourftp.com>nul"


Happy Coding!!!!!!

Resume Suspended BizTalk Instances by Powershell

Hi

Recently I had in situation where a large number of instances were stuck in BizTalk which needs to be resumed faster as possible.

Following i did,

1) Query the BizTalkMsgBoxDb database to get all suspended messages by error description

select * from [dbo].[InstancesSuspended](nolock)Where lower(nvcerrordescription) like '%SearchError%'

2) Provide the , separated list of instances to following power shell script and run the script which resumed the instances on one shot as desired.

## ServiceClass members
# Description Value
# Orchestration 1
# Tracking 2
# Messaging 4
# MSMQT 8
# Other 16
# Isolated adapter 32
# Routing failure report 64

## ServiceStatus members
# Description Value
# Ready to run 1
# Active 2
# Suspended (resumable) 4
# Dehydrated 8
# Completed with discarded messages 16
# Suspended (not resumable) 32
# In breakpoint 64

#Get a list of suspended messages with info from WMI
$suspendedMsgs = Get-WmiObject MSBTS_ServiceInstance -Namespace 'root\MicrosoftBizTalkServer' -Filter '(ServiceClass = 4 or ServiceClass = 1) and (ServiceStatus = 4 or ServiceStatus = 16 or ServiceStatus = 32)' | sort SuspendTime -Desc
# Note if you want to use the datetimes from WMI use [management.managementDateTimeConverter]::ToDateTime() to convert.
foreach($msg in $suspendedMsgs)
{
    #Replace your instances list over here needs to be resume
    $msgInstnces = "905EA5D3-2BB9-4FA0-B8BC-74BB5D44EB9D,430A863B-9C4A-47CB-8953-4F6CB80AAD75".Split(",")
    foreach($msgInstnce in $msgInstnces)
    {
        if($msg.InstanceId -eq "{" + $msgInstnce + "}")
        {
        $msg.InvokeMethod("Resume",$null)
        }
    }
}


Happy Coding!!!!!

Search Text in all stored procedures of database

Hi All,

To those who want to search any text within all stored procedure of particular database then you can utilize the following system tables sys.sql_modules and sys.objects and search for given text.


Here is the MS-SQL query, be noted you need the elevated access to run this query.

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
  FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
WHERE m.definition Like '%\[ABC\]%' ESCAPE '\';


the above search the text [ABC] in all stored procedure objects in particular database.

Happy Coding!!!!!

Handling The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION error in MS-SQL

Hi All,



When ever are you using Transactions in your T-SQL statements or in functions or stored procedures you should always make sure there is high chances of Transactions are committed or roll-backed due to one or other reasons and you might encounter the following error every time you trying to commit or rollback the transaction which is already processed.



To avoid the code failure and MS-SQL to raise this error we should better to handle the code by checking the condition if any transaction is active in current session.



we have @@TRANCOUNT global variable which gives the count value of active transactions. we can use this global variable value before committing or roll-backing the transactions.



i.e.



IF(@@TRANCOUNT>0)

  COMMIT TRANSACTION



OR



IF(@@TRANCOUNT>0)

  ROLLBACK TRANSACTION







Happy coding

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 !!!