Custom SQL Management Pack Invoke-SqlCmd Exception

Posted by : on

troubleshooting   guides   operationsManager


The customer noticed the issue was occurring inside of one of their custom Management Packs (MP) for SCOM SQL Blocking monitoring. They deployed this custom MP to two SCOM Management Groups. The Agents where we were seeing the issue were dual (multi) homed SCOM Agents that were SQL Servers (due to the way the MP was designed).

Script Section

The command inside of the custom Management Pack was Invoke-SqlCmd, which is apart of the built in SQL Powershell Cmdlets:

$conn = "MSSQL-2019"
$ScriptName = "Custom.SQLBlocking.Timed.Monitor.DataSource.ps1"
[int]$WaitTimeMS = $WaitTime * 1000
$blocked_session = $null
$sql1 = "use master
                  SELECT r.session_id,
                  s.memory_usage as Memory,
                  DB_NAME(r.database_id) AS DatabaseName,
                  t.text as Query_Text
                  FROM sys.dm_exec_requests r
                  CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
                  INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
                  WHERE r.blocking_session_id <> 0 and wait_time > $WaitTimeMS"
Try {
$blocked_session = Invoke-Sqlcmd -Query $sql1  -ServerInstance $conn
Catch {$momapi.LogScriptEvent($ScriptName, 9994, 0, $error)}

Invoke-SqlCmd in the above context should be able to successfully run with Local System (or a User Account) as the Action Account.

Catching the error

We noticed the following error after the script above ran as a part of the Management Pack:

Custom.SQLBlocking.Timed.Monitor.DataSource.ps1 : ManagedBatchParser.ParserException
    at ManagedBatchParser.Parser.Parse()
    at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(String sqlCommand)


You will need to set the time intervals between the rules / monitors to run at a different interval than the other Management Group that is reporting to this same Agent / Group to cause it to be multi-homed.


You can remove the other Management Group and allow only 1 Management Server in the Agent Management control panel.

Page Views

Share on:
About Blake Drumm
Blake Drumm

I like to collaborate and work on projects. My skills with Powershell allow me to quickly develop automated solutions to suit my customers, and my own needs.

Email :

Website :

About Blake Drumm

My name is Blake Drumm, I am working on the Azure Monitoring Enterprise Team with Microsoft. Currently working to update public documentation for System Center products and write troubleshooting guides to assist with fixing issues that may arise while using the products. I like to blog on Operations Manager and Azure Automation products, keep checking back for new posts. My goal is to post atleast once a month if possible.

Follow @blakedrumm
Useful Links