Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
    
DECIMAL is a numeric data type. Why does the error message say you are converting form varchar? –  miracle173 Nov 5 '12 at 6:31
add comment

1 Answer 1

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;
share|improve this answer
    
The service account has enough permissions on the script and if it was a a permission issue, It should fail the whole script right? But in this case, the error is always the last table and the error message is always the data conversion related one. I still have this issue and I keep running it manually everyday :( –  yogirk Jun 6 '12 at 12:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.