LINKED SERVER: COLLATION / ENCODING Problem, PLEASE HELP!

I have a linked server to PostgreSQL on my SQL 2K Server.
While trying to retrieve data from the linked server without using any collation I get "?????" instead of data. (it's because the data stored on PostgreSQL is in Hebrew).

If I use something like this:
SELECT myfield COLLATE SQL_EBCDIC273_CP1_CS_AS as myfield FROM OPENQUERY(linked_server, ....)
then I can see returned data.

BUT if I do the same with FOR XML AUTO at the end (i.e. SELECT myfield COLLATE SQL_EBCDIC273_CP1_CS_AS as myfield FROM OPENQUERY(linked_server, ...)  FOR XML AUTO) I continue receiving "?????".

1. Is it possible to set COLLATION GLOBALLY for all returned fields by PostgreSQL?
2. How to retrieve DATA using FOR XML clause (in this specific issue with collation conflicts)

Thanks.
bibariusAsked:
Who is Participating?
 
mironCommented:
do you have any special fonts installed for rendering Hebrew
0
 
mironCommented:
my first turn would be to try something easy, like this

select * from
(SELECT myfield COLLATE SQL_EBCDIC273_CP1_CS_AS as myfield FROM OPENQUERY(linked_server, ...) ) as dt for xml auto

0
 
bibariusAuthor Commented:
Hi Miron!

I tried what you wrote. It looks so:
select fname from (
select fname COLLATE SQL_EBCDIC273_CP1_CS_AS as fname from openquery(secureshop, 'select fname from userdb order by clubid desc limit 10') ds) as ds
for xml auto

.. but same result.... I get "?????" after I use FOR XML AUTO...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mironCommented:
well, it looks like SQL Server provides for setting up collation for a linked server this way

sp_serveroption @server = 'secureshop'
    ,@optname =  'collation name'
    , @optvalue = 'SQL_EBCDIC273_CP1_CS_AS'

I am not sure that it would help with XML AUTO though, one more to go

--cheers
0
 
bibariusAuthor Commented:
I'v already tried this...
It doesn't help...
0
 
mironCommented:
on the other hand, could you try something like this

setup linked server collation to SQL_EBCDIC273_CP1_CS_AS

then on the local SQL Server from windows command prompt execute

bcp "select * from ( secureshop, 'select fname from userdb order by clubid desc limit 10' ) as dt for xml auto" queryout C:\test.xml -c -C 1252  -T

and then try to view the file C:\test.xml in the IE. You will need to edit xml root node if the query returns multiple rows.


--cheers
0
 
mironCommented:
In that post

"
I'v already tried this...
It doesn't help...
"

did you mean it misses to help with XML or it does not help completely. Also, if none of the above helped, could you post the result of this query


select top 1  convert( binary(256), fname ) from  ( select fname COLLATE SQL_EBCDIC273_CP1_CS_AS as fname from openquery(secureshop, 'select fname from userdb order by clubid desc limit 10') ds ) as dt
0
 
bibariusAuthor Commented:
None of the above helped.

The result of your last query is:
0xF9ECE5EEE9000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0
 
bibariusAuthor Commented:
Hi Miron!

I just finished dealing with this problem.
Everything fixed after I rebuilt the server and set other collation.

Thank you for your help (willing to help) :-)
You earned your points :-)
0
 
mironCommented:
Hi bibarius,

            thanks for being generous :)

--cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.