I am runnng a Microsoft SQL sensor on the PRTG remote probe with a SQL script (see below for the script). When I run the script from Management Studio it works as it should (returns 0-5 affected rows). However, when I try to run the same script with PRTG it has 300+ affected rows. I am creating a temp table which generates the 300+ rows, but then I have a filter to only pick out certain rows (the 0-5 row mentioned earlier).

Here is the script:

-- Create tmp table from sp_who results
USING MASTER
CREATE TABLE #TmpWho
(spid VARCHAR(150), 
Status VARCHAR(150), 
login VARCHAR(150),
hostname VARCHAR(150), 
blk VARCHAR(150), 
dbname VARCHAR(150), 
cmd VARCHAR(150), 
CPUtime VARCHAR(150), 
DiskIO VARCHAR(150), 
LastBatch VARCHAR(150), 
ProgramName VARCHAR(150), 
spid2 VARCHAR(150), 
reqid VARCHAR(150))
INSERT INTO #TmpWho
EXEC sp_who2

-- filter temp table where blk has special data
SELECT * FROM #TmpWho
WHERE blk not like '%.%'

DROP TABLE #TmpWho

Any ideas as to why PTRG is showing the unfiltered rows?

Just in case, here is my results from sensor:

18.03.19 15:16:54: Connections String: Data Source=REDACTED;Initial Catalog=master;User ID=sa;Password=******;Connect Timeout=60
18.03.19 15:16:54: Opening Connection to MSSQL Server 'REDACTED'
18.03.19 15:16:54: Running Command 'USE MASTER
CREATE TABLE #TmpWho
(spid VARCHAR(150), 
Status VARCHAR(150), 
login VARCHAR(150),
hostname VARCHAR(150), 
blk VARCHAR(150), 
dbname VARCHAR(150), 
cmd VARCHAR(150), 
CPUtime VARCHAR(150), 
DiskIO VARCHAR(150), 
LastBatch VARCHAR(150), 
ProgramName VARCHAR(150), 
spid2 VARCHAR(150), 
reqid VARCHAR(150))
INSERT INTO #TmpWho
EXEC sp_who2


SELECT * FROM #TmpWho
WHERE blk not like '%.%'

DROP TABLE #TmpWho'
18.03.19 15:16:54: Command completed with 330 affected Rows
18.03.19 15:16:54: Closing Connection to MSSQL Server 'REDACTED'
18.03.19 15:16:54: Execution Time: return 143.0067 [Double]
18.03.19 15:16:54: Query Execution Time: return 71.0033 [Double]
18.03.19 15:16:54: Affected Rows: return 330 [Int64]

Image of the same script being run from SQL server: https://i.imgur.com/OmkWbYU.jpg


Article Comments

Could you put that query into a stored procedure and execute the same in PRTG instead? That's likely to work, as it puts the execution of rather complex queries like that :)


PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team


Mar, 2019 - Permalink