locked
Error in Accessing Postgres from SSRS RRS feed

  • Question

  • I am accessing postgres database from SSRS . I am getting the following error from BIDS / SSMS.

    ERROR [22P05] ERROR: character 0xc280 of encoding "UTF8" has no equivalent in "WIN1252";
    Error while executing the query

    The query runs fine from PGAdmin. This seems to be the collation issue . Has anyone found a workaround for this issue ?

    Thanks in Advance.

    Friday, July 13, 2012 3:42 PM

Answers

  • Hi DBA_CONSULTING,

    Please refer to this thread which describes a similar problem:
    http://stackoverflow.com/questions/7167298/rodbc-character-encoding-error-with-postgresql

    As Iterator and Joris Meys mentioned:
    The issue arises because R is trying to convert to a Windows locale that supports UTF8. 0xc280 is a control element ( U+0080 in Unicode) that is causing trouble pretty often when using SQL and the likes. The problem often lies in the conversion chain that invariably happens when you use different applications that use different encoding schemes. Windows has UTF-8 included by now, so it's not strictly a Windows problem. I believe the problem arises before R reads the data in.

    In fact, in the chain the character sequence 0x80 in UNICODE will be mapped to 0xc280 in UTF-8. This is supposed to be a control sequence, and cannot be printed. But chances are big that the 0x80 is in fact not UNICODE, but Windows Latin-1 or Latin-2. In that case, the 0x80 represents the euro sign. That might explain how it ends up in your data. Check if you can find something like that in the data, that would explain something already.

    My guess is that the solution will not lie at the R-end of this workchain, but before that. It will try automatic conversion, but this one is reported to fail in some cases (also for SQL and Oracle btw). Check in which encoding you're working in Postgresql, and try to use any of the latin types. There might be other links involved (a Putty or similar terminal for example). I'm pretty sure all the encodings there are ISO8859-1, which is Latin-1. Somewhere UTF-8 gets thrown in between, and when the 0x80 character gets wrongly mapped to 0xc280, you get trouble.

    So check the encodings in your complete workchain, and make sure that they all match. If they don't, the automatic conversion done between each step is bound to give trouble for some characters.

    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Iric Wen Monday, July 23, 2012 5:35 AM
    Monday, July 16, 2012 8:54 AM

All replies

  • Hi DBA_CONSULTING,

    Please refer to this thread which describes a similar problem:
    http://stackoverflow.com/questions/7167298/rodbc-character-encoding-error-with-postgresql

    As Iterator and Joris Meys mentioned:
    The issue arises because R is trying to convert to a Windows locale that supports UTF8. 0xc280 is a control element ( U+0080 in Unicode) that is causing trouble pretty often when using SQL and the likes. The problem often lies in the conversion chain that invariably happens when you use different applications that use different encoding schemes. Windows has UTF-8 included by now, so it's not strictly a Windows problem. I believe the problem arises before R reads the data in.

    In fact, in the chain the character sequence 0x80 in UNICODE will be mapped to 0xc280 in UTF-8. This is supposed to be a control sequence, and cannot be printed. But chances are big that the 0x80 is in fact not UNICODE, but Windows Latin-1 or Latin-2. In that case, the 0x80 represents the euro sign. That might explain how it ends up in your data. Check if you can find something like that in the data, that would explain something already.

    My guess is that the solution will not lie at the R-end of this workchain, but before that. It will try automatic conversion, but this one is reported to fail in some cases (also for SQL and Oracle btw). Check in which encoding you're working in Postgresql, and try to use any of the latin types. There might be other links involved (a Putty or similar terminal for example). I'm pretty sure all the encodings there are ISO8859-1, which is Latin-1. Somewhere UTF-8 gets thrown in between, and when the 0x80 character gets wrongly mapped to 0xc280, you get trouble.

    So check the encodings in your complete workchain, and make sure that they all match. If they don't, the automatic conversion done between each step is bound to give trouble for some characters.

    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Iric Wen Monday, July 23, 2012 5:35 AM
    Monday, July 16, 2012 8:54 AM
  • I get similar error when connecting to Postgres DB from MS SQL Management client. Tyring to fix the source data is almost impossible in my case.

    My Scenario:

    1. Trying to connect to Postgress using MS SQL Linked Objects via an ODBC System DSN, and see errors such as "ERROR: character 0xc280 of encoding "UTF8" has no equivalent in"WIN1252";
    2. Select statements on some tables work and other throw this error.

    Fix: Use an ODBC driver that supports Unicode. I am using an ODBC driver from PostgreSQL Global Development Group. Go to Configure DSN/Manage DSN and select the Unicode driver.

    Good luck.

    Madhu

     
    Friday, October 4, 2013 8:24 PM