Hi there I want PRTG to run this Script and respond with OK if there is 0 and Error if there is 1 or more

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id =tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address =wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id =tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id =tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id =wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

when I set it up and run it in PRTG I get the error of Invalid Syntax near go when i run the script in SQL Manager it works fine

Please advise thanks Ash

Article Comments

Would it be possible to migrate this into a stored procedure (so that PRTG only checks the result table)? It may be the cross apply commands, although I'm not sure.. :) What happens when you remove the GO statement at the end?


May, 2016 - Permalink

Hi there If I take the GO statement away, it errors out in PRTG with no Valid Database Returned Although it still works in SQL Does PRTG have issues with GO Statements?

Thanks Ash


May, 2016 - Permalink

It was just an assumption...what's the actual result of the query? Screenshot will do as well.


May, 2016 - Permalink