Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rhondastrawter
Starting Member
5 Posts |
 Posted - 2011-06-20 : 12:24:08
|
| I am trying to load a SQL Server table from data from an Oracle table. One of my columns from the Oracle table is VARCHAR2(4000). When I attempt to load this table in SQL Server I receive a character conversion error message. The column in SQL Server is defined as text. I have attempted to change the characteristics on the Oracle table from CLOB, LONG, BLOB, LOB with no success. Can you help me with this conversion. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-06-20 : 12:40:11
|
Not ever done this but I believe if you are on sql server 2005 or above then you can use varchar(max) on sql server side to receive oracle varchar2.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
rhondastrawter
Starting Member
5 Posts |
Posted - 2011-06-20 : 13:50:57
|
| That didn't work. Any more suggestions? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-20 : 14:52:19
|
| You shouldn't be using Text data type. See hereIn your case, the SQL Server column should be VARCHAR(4000).That said, conversion from Varchar to Text does work (see here).If you must keep the text data type, you may need to import first to a staging table with VARCHAR(4000) data type.But...you haven';t told us what error message you're getting. You should be able to perform the conversion in the data transformation task. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-20 : 14:55:02
|
| If you have foreign language characters in your VARCHAR2 column, you may need to use NVARCHAR(4000) in SQL Server. |
|
|
|
rhondastrawter
Starting Member
5 Posts |
Posted - 2011-06-20 : 15:37:39
|
| The SQL Server database is a legacy system where I cannot change the characteristics of the column unless I do as suggested and create a staging area in SQL Server write to the staging area and then move the data to the intended table (which is cumbersome). The error message I receive is "Warning(166,11): PLW-07202: bind type would result in conversion away from column type". Thank you. |
|
|
|
|
|
|
Oracle to SQL Server Converstion VARCHAR2(4000)