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

Document ID : KB000008086
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

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

non - working select.PNG

 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.

non - working select2.PNG

Environment:
Any sqlserver probe version
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.

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.

working select.PNG

Additional Information: