Re: Problem with client_encoding and PsqlODBC driver.

On 03/20/2015 09:28 AM, Kadri Raudsepp wrote:

On Fri, Mar 20, 2015 at 5:57 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

    On 03/20/2015 08:48 AM, Kadri Raudsepp wrote:

        Sorry, didn't see the first question at first - I haven't been
        able to
        find a way to specify encoding with openquery, every attempt has
        resulted in syntax errors.


    Just to be certain you are talking about?:

    https://msdn.microsoft.com/en-__us/library/ms188427.aspx
    <https://msdn.microsoft.com/en-us/library/ms188427.aspx>


Yes, this is what I'm using.



    Some more questions:

    1) Where is your Postgres server and on what OS?


The servers in question, SQL server and Postgres server, are in
different countries, if that's what you meant. Postgres runs on Debian
3.16.7 x86_64. I'm the dba for the postgres database in question, but
not too familiar with MS tools. Data import will be done by MS dba-s in
their location, but they have had even less luck figuring out the
encoding issue.


    2) Do you have pgAdmin(http://www.pgadmin.__org/
    <http://www.pgadmin.org/>) or the psql client installed on the
    Windows machine?
    If so does running a query through them show the correct results?


I did install PgAdmin III in the environment I set up for testing the
solution, which is as close as possible to the live environments we'll
be using. Simply running the query there gives the same garbled result,
  "õ" instead of "õ" etc. Running "set client_encoding to latin1;
select * from table", however, gives the correct result.
I have given the instructions for getting the correct data via PgAdmin
and Import Data to my colleagues, but they are strictly MS people so I'm
yet to see how this works out.
I would prefer if there was minimal amount of trouble on their end,
because we'll be migrating the data while the services running on that
data have been turned off, which means any delay will cause financial
loss. That's why I'm looking to get the driver working.

I do not use MS products much and SQL Server not all, so I am working blind here. That has never stopped me before, so what I could find that might apply:

1)  This post:
https://social.msdn.microsoft.com/forums/sqlserver/en-US/acb718fd-0943-4e1c-95b0-067361157821/how-to-use-openquery-for-utf8-character

2) Led me to:
https://technet.microsoft.com/en-us/library/ms186839%28v=sql.105%29.aspx

3) Led me to:
https://msdn.microsoft.com/en-us/library/ms144260%28v=sql.105%29.aspx

My guess is the answer lies in the above, but that is requires someone more SQL Server savvy then I to unravel.

        Katie



    --
    Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>


Katie

--
Adrian Klaver
adrian.klaver@aklaver.com