January 25, 2011 at 8:52 am
i'm trying to set up a linked server in SQL 2005 to Oracle 9i database. french characters are not translating properly: Example, "Référence" comes across as "RÚfÚrence".
SQL is installed with SQL_Latin1_General_CP850_BIN2 collation.
Oracle 9i is installed with CHARACTER SET WE8ISO8859P1 NATIONAL CHARACTER SET AL16UTF16;.
I'm using the Oracle 10g OLEDB driver to create the linked server in SQL:
EXEC master.dbo.sp_addlinkedserver @server = N'XXX_TEST', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'XXX_TEST', @provstr=N'OraOLEDB.Oracle;Password=guest;Persist Security Info=True;User ID=XXX_GUEST;Data Source=XXX_TEST'
Any ideas?
THANKS IN ADVANCE
January 25, 2011 at 9:24 am
TRy this collation on sql server:SQL_Latin1_General_CP1_CI_AS
January 28, 2011 at 1:45 am
The problem is that this is an SAP db server and it is required to run the SQL_Latin1_General_CP850_BIN collation. So I can't change the server or db collation.
I tried changing the settings in the linked server, set Collation Name = Latin1_General_CI_AS (which BOL says to use for non-SQL OLEDB databases). This produces the correct results in the query (the french characters appear correctly ie "Référence"). However, now joins in SQL based on these imported tables do not work.
SELECT T.NUMMER,T.NAME FROM SAPPH2.LK_T05_BOM_LATEST_VERSION H
INNER JOIN SAPPH2.PMX_TEILESTAMM T
ON H.TEILESTAMM = T.ID
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP850_BIN2" in the equal to operation.
What is happening here?
January 31, 2011 at 8:13 am
FYI
It was recommended to me on another board that I try this:
You get this error, because SQL Server does not know which rules to apply. If the strings are "April" and "APRIL", they are equal under Latin1_General_CI_AS but different under you SAP collation.
The way to resolve is to use a COLLATE clause, for instance:
SELECT T.NUMMER,T.NAME FROM SAPPH2.LK_T05_BOM_LATEST_VERSION H
INNER JOIN SAPPH2.PMX_TEILESTAMM T
ON H.TEILESTAMM = T.ID COLLATE SQL_Latin1_General_CP850_BIN
This forces the collation of the expression.
But strange, the query seems to be working ok without the COLLATE clause today. Nothing changed AFAIK. I don't understand how but it works now.
Does anyone have any web resources for fully understanding collation issues between SQL and other databases with linked server connections? I would much appreciate it!
January 31, 2011 at 9:54 am
you need to cast column.
Your ID column is char?
SELECT T.NUMMER,T.NAME FROM SAPPH2.LK_T05_BOM_LATEST_VERSION H
INNER JOIN SAPPH2.PMX_TEILESTAMM T
ON H.TEILESTAMM = T.ID COLLATE SQL_Latin1_General_CP1250_CI_AS
the "=" & Comparaison between 2 table need to be in the same collation
Exemeple
Select X COLLATE SQL_Latin1_General_CP1250_CI_AS From table1
UNION
Select x FROM table2
Table1 (Collation for x is **BIN)
Table2(Collation for x is ***CP1250**)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply