I have a rather weird problem with exporting a bunch of tables from SQL Server 2005 to Oracle database. I have SQL Agent job that deletes Oracle tables, recreates them and then exports the data using linked server
and OPENQUERY
. The entire script is here.
My problem is, the job fails while exporting data at the last table PSRPT_TEAM_ROLES_PSFT
. The error message from the log files says the job failed due to a data conversion error: Msg 8114, Sev 16, State 5, Line 4 : Error converting data type varchar to numeric. [SQLSTATE 42000]
. I have identified the column that is likely causing this error, BO_ID
which is DECIMAL(31,0)
at source and exported as NUMERIC(31,0)
to destination. When I alter the destination column to NVARCHAR2
and export, the job doesn't fail but the Oracle guys told me that it significantly increased the size of the table. The weird part is, when I run the export query manually, even when the datatype NUMERIC(31,0)
at destination I see no error. So I guess my question is
- Am I missing something obvious?
- Why does this table export fail when run as a SQL agent job and work perfectly when run manually?
Best Answer
I tried your script and recreated your environment completely and i didn't see any error.
Ran perfectly and created all tables and transferred all data successfully
i tried running under a sql agent job and still no error was generated
but i guess you can update your insert script by making sure the column gets converted under all conditions
or
change your script to