Skip to Content
avatar image
Former Member

Invalidcharacter value for cast specification

Hello All,

We are migrating our legacy data from Oracle to SQL SERVER 2016.

SAP BODS SP4 and SQL SERVER 2016 are softare version.

we are pulling data from oracle through ODBC connection and loading. There are few column in SQL SERVER 2016 (Target) with datatype as datetime.

So i need to populate the start date and end-time in those target colums. I am passing values to this colum through global variables. But i am getting the error..

1482014640DBS-0704012016-12-25 3:47:44 PMODBC data source <PAA-KAAVTSQL1> error message for operation <SQLExecute>: <[Microsoft][SQL Server Native Client 11.0]Invalidcharacter value for cast specification>.

$GV_STARTDATE = sysdate( ); (I am using this fucntion in Script and calling this GV in Cloum mapping)

am unable to trace why the job is failing, or is DataServices SP4 not compitable with SQL SERVER 2016..

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Dec 26, 2016 at 09:23 AM

    Hi Vinay,

    Please check the data type of the variable $GV_STARTDATE. If the datatype is "date" change it to "datetime".

    and also use $GV_STARTDATE=to_date(sysdate(),'yyyymmdd hh24:mi:ss'). you can this format according to your requirement.

    please check if this can help you.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 26, 2016 at 12:20 PM

    Hi Ravi,

    $GV_STARTDATE datatype is datetime only.when i print this variable i can able to see date and time but when i

    use this GV in coulum i am getting the above error.

    Regards

    Vinay

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 27, 2016 at 08:48 AM

    MS SQL Server 2016 is supported starting DS 4.2 SP8.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 28, 2016 at 12:53 AM

    I tried in different ways.

    1) Declaring sysdate() to column in Query Transform

    2)by declaring sysdate() and $GV_STARTDATE=to_date(sysdate(),'yyyymmdd hh24:mi:ss') to global variable

    in script, tried both the methods

    3) sql('DS_NEW','SELECT GETDATE() AS CurrentDateTime') in script

    with the print statement i can able to see date and time, in debugging mode can see data is passing through QT but failing to insert in Target( SQL SERVER 2016)..

    I am getting the same error...Is there any other way....

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 28, 2016 at 11:22 AM

    Upgrade DS to 4.2 SP8. Or downgrade to MS SQL Server 2012. Then try again.

    It makes no sense to try and run with incompatible software.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 29, 2016 at 03:06 AM

    we are trying to use sql server 2012.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 29, 2016 at 09:52 PM

    HI,

    I tried loading to SQL SERVER 2012 , but still facing the same issue.

    I have tried other option as well

    1) If i tried to load data from ORACLE( Source) to flat-file, I am facing Data Services Dump Error.

    914817392RUN-0504062016-12-29 4:48:57 PMData flow <BG_FAC_NNNNN_DF> received a bad system message. Message text from the child process is 914817392RUN-0504062016-12-29 4:48:57 PM<Oracle========================================================== 914817392RUN-0504062016-12-29 4:48:57 PMCollect the following and send to Customer Support: 914817392RUN-0504062016-12-29 4:48:57 PM1. Log files(error_*, monitor_*, trace_*) associated with this failed job. 914817392RUN-0504062016-12-29 4:48:57 PM2. Exported ATL file of this failed job. 914817392RUN-0504062016-12-29 4:48:57 PM3. DDL statements of tables referenced in this failed job. 914817392RUN-0504062016-12-29 4:48:57 PM4. Data to populate the tables referenced in the failed job. If not possible, get the last few rows (or sample of them) when 914817392RUN-0504062016-12-29 4:48:57 PMthe job failed. 914817392RUN-0504062016-12-29 4:48:57 PM5. Core dump, if any, generated from this failed job. 914817392RUN-0504062016-12-29 4:48:57 PM==========================================================>. The process executing data flow <BG_FAC_NNNNN_DF> has

    2) If i manually copy data from Source system to Flat-file and Load data from Flat-file to SQL SERVER , I was successfully load data.

    3) If i try to load data from Oracle to SQL server , i am facing with

    SQL1> error message for operation <SQLExecute>: <[Microsoft][SQL Server Native Client 11.0]Invalid character value for cast specification>.


    I am using ODBC connection while connecting to Oracle(Source)

    Am really having very Hard time on this issue, Can anyone help me on this

    Add comment
    10|10000 characters needed characters exceeded