locked
binary data or string would be truncated RRS feed

  • Question

  • Online blogs suggested that query hint OPTION(querytraceon 460) inserts the clipped string and also notifies the row and column where the problem is. I tried but I got the truncation error as usual. However I can insert with ANSI warning off.

    OPTION(querytraceon 460) This hint works or not?

    Friday, February 7, 2020 5:25 AM

Answers

All replies

  • Hi Curendra,

    The truncation error may occur when the length of data exceeds the field. OPTION(querytraceon 460) will work in SQL Server 2016-17.

    Refer to How to fix the error “String or binary data would be truncated”.

    Best Regards,

    Lily


    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

    Friday, February 7, 2020 6:23 AM
  • Mine is sql server 16 sp1.
    Friday, February 7, 2020 8:14 AM
  • Hi Curendra,

    I have not installed SQL Server 2016 sp1, did you make a try in this version of SQL Server? 

    Best Regards,

    Lily


    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

    Friday, February 7, 2020 8:51 AM
  • Yes I tried but didn't get the expected result. I get usual error even using the query hint.
    Friday, February 7, 2020 8:52 AM
  • However I can insert with ANSI warning off.

    By short the text to the allowed size, e.g. using the LEFT function.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, February 7, 2020 9:16 AM
  • My concern is how to make the query hint OPTION(querytraceon 460) work as it is mentioned in the blogs.
    Friday, February 7, 2020 9:23 AM
  • DBCC traceon(460)

    The above syntax is at the top of the commands

    Trace Flag : 460

    Function: Changes the ”String or binary would be truncated” error message to a much more descriptive one, giving table and column name for the offending data, and includes the actual string. Works in SQL Server 2019 CTP 2.1 and 2.2

    Friday, February 7, 2020 9:38 AM
  • See DBCC TRACEON - Trace Flags (Transact-SQL) => 460: "Note:This trace flag applies to SQL Server 2017 (14.x) CU12 and higher builds."; you have 2016 where the trace flag do not applies to.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Edited by Olaf HelperMVP Friday, February 7, 2020 9:40 AM
    • Marked as answer by Curendra Friday, February 7, 2020 9:48 AM
    Friday, February 7, 2020 9:40 AM
  • Yes when I tried in server 19, i got the column name and the value where the problem is. So this does not work in server 16 but the blogs have mentioned it.
    Friday, February 7, 2020 9:48 AM
  • Yes when I tried in server 19, i got the column name and the value where the problem is. So this does not work in server 16 but the blogs have mentioned it.

    It works in SQL 2016. If you are on the require service pack and cumulative update, that is. You said that you are on SQL 2016 SP1. You need to be on at least SQL 2016 SP2 CU6.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, February 7, 2020 10:54 PM