SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
chalamsbi
chalamsbi
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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.
chalamsbi
chalamsbi
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 46
Old and New database table structure is same.
Client is Oracle 11g.

No derived columns.
But there are CLOB columns.
chalamsbi
chalamsbi
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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
Jo Pattyn
Jo Pattyn
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7147 Visits: 10013
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?
chalamsbi
chalamsbi
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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
chalamsbi
chalamsbi
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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?
Jo Pattyn
Jo Pattyn
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7147 Visits: 10013
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.
chalamsbi
chalamsbi
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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.
Jo Pattyn
Jo Pattyn
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7147 Visits: 10013
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
chalamsbi
chalamsbi
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search