Skip to Content
0

Invalidcharacter value for cast specification

Dec 25, 2016 at 08:56 PM

137

avatar image
Former Member

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..

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

7 Answers

Ravi kiran Pagidi Dec 26, 2016 at 09:23 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 26, 2016 at 12:20 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Dirk Venken
Dec 27, 2016 at 08:48 AM
0

MS SQL Server 2016 is supported starting DS 4.2 SP8.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 28, 2016 at 12:53 AM
0

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....

Share
10 |10000 characters needed characters left characters exceeded
Dirk Venken
Dec 28, 2016 at 11:22 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 29, 2016 at 03:06 AM
0

we are trying to use sql server 2012.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 29, 2016 at 09:52 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded