Sensor Microsoft SQL v2 return error: [System.String] value can not be parsed. Please keep in mind

Hi there,

Please make sure that the query always returns a valid value. In your case it returns nothing, therefore PRTG can't parse the value.

Best regards.

Hi Dariusz,

here is full log where are renamed information about my DB. From this log is visible that was received data from 2 Columns and 5 Rows

27.02.17 16:33:40: Impersonating 'aaa\bbb'
27.02.17 16:33:40: Connections String: Data Source=xxx\yyy,1437;Initial Catalog=nnn;Integrated Security=True;Connect Timeout=60
27.02.17 16:33:40: Opening Connection to MSSQL Server 'xxx\yyy'
27.02.17 16:33:40: Running Command 'SELECT 	 	hhh, 	bbb 	FROM ccc WHERE 	eee  > dateadd(hh,-3,getdate()) AND 	fff  < getdate()  ORDER BY fff DESC'
27.02.17 16:33:40: Received Data Table with 2 Columns and 5 Rows
27.02.17 16:33:40: Closing Connection to MSSQL Server 'xxx\yyy'
27.02.17 16:33:40: Execution Time: return 62.5054 [Double]
27.02.17 16:33:40: Query Execution Time: return 0 [Double]
27.02.17 16:33:40: Affected Rows: return 5 [Int64]
27.02.17 16:33:40: Id 3: String found(""). Trying to parse as Int64, Double, TimeSpan or DateTime. 
27.02.17 16:33:40: Id 3: "" [System.String] value can not be parsed. Please keep in mind that any date or time formats need to use the "EN-US" format.

Best regards

Ivos

What actual PRTG version are you running? Could you post the settings of the sensor (How to embed pictures)?

Version is PRTG Network Monitor 17.1.28.1032

Could you run the following EXE, add all parameters including the query: C:\Program Files (x86)\PRTG Network Monitor\Sensor System\SQLv2.exe ...and post the results?

Do you mean run follow: C:\Program Files (x86)\PRTG Network Monitor\Sensor System\SQLv2.exe xxx.sql >>C:\temp\output.txt ?

No, simply double-click it, a gui will open and you can fill in the data :)

This is result. title_ad is row name in table. Other in result names was changed (it are not public data).

01.03.17 16:57:37: Impersonating 'vvv\uuu' 01.03.17 16:57:37: Connections String: Data Source='xxx\yyy';Initial Catalog=ttt;Integrated Security=True 01.03.17 16:57:37: Opening Connection to MSSQL Server 'xxx\yyy' 01.03.17 16:57:37: Running Command 'SELECT

xxx._name, yyy_log.errors FROM yyy_log WHERE yyy.begin_datetime > dateadd(hh,-3,getdate()) AND yyy.begin_datetime < getdate() ORDER BY yyy.begin_datetime DESC' 01.03.17 16:57:37: Received Data Table with 2 Columns and 5 Rows 01.03.17 16:57:37: Closing Connection to MSSQL Server 'xxx\yyy' 01.03.17 16:57:37: title_ad: String found(""). Trying to parse as Int64, Double, TimeSpan or DateTime. 01.03.17 16:57:37: title_ad: "" [System.String] value can not be parsed. Please keep in mind that any date or time formats need to use the "EN-US" format.

Could you post a screenshot of that? And from an actual SQL client that executes the query?

Original is query run from SQL Server 2014 Management Studio but for this problém is exit is from C:\Program Files (x86)\PRTG Network Monitor\Sensor System\SQLv2.exe or from PRTG sensor Microsoft SQL v2

I also need a screenshot of the actual result from the management studio, as stated in my previous post :)

Screenshot wast sent yesterday but was not uploaded to this site

I didn't publish it since you censored the columns in the original post, hence I considered them confidential :) Could you try to cast the actual values (https://msdn.microsoft.com/de-de/library/ms187928.aspx) as integers? Not sure why they're stored as strings within the database.

Here's another example.

I've got the same problem, does the sensor allow only numerical value? I've got a string result cause it's the real value I need to know, isn't possibile to use it?

Hi Andrea,

In order to evaluate strings, please check the following article:
How can I monitor strings from an SQL database and show a sensor status depending on it?

Kind regards,
Stephan Linke, Tech Support Team

For what it's worth: In our case SP_DATABASES_DATABASE_SIZE.sql which contains the command sp_databases, was addressing a query that was specified to max out at a DB size of 2TB.

When that was exceeded, a value of NULL was returned. As that is not a figure, PRTG threw this error: [System.String] value can not be parsed. Please keep in mind that any date or time formats need to use the "EN-US" format.

See limitation set to size in ddl of the stored procedure:

create procedure sys.sp_databases as set nocount on

select DATABASE_NAME = db_name(s_mf.database_id), DATABASE_SIZE = convert(int, case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int... when sum(convert(bigint,s_mf.size)) >= 268435456 then null else sum(convert(bigint,s_mf.size))*8 -- Convert from 8192 byte pages to Kb end), REMARKS = convert(varchar(254),null) from sys.master_files s_mf where s_mf.state = 0 and -- ONLINE has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access group by s_mf.database_id order by 1

Create a new version, e.g. sp_databasesV2, where that limitation is removed and put that in the file SP_DATABASES_DATABASE_SIZE.sql instead.