PowerShell to Add or Remove assembly to GAC

Hi All,

Very often during the BizTalk Development we require to register the Assembly in GAC. This tasks seems to be very easy on your development box but when you are going to deploy to TEST or STAG environment, you will find yourself in difficulties when you don't find Visual Studio command prompt!!

To ease such situation in Windows have a nice feature of PowerShell using which one can deploy the Assembly in GAC.

Similar way to Remove the Assembly from GAC use the method GACRemove()



Here is the snippet,


#Note you should be running PowerShell as an Admin

[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")           

$publish = New-Object System.EnterpriseServices.Internal.Publish           

$publish.GacInstall("C:\YourDLLPath\To\YourDLL.dll")



#If installing into the GAC on a server hosting web applications in Internet Information Services, you need to restart IIS for the applications to pick up the change.

#Uncomment the below line if necessary...



#iisreset




Hope this Helps!!!!!!


HTH

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