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
[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE CAST([BO_ID] as DECIMAL(31,0)) END
or
[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE Null END
change your script to
INSERT INTO OPENQUERY (
ZMMS_ORA
'SELECT
"COMPANYID",
"BO_ID",
"AS CM",
"AS RSM",
"AS SE",
"TS CM",
"TS RAE",
"TS RSM",
"TS SE",
"BBONE AM",
"ASM",
"WCT ASM",
"LS TSM",
"WCT TSM",
"TS TSM",
"AS PM",
"AS DEV",
"TS PM",
"TS DEV",
"TS TECH",
"BB EXEC",
"CHM",
"ANGEL Client Manager (CM)",
"ANGEL Engagement Team",
"BbOne Client Manager (CM)",
"Blackboard Executive",
"CE/Vista Hosting Mgr (ASM)",
"CE/Vista Tech Support (TSM)",
"Complex Hosting Manager (CHM)",
"Learn Client Manager (CM)",
"Learn Hosting Mgr (ASM)",
"Learn Reg Sales Mgr (RSM)",
"Learn Sales Engineer (SE)",
"Learn Services Developer",
"Learn Services ICM Mgr",
"Learn Services Project Manager",
"Learn Tech Support (TSM)",
"Transact Client Manager (CM)",
"Transact Project Manager",
"Transact Reg Acct Exec (RAE)",
"Transact Reg Sales Mgr (RSM)",
"Transact Sales Engineer (SE)",
"Transact Services Technician",
"Transact Tech Support (TSM)",
"Xythos Tech Support (TSM)" FROM ZMMSUSER.PSRPT_TEAM_ROLES_PSFT'
)
SELECT "COMPANYID"
,[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE CAST([BO_ID] as DECIMAL(31,0)) END
,"AS CM"
,"AS RSM"
,"AS SE"
,"TS CM"
,"TS RAE"
,"TS RSM"
,"TS SE"
,"BBONE AM"
,"ASM"
,"WCT ASM"
,"LS TSM"
,"WCT TSM"
,"TS TSM"
,"AS PM"
,"AS DEV"
,"TS PM"
,"TS DEV"
,"TS TECH"
,"BB EXEC"
,"CHM"
,"ANGEL Client Manager (CM)"
,"ANGEL Engagement Team"
,"BbOne Client Manager (CM)"
,"Blackboard Executive"
,"CE/Vista Hosting Mgr (ASM)"
,"CE/Vista Tech Support (TSM)"
,"Complex Hosting Manager (CHM)"
,"Learn Client Manager (CM)"
,"Learn Hosting Mgr (ASM)"
,"Learn Reg Sales Mgr (RSM)"
,"Learn Sales Engineer (SE)"
,"Learn Services Developer"
,"Learn Services ICM Mgr"
,"Learn Services Project Manager"
,"Learn Tech Support (TSM)"
,"Transact Client Manager (CM)"
,"Transact Project Manager"
,"Transact Reg Acct Exec (RAE)"
,"Transact Reg Sales Mgr (RSM)"
,"Transact Sales Engineer (SE)"
,"Transact Services Technician"
,"Transact Tech Support (TSM)"
,"Xythos Tech Support (TSM)"
FROM CRMRPT..B_RPT_B_TEAM_ROLE;
Best Answer
I would try installing the Oracle Data Access Components (ODAC).
You will be able to download them from this link. http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html