Microsoft SQL v2 sensor with declared variables

Hello,

Thanks for your advice. I fixed this by removing all the semicolons from the SQL script except the last one. The script is now:

/* Vantage license query - returns the number of licenses free for users to obtain */
/* Declarations - leave these alone */ 
DECLARE @totalSeats INT
DECLARE @reusableSeats INT
DECLARE @usedSeats INT
DECLARE @serialNum DECIMAL
/* End Declarations */


/* Set the license serial number to check here */
SET @serialNum = 'serial number here'


/* Get the total number of seats we are licensed for */
SELECT @totalSeats = SUM(maxusers) FROM liccnfg WHERE serialnum = @serialNum

/* Get the number of seats released by clients which can be picked up by others */
SELECT @reusableSeats = COUNT(*) FROM licuser 
WHERE DATEDIFF(second, DATEADD(second, lasttime, lastdate), GETDATE()) > "timeout" 
AND "licnum" > 0 
AND "sessiontype" = ''

/* Get the total number of seats currently in use */
SELECT @usedSeats = COUNT("licnum") FROM licuser
WHERE "licnum" > 0
AND "sessiontype" = ''

/* Select the number of available licenses a client can use */
SELECT (@totalSeats - @usedSeats) + @reusableSeats as "Available Licenses";

Looking at the sensor log data, it seems the Microsoft SQL v2 sensor was splitting the script up by semicolon and running each statement as a single query, rather than as a whole.

Removing all the semicolons minus the last one got the sensor reporting properly.

Very well, good to hear it's working now.

Kind regards.

Hello,

I'd like to post back and mention that this issue has appeared after upgrading to 17.2.30.1883.

The sensor log:

18.05.17 10:47:16: Impersonating 'username_redacted'
18.05.17 10:47:16: Connections String: Data Source=server_redacted;Initial Catalog=db_redacted;Integrated Security=True;Connect Timeout=60
18.05.17 10:47:16: Opening Connection to MSSQL Server 'server_redacted'
18.05.17 10:47:16: Running Command 'SELECT (@totalSeats - @usedSeats) + @reusableSeats as "Available Licenses"'
18.05.17 10:47:16: Closing Connection to MSSQL Server 'server_redacted'

I'm running the same query as above with only the last semicolon but it looks like PRTG is only executing the final statement in the SQL file.

Any ideas?

This sounds like a bug we have found some time ago, where SQL statements break when using DECLARE. A fix is on its way. For now there is workaround: Add a new line after DECLARE (sounds strange, but it works...)

For example instead of DECLARE @totalSeats INT you do this: DECLARE @totalSeats INT

Do this for every DECLARE, then it should be fine.

Kind regards,

Erhard

Adding the new line between DECLARE and the variable line didn't make any difference initially. I did however remove the comments from the script which worked in addition to the new lines with DECLARE.

It seems the command parser is matching on the first /* and the last */ in the file, which explains why the only command executed was the final line in the script.

The working query is:

DECLARE
@totalSeats INT
DECLARE
@reusableSeats INT
DECLARE
@usedSeats INT
DECLARE
@serialNum DECIMAL

SET @serialNum = 'serial num here'

SELECT @totalSeats = SUM(maxusers) FROM liccnfg WHERE serialnum = @serialNum

SELECT @reusableSeats = COUNT(*) FROM licuser 
WHERE DATEDIFF(second, DATEADD(second, lasttime, lastdate), GETDATE()) > "timeout" 
AND "licnum" > 0 
AND "sessiontype" = ''

SELECT @usedSeats = COUNT("licnum") FROM licuser
WHERE "licnum" > 0
AND "sessiontype" = ''

SELECT (@totalSeats - @usedSeats) + @reusableSeats as "Available Licenses"

Thank you for sharing your findings, looks like I need to update the bugticket regarding this issue.

Thanks again & kind regards,

Erhard