LINKED SERVER: COLLATION / ENCODING Problem, PLEASE HELP!
Posted on 2003-11-23
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)