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