Every night we run an import from an IBM Informix DB to our server via a Linked Server. A few times a week I'll get a notification that the procedure encountered an error during the load.
Error Number: 8114
Error Message: Error converting data type DBTYPE_DBDATE to date.
I didn't build this code but it isn't too complex to begin with, here is an example:
INSERT INTO [dbo].[Data_Table]
(ID, Date1, Date2)
SELECT ID, Date1, Date2
FROM OPENQUERY(INFORMIX, 'SELECT ID, Date1, Date2 FROM informix.Data_Table')
SET @Error = ERROR_NUMBER()
SET @Log = @Log + left(ERROR_MESSAGE(),128)
So the error message seems pretty straightforward. I figure there is some weird date format that is allowed for "DBTYPE_DBDATE" that SQL doesn't like. On the table in question there is only two columns that are DATE data types so it shouldn't be too hard... I do a couple test loads to a different test table to see if I can duplicate the issue but I can't. I load the columns in another test table as VARCHAR in case the values are not coming in at all in hopes I can actually see an invalid date format. Unfortunately, the dates looke fine when I inspected them after the load. So then I wrap the date columns with a TRY_CAST in the select statement but I still get the error message. Then I check the ODBC driver in case its not using the right one but it seems to be fine. There also doesn't seem to be any advanced options with the driver that migth help.
As far as I can tell the data completely loads into table. At least the bulk of it makes into the destination table. If it's only a couple of offending rows that don't make it in, I can't quite tell. The source table is very active with new records coming all the time. So it's difficult to compare the final row count with the source table but it they are relatively close by the time I get in to check.
Maybe it's a false positive but it's hard to tell. But either way it would be nice to resolve the issue. Any ideas, on what else I can try?
SELECT quote FROM brain WHERE original = 1
0 rows returned