Change of source databases(9i to 11g) 0xC02020F6 cannot convert between unicode and non-unicode string data types

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Working with Oracle » Change of source databases(9i to 11g)...

11 posts, Page 1 of 2

1 2 »»

Change of source databases(9i to 11g) 0xC02020F6 cannot convert between unicode and non-unicode string data types

Rate Topic

Display Mode

Topic Options

Author

Message

chalamsbi

Posted Tuesday, August 14, 2012 1:49 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 3:20 PM
Points: 19, Visits: 46

Hi, Could someone tell me how to resolve this issue.

I changed my oracle source database from 9i to 11g.

Everything worked fine earlier.
But I get the following error now

Code: 0xC02020F6 Source: Load Audit_Log ViewPoint xxxTable_Namexxx Source [1] Description: Column "xxxColumn_Namexxx" cannot convert between unicode and non-unicode string data types. End Error
***Followed by all columns***

I believe it is the 64bit issue.
I am running the job from SQL Server Agent.

Post #1345003

chalamsbi

Posted Tuesday, August 14, 2012 1:51 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 3:20 PM
Points: 19, Visits: 46

Old and New database table structure is same.
Client is Oracle 11g.

No derived columns.
But there are CLOB columns.

Post #1345008

chalamsbi

Posted Tuesday, August 14, 2012 2:13 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 3:20 PM
Points: 19, Visits: 46

I tried running from command prompt from both 32 bit(Program Files(86) and 64 bit Program Files folders.

Job fails in both

64 bit Error -

Started: 1:02:54 PM
Progress: 2012-08-14 13:02:58.03
Source: Load Audit_Log
Validating: 0% complete
End Progress
Progress: 2012-08-14 13:02:58.05
Source: Load Audit_Log
Validating: 50% complete
End Progress
Error: 2012-08-14 13:02:58.50
Code: 0xC02020F6
Source: Load Table_Name SourceDB Table_Name Source [1]
Description: Column "Column_Name" cannot convert between unicode and non-u
nicode string data types.
End Error
Error: 2012-08-14 13:02:58.52
Code: 0xC02020F6
Source: Load Table_Name SourceDB Table_Name Source [1]
Description: Column "Column Name" cannot convert between unicode and non-unicod
e string data types.
End Error

32 bit Error -

Error code: 0x80004005.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Descrip
tion: "ORA-12154: TNS:could not resolve the connect identifier specified".
End Error
Error: 2012-08-14 13:07:03.76
Code: 0xC020801C
Source: Load Table Name SourceTable Name Source [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAG
ER. The AcquireConnection method call to the connection manager "Source" fai
led with error code 0xC0202009. There may be error messages posted before this
with more information on why the AcquireConnection method call failed.
End Error

Post #1345018

Jo Pattyn

Posted Tuesday, August 14, 2012 4:50 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:28 AM
Points: 1,333, Visits: 4,417

Unlikely that the move to 64-bit triggers cannot convert between non-unicode to unicode.

Did you change the nls_characterset of your oracle database whilst migrating from 9 to 11?
The oracle client on your server is updated to v11?

Post #1345082

chalamsbi

Posted Tuesday, August 14, 2012 5:06 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 3:20 PM
Points: 19, Visits: 46

Hi Jo,

We are not migrating. But the 11g is altogether a new database.
Yes, we have Oracle 11g client installed

Thank you
Chalmsbi

Post #1345087

chalamsbi

Posted Tuesday, August 14, 2012 5:15 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 3:20 PM
Points: 19, Visits: 46

I just checked the NLS_CHARACTERSET for old database(9i) is WE8ISO8859P1 and for new datatabse(11g) is AL32UTF8

Is this causing the issue?

Post #1345088

Jo Pattyn

Posted Tuesday, August 14, 2012 10:00 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:28 AM
Points: 1,333, Visits: 4,417

Almost certain, AL32UTF8 is an unicode characterset. All char/varchar2/... columns can now store unicode characters. You can consider them nchar/nvarchar... by now.
WE8ISO8859P1 is non-unicode.

Because not all characters from AL32UTF8 can be represented in sqlserver varchar columns, the warning/error is thrown.
NLS_CHARACTERSET can't be changed after the oracledatabase is created.

Post #1345124

chalamsbi

Posted Wednesday, August 15, 2012 6:09 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 3:20 PM
Points: 19, Visits: 46

Thank You for the reply Jo.

Yes, there is unicode conversion in the source database. What are the options I have now?

We do not have any plans to change the table structure in Target.

We tried changing the NLS_LANG registry of oracle to AL32UTF8

Changed one of the first tables columns to nvarchar.

But the job fails.

Any suggestions please.

Post #1345616

Jo Pattyn

Posted Thursday, August 16, 2012 12:42 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:28 AM
Points: 1,333, Visits: 4,417

You should not set the NLS_LANG to AL32UTF8 if the client (SQLSERVER varchar) isn't AL32UTF8 aware.
Found a post that fixed this issue in SSIS apparently

Post #1346186

chalamsbi

Posted Monday, September 10, 2012 1:12 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 10, 2013 3:20 PM
Points: 19, Visits: 46

Hi Jo,

The new source character set is in Unicode.

The mappings are done with source DT_STR for source and target external columns.
Do I need to change the code to DT_WSTR for source and Target external columns with unicode conversion transformation?
Or changing the Target SQL Server database character set will resolve the issue?

Thank You.

Post #1356973

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions