sqlserver: custom checkpoint query pulls back field variables as incorrect data type

book

Article ID: 8086

calendar_today

Updated On:

Products

DX Infrastructure Management NIMSOFT PROBES

Issue/Introduction

The customer has defined a custom checkpoint, and can run the query successfully via the "test query" button.

 However, when checking the message variables that are being pulled back, these show that some of the fields of type INT are brought back as character type  and then these cannot be used for the row identification.

Cause

This can happen if the first row in the table you are querying contains NULL values in the respective field, and then the probe cannot properly identify the data type.

Environment

Any sqlserver probe version

Resolution

To resolve this, you would need to adjust the sql select you are using to pull back the values.  In the example, the customer used a wildcard select to get all fields, such as this:

 

select * from CERTIFICATE_DATA

 

To force the probe to recognize the correct data type, you need to adjust the select as per the example below:

 

select 
CertificateName, 
ExpiryDate, 
CurrentDate, 
ISNULL(DAYS_45, 0 ) as DAYS45 , 
ISNULL(DAYS_30, 0 ) DAYS30, 
ISNULL(DAYS_15, 0 ) DAYS15, 
FILE_NAME 
from CERTIFICATE_DATA

 

This then allowed the probe to pull back the correct data type (numeric) for the INT columns of the table.

Additional Information

For more details on the sqlserver probe, please check the documentation found here:

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/ca-unified-infrastructure-management-probes/GA/alphabetical-probe-articles/sqlserver-sql-server-monitoring/sqlserver-sql-server-monitoring-release-notes.html

 

Attachments

1558714323513000008086_sktwi1f5rjvs16tef.png get_app
1558714321441000008086_sktwi1f5rjvs16tee.png get_app
1558714317614000008086_sktwi1f5rjvs16ted.png get_app