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


Cannot convert between unicode and non-unicode string data types


Cannot convert between unicode and non-unicode string data types

Author
Message
hise0001
hise0001
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9
Alright... I've beat my head against a wall on this issue at different times....

Let me just start with Yes you do have to have a data conversion task before loading into SQL Server. My specific situation is the source is an Oracle 11g server and the data is stored as varchar2. I have setup my sql server 2008 R2 table where the columns are nvarchar2 (unicode).

I have a data conversion task to convert the oracle columns to Unicode (WSTR_STR or something like that... I can't access my server to verify exactly how the converstion task references that).

The Oracle source is using the Oracle OLDB connector and the SQL Server Destination is using the SQL Server OLEDB connector.

I'm using the Oracle OLEDB connector because I have to build a select statement variable (to substitute in dynamic criteria).

All of this has been working fine until I promoted to a production project file. (ie a manual effort to of starting a new project file and loading it with the packages, etc of the currently working project and then updating the Data Sources to point to the production database (found on the same server)) .... getting the message unable to convert between unicode and non-unicode.

.... So in the new "production" project file, I changed the Oracle OLEDB connector to an Oracle.Net for OLEDB connector which required changes is how a built the dynamic query to be passed to Oracle..... This fixed the unable to convert unicode and non-unicode errors.

.... I came across this thread because of searching on the "unable to convert unicode and non-unicode" error. A couple of posts earlier than this one, someone mentioned that this maybe a 32-bit vs 64-bit issue.... I'm unable to test and confirm at this time; but, I bet that there is merit to that state has my Dev project is set to 32-bit and when creating the Prod project file, I forgot to set it to 32-bit (Solution Explorer->RightClick on Project->Properties->Debugging->Run64BitRuntime = False)

So... In short, if you're having problems with Unicode, Non-Unicode conversion and you are already using a Data Conversion task, the fix is either....

1. Use the Oracle.Net connector for your source
or
2. Set your project to run the 32bit Runtime.
rtagle 63214
rtagle 63214
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 2
Hello, so my issue was similar in that I was trying to bring over Oracle 11g data into MS SQL. I have been connecting to other Oracle instances in this environment, but this was the first time connecting to this new source and was getting the message: Cannot convert between unicode and non-unicode string data types.
When I used this provider: OraOLEDB.Oracle.1 (AKA Oracle Provider for OLE DB), the normal Oracle varchar2 columns were being shown as DT_WSTR.
When I switched to use the Microsoft OLE DB provider for Oracle (MSDAORA.1), the source column stays as DT_STR so no issue between Unicode and Non-Unicode. Hope this helps others.
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