DBA - PerfMon Counter Collection job fails with conversion to money error about tigertoolbox HOT 5 CLOSED

I got the same error on one job execution.

from tigertoolbox.

The following fixed it for me:
This is due to CONVERT(money,ct2.[output]) in [dbo].[spGetPerfCountersFromPowerShell].
Replacing this by CONVERT(varchar(20),ct2.[output]) seems to do the job.

from tigertoolbox.

Hi all,
I've made a different change to the sproc to avoid subsequent conversion errors in the additional Reports.
So I'll work now with
SELECT REPLACE(REPLACE(REPLACE(ct.[output],'\'+@@ServerName+'',''),' :',''),'sqlserver:','')[CounterName] , convert(money,replace(ct2.output,',','.')) [CounterValue], GETDATE() [DateSampled]

from tigertoolbox.

I have merged @gkieffer83 proposed change in the solution which should avoid this issue in future.

from tigertoolbox.

Hello savjani,
I am not sure if converting to varchar is the final solution. You'll probably save unwanted values in the dba_local db. Sometimes you'll get a value with a negative Exponent back from the Performance Counters.
Then you'll have some entries with e-5 in it. This will definitely lead to new problems in the reports.

So I stick to the solution to replace a comma with a dot. Sometimes the Collector Job still Fails with Cannot convert a char value to money. The char value has incorrect Syntax but I do not have faulty values in the dba_local.

I already ran into repprting problems when I just used the convert to varchar method.
https://dirkhondong.wordpress.com/2017/04/26/sql-server-baseline-reports-perfmon-counter-collection-job-schlgt-fehl/

from tigertoolbox.