SAP DATS column containing the value "a.b.c.d" ??? (Problem when SAP ADO is used ..)
While going through the data stored in one of the tables in SAP, we saw a value "a.b.c.d" stored in a DATS column! How could that be? A DATS column is supposed to store a Date value, right?
Well, it seems that SAP does not enforce any validation on the value being stored in a DATS column (other than the simplistic validation done by the SAP GUI). Therefore, any random junk can be stored in a DATS column. This might lead to a problem using the SAP ADO Provider ... here's how:
You use the SAP ADO Provider to select data from a table T, (having a DATS column), in which one row contains junk in this DATS column. At runtime, when you execute:
SELECT * FROM T
The ADO Provider at runtime, will try converting the data returned from SAP to a DateTime value. This, as can be expected, will cause an error/exception to be thrown.
Luckily, there is a workaround:
SELECT * FROM T OPTION 'DisableDataValidation'
When the above query is used, the ADO Provider does not attempt to convert values in DATS and TIMS columns to a DateTime object, but instead exposes it as a String.