August 3, 2011 at 3:31 pm
I have set up a linked server from SQL 2005 to Oracle using the Oracle SQL Driver.
I am having 2 problems with it:
1)training invisible spaces - as data is brought into SQL some text fields (not all text fields) have trailing blanks that SQL server cannot see to trim (rtrim does not work). The number of spaces equals the number of letters in the text - so
'ABC' in Oracle becomes 'ABC'
2)random results - my queries don't come back with the same results everytime. The exact same query will come back with 10 records for 10 times and then the 11th time it will bring back 9.
Note that both of these problems apply to all text, not just text with chinese characters.
But it seems that perhaps it might be related to the driver or the SQL collation.
Does anyone have any experience with bringing data from an Oracle database with Chinese characters into a SQL server 2005 database using Linked Server that is collated as Chinese_PRC_CI_AS?
many thanks - Annette
August 3, 2011 at 3:36 pm
You may try and use the 'Microsoft OLE DB Provider for Oracle', it will probably honor the SQL Server collation setting.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
August 3, 2011 at 3:58 pm
Thank you very much for your response. Unfortunately, that is the drive I am using....
Annette
August 3, 2011 at 4:04 pm
The main problem is the provider you mentioned is not in the list of providers while creating the linked server. How to get the 'Microsoft OLE DB Provider for Oracle' ??
August 3, 2011 at 4:18 pm
You have to install Oracle client on the server.
then you can download this driver from Microsoft website:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=741
Annette
August 4, 2011 at 8:40 am
Try using an SSIS task to do the import. It will give you more control over the columns, it could help with your first problem.
Not sure about your second problem, though. How are you querying the Oracle database? Is it a SQL statement using the linked server as the table name or are you using OPENQUERY? Try both ways and see what happens.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply