May 14, 2009 at 4:42 pm
I am trying to import data from Oracle to SQL Server. In the Oracle table I'm pulling from, it has a columns defined as a LONG data type. The column in my destination table is defined as a text data type. Whether I use a linked server query, SSIS or the import wizard, I can only retrieve about 100 characters from that column. I know there is more data than that, because I can see it when I'm connected directly to Oracle.
Can anyone please help me figure out how to import the full contents of this column from Oracle into SQL Server?
Thanks!
May 14, 2009 at 6:34 pm
The Oracle ODBC driver turns off the retrieval of longs by default.
Turn "Force Retrieval of Longs" to on.
SQL = Scarcely Qualifies as a Language
May 15, 2009 at 8:05 am
Will you please tell me how to configure that? My experience with Oracle is extremely limited.
I've tried going into the ODBC Administrator (where you can create DSNs and such), but I haven't found that option anywhere. I've tried creating DSNs with both the "Microsoft ODBC driver for Oracle" and the "Oracle in OraClient10g home" driver, but haven't seen an option to force retrieval of longs anywhere.
Thanks!
May 15, 2009 at 12:15 pm
This is not an Oracle issue, this is a Windows issue.
Open ODBC Data Source Administrator
Select the offending Data Source
Click Configure
Check "Force Retrieval of Long Options" option.
Click OK
Click OK
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 15, 2009 at 2:12 pm
Please allow me to clarify my original posting.
In SQL Server 2005, I have a linked server set up to access information from an Oracle database. There is no DSN set up for my connection; I'm using the TNSNames entry for the connection to Oracle. When I do a "Select" from the linked server tables, the field defined as "LONG" will only return a maximum of 100 characters.
The workaround I've implemented so far is to create a DSN (via ODBC Administrator of course) and link the tables to an Access 2003 database. I can then import the data from Access into SQL Server and get all of the data.
**When I configured the DSN, I did not get any options regarding the Long data type. I've tried using both of my available Oracle drivers and neither allows that option.
Thanks.
May 15, 2009 at 3:09 pm
well... original post said nothing about a tnsnames based connection then everybody started talking about ODBC then you asked where to set some option and at that time I answered the question.
Having said that, if you are getting into the Oracle database thru a tns connection in a way Oracle recognizes it as a SQLPlus session just use the set command, like:
set long 32767
select your_long_column from your_table;
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 1, 2009 at 2:13 pm
A coworker friend helped me search on this, and finally found the answer, explained here:
It turns out this is an issue with the 64-bit Oracle client. A simple change to the server's registry and then a reboot will fix the problem. We all may have come across the solution much more quickly had I remembered to mention that this was on a 64-bit installation... oops! :w00t:
Thanks everyone for your suggestions! I'm now able to get the data through a linked server, which is my preferred method for remote data access.
Thanks!
Desi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply