New Question
 
 
PRTG Network Monitor

Intuitive to Use.
Easy to manage.

200.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free PRTG
Download >>

 

What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

 

Top Tags


View all Tags


Microsoft SQL v2 sensor with declared variables

Votes:

0

Your Vote:

Up

Down

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.

microsoftsql sql-v2 sqlsensor

Created on Aug 23, 2016 1:37:13 PM by  Captec IT Support (20) 1 1



7 Replies

Accepted Answer

Votes:

1

Your Vote:

Up

Down

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.

Created on Aug 24, 2016 11:58:30 AM by  Erhard Mikulik [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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.

Created on Aug 24, 2016 2:03:01 PM by  Captec IT Support (20) 1 1



Votes:

0

Your Vote:

Up

Down

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

Kind regards.

Created on Aug 24, 2016 2:54:34 PM by  Erhard Mikulik [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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?

Created on May 18, 2017 10:03:48 AM by  Captec IT Support (20) 1 1

Last change on Aug 10, 2017 5:40:28 AM by  Luciano Lingnau [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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

Created on May 18, 2017 12:11:06 PM by  Erhard Mikulik [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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"

Created on May 18, 2017 12:35:33 PM by  Captec IT Support (20) 1 1



Votes:

0

Your Vote:

Up

Down

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

Thanks again & kind regards,

Erhard

Created on May 18, 2017 12:38:52 PM by  Erhard Mikulik [Paessler Support]

Last change on May 18, 2017 12:41:22 PM by  Erhard Mikulik [Paessler Support]



Please log in or register to enter your reply.


Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.