Linked Server and uncode data - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Linked Server and uncode data

Author

Topic

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-09-28 : 12:33:33

Hi,
I added a linked server with these syntax.

EXEC sp_addlinkedserver 
  'OracleLinkedServer', 'Oracle', 
  'MSDAORA', 'OracleServer' 

EXEC sp_addlinkedsrvlogin ' 
  OracleLinkedServer ', false, 
  'SQLuser', 'OracleUser', 
  'OraclePwd'

When I select from a linked server table ,columns with unicode data is shown as questionmark

I tryed to choose deferent colllation settings in linked server
I set the collation comptible to false and remote collation to true then chose different collation name,but it didnt work.
The sql srver collation is SQL_Latin1_General_CP1_CI_AS
I don't know what the oracle collation might be,since I have only access to some views.
How can I solve this problem
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 14:37:57

unless you've some idea on what language data linked server is handling, it would be difficult to determine the sql server column collation.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-09-29 : 16:20:36

What ever the oracle collation is
when I link it to my local sql server whith arabic_CI_AI server collation it works fine
but the remote sql server 2000 has latin... server collation and it brings out the problem.
I dont know If I can change server collation with no risk
because it's a bank database that many client and application use its data
and I think changing it's collation may has some effects on its own data.

Kristen
Test

22859 Posts

Posted - 2011-09-29 : 18:20:37

I explicitly CAST the collation in my SQL statements when referencing a remote LINKED server.

Can you do that (instead of trying to control it as part of the Config. of the linked server)?

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-09-30 : 02:14:51

casting it to nvarchar ? no that dosent work
or say what the column collation is when selecting it? It dosent work either

Kristen
Test

22859 Posts

Posted - 2011-09-30 : 05:56:24

Sorry, didn't mean CAST I meant COLLATE


SELECT Col1, Col2, ...
FROM MyLocalTable AS L
     JOIN MyRemoteServer..MyRemoteOracleDatabase AS O
         ON L.SomeVarchar = O.OtherVarchar COLLATE xxx -- use an appropriate collation

In practice we normally use OPENQUERY to retrieve the data from the remote server into a ##Temp table, and then JOIN it (locally) when we then apply the COLLATE we need.

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-10-01 : 00:42:36

What do you mean by join statement?
which table I should join with? I dont have such table in local server.

quote:
Originally posted by fan2005

quote:
Originally posted by Kristen

Sorry, didn't mean CAST I meant COLLATE

SELECT Col1, Col2, ...
FROM MyLocalTable AS L
JOIN MyRemoteServer..MyRemoteOracleDatabase AS O
ON L.SomeVarchar = O.OtherVarchar COLLATE xxx -- use an appropriate collation

In practice we normally use OPENQUERY to retrieve the data from the remote server into a ##Temp table, and then JOIN it (locally) when we then apply the COLLATE we need.