none
Data transfer using ODBC driver V 17 for non-English characters gets stored as ??? in SQL Server 2017 RRS feed

  • Question

  • Hi,

    Recently we have migrated our SQL database from SQL Server 2008 R2 to SQL 2017. This database was getting populated from Lotus Notes through LEI jobs (Lotus Extractor Integrator IBM tool).

    Now when we moved to SQL 2017 and performed the data population through LEI jobs, we found that some non-English characters are getting transferred as ??? instead of respective language text.

    We checked by lowering the version of ODBC driver to 13.1 but didnt help. Also from SQL schema perspective nothing much to do. Hence please provide some input on this.

    Regards

    Sanjay Nipane


    Sanjay Nipane

    Friday, June 28, 2019 7:08 AM

All replies

  • Hi Sanjay Nipane,

     

    >>Now when we moved to SQL 2017 and performed the data population through LEI jobs, we found that some non-English characters are getting transferred as ??? instead of respective language text.

     

    According to your description,  I think this is a problem caused by changes in the database collation. Would you please use the statement below to check if the collation on sql server 2017 is the same as sql server 2008 r2:  

    SELECT name, collation_name FROM sys.databases;

     

    Did you use backup-restore to do migration from sql server 2008 r2 to sql server 2017?

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 1, 2019 6:03 AM
  • Hi,

    Collation using query provided by you, returning as 'SQL_Latin1_General_CP1_CI_AS' for both databases.

    For generating SQL Database in 2017, we created it using SQL schema script which we generated from SQL 2008 R2 database since we wanted to have it empty and get it populated through LEI jobs.


    Sanjay Nipane

    Monday, July 1, 2019 8:07 AM
  • Would you please confirm that the collation is consistent with the databases on sql server 2008 r2?

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 1, 2019 8:17 AM
  • Yes it is consistent.

    Sanjay Nipane

    Monday, July 1, 2019 8:21 AM
  • Whether we use ODBC 17 or SQL Driver from LEI Jobs hosted on Azure VM, we get ??? only.

    Sanjay Nipane

    Monday, July 1, 2019 9:04 AM
  • Hi,

    Using MSOLEDB Driver helped to solve this problem. Refer link https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-2017


    Sanjay Nipane

    Wednesday, July 3, 2019 7:33 AM