My company has an application where one of the things that we need to do is pull a bunch of data from an AS/400 database (the product is called Ingenium, if that would help any) and put it into SQL Server. Right now, we have the AS/400 create a bunch of text files with the data we need and then bulk import them into SQL Server in a DTS Package. It runs pretty slow and can hold a lot of stuff up if it every hangs for whatever reason, so we're looking into a way to get it to run faster.

The first thing that I've tried to do is create a linked server to the AS/400 db and pull in the data directly and cut the text files out of the process. That works fine, except when it comes to date fields. For some reason that is inexplicable (and the reasons why it can't be fixed in the AS/400 system are even more inexplicable), sometimes null date values are stored as NULL and sometimes they are stored as '0001-01-01'. When pulling the records over that have dates of '0001-01-01', an error is thrown that says:

Error converting data type DBTYPE_DBTIMESTAMP to datetime.

This appears to be because SQL tries to map the Ingenium DBTYPE_DBTIMESTAMP datatype to a datetime field and having this dummy value in it throws an out-of-range error. Any casting done on the field seems to happen after the conversion to the SQL Server datatype, so that doesn't help. For example:

SELECT POL_ID, CAST(POL_APP_SIGN_DT as varchar(50)) FROM ingenium_dev.NORDEV.IPRD552.TPOL

throws the error, while

SELECT * FROM OPENQUERY(ingenium_dev, 'SELECT POL_ID, CAST(POL_APP_SIGN_DT as varchar(50)) FROM IPRD552.TPOL')

works fine and pulls in the data as a varchar.

I don't want to use the OPENQUERY syntax and was wondering if anyone knew of a way to override SQL Server's automatic datatype conversions and force it to view DBTYPE_DBTIMESTAMP types as varchars instead of as datetimes or to be able to convert the data before it throws the out-of-range exception (CONVERT doesn't work either)?

Anyone have any ideas?