Click here to monitor SSC
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 (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

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