October 31, 2018 at 7:10 am
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:
BEGIN TRY
INSERT INTO [dbo].[Data_Table]
(ID, Date1, Date2)
SELECT ID, Date1, Date2
FROM OPENQUERY(INFORMIX, 'SELECT ID, Date1, Date2 FROM informix.Data_Table')
END TRY
BEGIN CATCH
SET @Error = ERROR_NUMBER()
SET @Log = @Log + left(ERROR_MESSAGE(),128)
END CATCH
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?
October 31, 2018 at 8:44 am
Can't really help here but have to ask, have you considered staging the data in a separate table first then importing the data from there? If there is a problem you have the static data from the data pull from Informix to check and not have to worry about the rapidly changing data on the source system.
October 31, 2018 at 8:52 am
Is the Informix DB linked server a live data source? I get this issue sometimes when extracting data from a live source system, and re-running this process usually solved it.
I also changed the datatype to be DATETIME2 which has helped.
Thanks
Mark
October 31, 2018 at 9:13 am
Lynn Pettis - Wednesday, October 31, 2018 8:44 AMCan't really help here but have to ask, have you considered staging the data in a separate table first then importing the data from there? If there is a problem you have the static data from the data pull from Informix to check and not have to worry about the rapidly changing data on the source system.
I'd love to but the DB is owned and managed by a 3rd party. Access to the data so we can load into our warehouse is given to a us as a courtesy.
October 31, 2018 at 9:21 am
mark.humphreys - Wednesday, October 31, 2018 8:52 AMIs the Informix DB linked server a live data source? I get this issue sometimes when extracting data from a live source system, and re-running this process usually solved it.
I also changed the datatype to be DATETIME2 which has helped.
Thanks
Mark
Yes, I believe it a live data source but I can email the vendor and confirm. With that in mind I wonder if the use of a HINT in the OPENQUERY statement might be helpful but I'm not familiar at alll with Informix. There is also an option to change the default isolation leveI at the driver.
I was actually considering changing the data type to a DATETIME column as a possible next move. However, I'm trying to think of any scenarios where that might cause me issues with any reports that currently use the table.
October 31, 2018 at 9:29 am
I had to make sure all columns used later in my process matched the DATETIME2 datatype.
The datasource we query is live and I think there are times when the records are not fully written when the query hits the dirty record. As like you when I query the data to check for issues I never find any. And then when I run the same query again it works.
Thanks
Mark
October 31, 2018 at 9:35 am
Y.B. - Wednesday, October 31, 2018 9:13 AMLynn Pettis - Wednesday, October 31, 2018 8:44 AMCan't really help here but have to ask, have you considered staging the data in a separate table first then importing the data from there? If there is a problem you have the static data from the data pull from Informix to check and not have to worry about the rapidly changing data on the source system.I'd love to but the DB is owned and managed by a 3rd party. Access to the data so we can load into our warehouse is given to a us as a courtesy.
Why would that keep you from staging the data prior to loading it to your data warehouse?
October 31, 2018 at 9:58 am
Lynn Pettis - Wednesday, October 31, 2018 9:35 AMY.B. - Wednesday, October 31, 2018 9:13 AMLynn Pettis - Wednesday, October 31, 2018 8:44 AMCan't really help here but have to ask, have you considered staging the data in a separate table first then importing the data from there? If there is a problem you have the static data from the data pull from Informix to check and not have to worry about the rapidly changing data on the source system.I'd love to but the DB is owned and managed by a 3rd party. Access to the data so we can load into our warehouse is given to a us as a courtesy.
Why would that keep you from staging the data prior to loading it to your data warehouse?
Because the staging table would likely still encounter the same issue. SQL Agent would now complain during the load to Staging rather than the warehouse...and I'd still be left wondering if all the data made it through. Unless I'm misundertanding what you are saying...
EDIT: That being said it would allow me more freedom to mess around with the data types during the load to staging. So I'm starting to see what you mean. I might actually run a parallel job and do some more testing. Thanks for the tip.
October 31, 2018 at 3:05 pm
When you changed to datetime2 did you use datetime2(7)? That seems to have the same valid range at informix date data type.
November 1, 2018 at 3:24 am
Joe Torre - Wednesday, October 31, 2018 3:05 PMWhen you changed to datetime2 did you use datetime2(7)? That seems to have the same valid range at informix date data type.
Hi Joe, That is correct. And this seems to have solved most of the issues I had. However I still get the issue periodically and I think this is down to the fact I query a live data source and sometimes I hit a dirty record.
November 1, 2018 at 9:01 am
I'm going with converting the dates at the source and see if that buys me anything.
SELECT ID, somedate
FROM OPENQUERY(INFORMIX, 'SELECT ID, TO_CHAR(somedate, "%Y-%m-%d") somedate FROM informix.Data_Table')
Only time will tell since the errors were difficult to reproduce to begin with. At least the output still comes into SQL as I expect it too. I'll post back with the results in a week or two...less if it happens again sooner.
November 14, 2018 at 9:08 am
It's been just about two weeks and still haven't had any issues since I made the changes mentioned in my previous post. This job was consistently failing at least a couple times a week prior. It may not be completely definative but it does seem like this did in fact fix the issue. I figure there has to be some issue with the ODBC driver that is doing some funky implicit conversion but I can't be 100% sure. Maybe my solution will work for someone else. I'd love to hear back from anyone if it does indeed help them.
Cheers,
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply