What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general.

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

Timeout with sql-sensor

Votes:

0

Hi There,

I've created a basic SQL sensor to run a query and check the response. As soon as I start the sensor I get a timeout error.

Steps I've taken:

- create a windows user with DB read access mapping for Database X within MS SQL server. - create a simple "select" query and the output is "4" - logon into SQL server and login with DB read user and run the query, the output shows "4". - create a basic SQL Sensor for DB X and type in the same select query. Authentication type: Windows authentication. - Select credentials: a user account with domain admin membership. - OS: Windows W2K12 and SQL 2008 Server.

I Saved the sensor, wait for a minute and then shows a timeout error.

Is it something I'm overlooking here?

cheers R.

error sqlsensor timeout

Created on Dec 9, 2014 2:42:05 PM



Best Answer

Accepted Answer

Votes:

1

Hey roberto,

I've checked it on one of my servers, I don't write instances at all.It was a misspelling from me. Here is a functional sample for setting up an MSSQLv1 Sensor:

  • SQL-Server specific data
  1. Timeout: 60 (seconds maybe default ;))
  2. Instances: <<clear>> (I don't write it down, for me it works without it)
  3. Port: automatic
  • Database
  1. Database: <<name of your database / maybe: NEDIRIUM_GG6 >>
  2. Authentification: I've used the windows settings, as I said before, this is much easier.
  • Data
  1. SQL-Statement: << your statement (in some cases it could be with brackets around "dbo">>
  2. specific settings for your matching, this differs to every sensor

Sorry for the translation, your titles may diff them in the tutorial. I'm using a german version ;)

Sensor v1 only display's or matches one result, so please don't use a star in your statement. This is how it works for me, the v2 sensor i don't set up till now, because ist still beta and for me not useful till now. If i've more time, I will take a look closer to this sensor.

As additional help, here a sample select statement from Microsoft MSDN library ( http://msdn.microsoft.com/de-de/library/ms177682.aspx ):

SELECT p.FirstName, p.LastName, e.JobTitle FROM Person.Person AS p JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID WHERE e.JobTitle IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant');

As you can see, you are on the right way. Please try it this way. And If you don't use the v1 sensor, so please share this information.

Thanks an Best

Sascha

Created on Dec 11, 2014 10:03:16 AM



13 Replies

Votes:

3

Hi again roberto ;),

Correct me if im wrong, the credentials of the user who will access this database are not the same as for PRTG itself? Why? Of course this could be a security issue. But as a monitoring system it should be getting access to all systems?

In MSSQL itself you can give your PRTG User ReadOnly, so you're on the right side with security reasons.

What i wanted to say, maybe the user you've created has not the right to access via external interfaces. Please check it and also if it is possible to you, that PRTG Domain user got access to this database. In case of this you can use the credentials by PRTG. For MSSQL this is much easier than set up the credentials by your self.

Thanks

Sascha

Created on Dec 9, 2014 4:07:42 PM



Votes:

0

Hi Sascha,

I forgot to mention we also tried to use the user account for PRTG. We gave that one SQL read mappings on the database. Error message stays the same..

Cheers R.

Created on Dec 9, 2014 4:50:49 PM



Votes:

3

Did you check that the user has this rights? Login as PRTG Domain Admin (same windows account like the probe uses) on MSSQL and do a select statement?

If this works, than could it be a bigger point.

You can monitor other sensors on this system? Next to the SQL sensor you wanted to set up? So you can reach the MSSQL server and instances from PRTG-Server?

Best

Sascha

Created on Dec 10, 2014 6:45:51 AM



Votes:

0

Hi Sascha,

I've logged on with the PRTG user on the MSSQL Server and connected to the database. I ran the query and got the right output. Other sensors on that device operate fine.

Cheers R.

Created on Dec 10, 2014 10:02:06 AM



Votes:

0

I've resolved some firewall/connectivity issues:) And now got this error message:

Could not perform query: Could not convert variant of type (String) into type (Double)

Does this imply the query is wrong?

When I copy/paste the query in MSSQL server it works fine.

Cheers R.

Created on Dec 10, 2014 10:38:56 AM



Votes:

6

Ahh there are the problems ;).

Yes this means the query can't show the result, because of a "string" - "double" issue.

Can you paste the query to this board? Maybe we could help you with this issue. Otherwise control the type of the field you want to show. Could it be that you request an string but the field is an double?

Best Sascha

Created on Dec 10, 2014 10:47:24 AM



Votes:

0

This is the query:

select * from NEDIRIUM_GG6.dbo.NODE_STATE where NODE_NAME in ('CalifornicationToGGG', 'DAGGG_Plus_Receive', 'DAGGG_Plus_Send', 'ALTOBatchInFF','FROGGGM')

Created on Dec 10, 2014 4:10:54 PM



Votes:

0

Hey roberto,

first, you don't have to reclassify the database name "NEDIRIUM_GG6". You will start with dbo because the instance is named in an extra box. Furthermore a star as select statement ist not best practise. You can monitor only one value or a row, so please be patience with this setting. Differs on the settings you've got.

Witch sensor you've choosen? The V2 Sensor? This one is still on beta. Try it with this statement. If it won't work, please feedback with more information. SELECT * FROM dbo.NODE_STATE WHERE NODE_NAME IN ('CalifornicationToGGG', 'DAGGG_Plus_Receive', 'DAGGG_Plus_Send', 'ALTOBatchInFF','FROGGGM')

Best

Sascha

Created on Dec 10, 2014 4:43:50 PM



Votes:

0

Hi Sascha,

Thanks again for helping me. How is exactly the syntax for a Windows SQL instance within the server name? Is it like? %Servername%\%Database% ?

Cheers R.

Created on Dec 11, 2014 8:59:01 AM



Accepted Answer

Votes:

1

Hey roberto,

I've checked it on one of my servers, I don't write instances at all.It was a misspelling from me. Here is a functional sample for setting up an MSSQLv1 Sensor:

  • SQL-Server specific data
  1. Timeout: 60 (seconds maybe default ;))
  2. Instances: <<clear>> (I don't write it down, for me it works without it)
  3. Port: automatic
  • Database
  1. Database: <<name of your database / maybe: NEDIRIUM_GG6 >>
  2. Authentification: I've used the windows settings, as I said before, this is much easier.
  • Data
  1. SQL-Statement: << your statement (in some cases it could be with brackets around "dbo">>
  2. specific settings for your matching, this differs to every sensor

Sorry for the translation, your titles may diff them in the tutorial. I'm using a german version ;)

Sensor v1 only display's or matches one result, so please don't use a star in your statement. This is how it works for me, the v2 sensor i don't set up till now, because ist still beta and for me not useful till now. If i've more time, I will take a look closer to this sensor.

As additional help, here a sample select statement from Microsoft MSDN library ( http://msdn.microsoft.com/de-de/library/ms177682.aspx ):

SELECT p.FirstName, p.LastName, e.JobTitle FROM Person.Person AS p JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID WHERE e.JobTitle IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant');

As you can see, you are on the right way. Please try it this way. And If you don't use the v1 sensor, so please share this information.

Thanks an Best

Sascha

Created on Dec 11, 2014 10:03:16 AM



Votes:

0

Hi Sascha,

Another query I've tested runs fine. So it must be something within the Syntax or Sensor. How am I able to tell what version of MS SQL Server sensor I'm using?

Cheers R.

Created on Dec 11, 2014 11:15:50 AM



Votes:

1

Hey roberto,

that's fine, maybe at least a problem with your query. In sensor v1 you can paste your query inside PRTG Interface, in v2 you have to build the query inside an ".sql" file.

Concentrate on your query, if another one works fine, than it must be an problem with the syntax of your statement. Please think about, the v1 only display's and shows 1 value in a field.

Install the MSSQL management system on your local computer and try to run the query from there. Maybe it could be still an problem of your rights. This sounds crazy, but i had an similar issue far away.

What's about the running query, is this similar to the one what doesn't work? Or is this a complete different table and database?

Best

Sascha

Created on Dec 11, 2014 12:49:05 PM



Votes:

1

Sascha,

After optimizing the SQL query we've got things runnning now. We also did an upgrade to the latest Paessler version, pherhaps that has also something to do with it. Anyway a lot of time further but also gained a lot of knowledge :)

Cheers R.

Created on Dec 11, 2014 3:17:49 PM




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.