Hi All,
Below is the power shell script for generating the event ID in the event viewer in case if any BizTalk orchestration is in suspended mode.
As of now, this script only useful for one orchestration checking at a time, in future I will modify and update the PowerShell script for publishing the events for multiple orchestrations at a time.
Copy the below script and save it in note pad with .PS1 extension and schedule a task job if you want to run on daily basis.
Below is the website link on how to Use the Windows Task Scheduler to Run a Windows PowerShell Script
For below script to be successful, you must create a linked server between BiztalkMgmtDB and biztalkmsgboxdb if both databases are residing in different servers.
I am writing different power shell script to make use of WMI instead of using Linked servers and I will update the blog in future posts.
$sqlText = “—————————-Start of Script————————————————————————–
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW
declare @ErrorOrchCount int,@ErrorMsg varchar(100)
SELECT
@ErrorOrchCount= Count(o.nvcName)
FROM [BizTalkMsgboxDb]..[Instances] AS i WITH (NOLOCK)
JOIN [BizTalkMgmtDb]..[bts_Orchestration] AS o WITH (NOLOCK) ON i.uidServiceID = o.uidGUID
WHERE o.nvcName =’OrchestrationName’ # Give the Orchestration Name
and i.nState not in (2,8)
GROUP BY o.nvcName, i.nState
if (@ErrorOrchCount >0)
begin
set @ErrorMsg=’Orchestration is in Suspended Mode’
end
else
Begin
set @ErrorMsg=Null;
END
”
#Provide the SQL Server Name on which sql statements needs to be executed
$serverName= “servername”
$QueryTimeout = 120
$ConnectionTimeout = 30
#Action of connecting to the Database and executing the query and returning results if there were any.
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString = “Server={0};Database={1};Integrated Security=True;Connect Timeout={2}” -f $ServerName,$DatabaseName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=New-Object system.Data.SqlClient.SqlCommand($sqlText,$conn)
$cmd.CommandTimeout=$QueryTimeout
$reader = $cmd.ExecuteReader()
while ($reader.Read()) {
$OrchStatus =$Reader.GetValue($1)
}
if ($OrchStatus)
{
Write-EventLog –LogName Application –Source “OrchestrationException” –EntryType Error –EventID 5012 #event id that you want to raise for SCOM to recognize
–Message “ Orchestration is in Suspended Mode.”
}
Please let me know if you have any questions.
Regards,
Chaitanya
SQL BLOGGING 