locked
Varchar(max) truncated to char 24 or 32 with native client 11 odbc driver RRS feed

  • Question

  • Hi,

    I had table with columns with text as datatype. Since I changed it to varchar(max), now with odbc native client 11 driver each call through ODBC cuts varchar(max) to 24 or 32 characters. with old odbc driver everything works fine. do you know reason for this one?

    Kind regards.

    Wednesday, July 25, 2018 9:56 AM

All replies

  • How are you doing the ODBC call?

    May be it has something to do with this behavior change

    https://docs.microsoft.com/en-us/sql/relational-databases/native-client/features/odbc-driver-behavior-change-when-handling-character-conversions?view=sql-server-2017


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, July 25, 2018 10:24 AM
  • We use standard MFC.

    Problem occurs also with linked servers using odbc when inserting/updating data in linked server...

    Wednesday, July 25, 2018 10:53 AM
  • I am researching in internet, and this is same mentioned problem, but I do not see resolution...

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/406dfbba-fd74-46e9-b1de-0fa2c8245c6d/sql-linked-server-to-odbc-openquery-data-gets-truncated?forum=sqlexpress 

    Wednesday, July 25, 2018 11:36 AM
  • Hi JaPasst,

    We are currently looking into this issue and will give you an update as soon as possible. Thank you for your understanding and support.

    Best Regards,

    Teige


    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.

    Thursday, July 26, 2018 6:19 AM
  • Hi JaPasst,

    Could you please tell us which version of ODBC driver did you use? Based on my test, native client 11 driver can can insert data length more than 40. We suggest you reviewing the application code refer the  Microsoft official  document.

    As the document said as below : “ODBC functions, such as SQLGetData, SQLBindCol, SQLBindParameter, may return (-4) SQL_NO_TOTAL as the length/indicator parameter when using the SQL Server 2012 driver when prior versions of the SQL Server ODBC driver returned a length value (which may not have been correct in all cases).”

    Best Regards,
    Teige


    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.

    Thursday, August 2, 2018 9:17 AM
  • Hi Teige,

    Thank you very much for your answer. I am getting closer to discover possible problem location.

    Application is not, since we created now small app, which uses only MFC, without any custom code, and problem exists. We found that when we put DB on collation with same codepage 1252 as Windows Server 2012, we do not have truncation. Also when DB stays with codepage 850 and during setting ODBC, we unchecked "Perform translation for character data", data are not truncated. My feeling is that library for ODBC translating somehow cuts.

    Just to mention that we have this version of App on more different servers (different OS), and problem occurs only on Windows Server 2012 R2, 64bit.

    Also please notice, that we are using SysWOW64 ODBC, 

    ODBC drivers that we use:

    SQL Server Native Client 11.0 v2011.110.7001.00 (from 8/15/2017)

    ODBC core components versions:

    - Administrator 6.3.9600.17415

    - Control Panel Startup 6.3.9600.17415

    - Cursor Library 6.3.9600.17415

    - Driver Manager 6.3.9600.17415

    - Localized Resource DLL 6.3.9600.16384

    - Unicode Cursor Library 6.3.9600.17415

    And maybe relevant, since problem occurs for each translation (done in ODBC), mscpxl32.dll (ODBC Code Page Translator) has version 6.3.9600.17415.

    I am available in case of any any additional info.

    Thank you very much.

    Kind regards,

    Japasst

    Thursday, August 2, 2018 9:45 AM
  • I had a similar problem years ago with the ODBC driver using Oracle.  I can't remember the exact details but the driver has to understand the data type or it defaults to a built in MAXLEN parameter.   I'm assuming it doesn't know what varchar(max) is or can't handle it so it defaults to MAXLEN.  I had to change Oracle db schema to make it work. 
    Thursday, August 2, 2018 1:51 PM
  • You changed varchar(max) columns to other data types or ?
    Thursday, August 2, 2018 3:06 PM