Hello,
I'm moving a query from the v1 sensor to the new v2 type. I've created the SQL file in the correct place, selected the correct database and have tried to run the sensor.
When run, the sensor returns 'Must declare the scalar variable "@totalSeats".' as an error. The full SQL script is below:
/* 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";
I can run this query successfully from SQL Server Management Studio, it returns one column named "Available Licenses" with the number of licensed seats available.
Article Comments
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.
Aug, 2016 - Permalink
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?
May, 2017 - Permalink
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
May, 2017 - Permalink
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"
May, 2017 - Permalink
Thank you for sharing your findings, looks like I need to update the bugticket regarding this issue.
Thanks again & kind regards,
Erhard
May, 2017 - Permalink
Hello there,
Try removing the semicolon at the end of each "DECLARE" line. Does it work then? If not, please enable "Write result to disk" in the sensor's "Settings" tab. With the next scan it will write one or several result-files into "C:\ProgramData\Paessler\PRTG Network Monitor\Logs (Sensors)" of your PRTG server (or on the Remote Probe if applicable). All files have the sensor's numeric ID in their filenames. The sensor ID can be found on the sensor's "Overview" tab. Please send us those result-files (support@paessler.com; use PAE747388 in the email's subject, so the ticket stays connected to this thread and gets to me).
Kind regards.
Aug, 2016 - Permalink