Avoid Statement Termination due to possible string truncation.

  • funooni

    Ten Centuries

    Points: 1314

    Hi,

    "string or binary data would be truncated. the statement has been terminated"

    This is a very common error about possible truncation of data when we try to insert something bigger than the column size. Let's say the column size is varchar(20) and we try to insert string of size 21 then we get this error.

    My question is "Is there a way to tell SQL Server to please go ahead and truncate the data and don't terminate the query "?

    I have a scenario where I would like it to truncate and insert only as much as possible. But i don't want to stop the query execution.

    Many Thanks

  • Thom A

    SSC Guru

    Points: 98216

    Yes, you with the following
    [Code]SET ANSI_WARNINGS OFF;

    --INSERT  operation

    SET ANSI_WARNINGS ON;[/code]
    It's important to remember to switch the warnings back on afterwards.

    Personally, however, rather than allowing the truncation, consider using LEFT.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • funooni

    Ten Centuries

    Points: 1314

    Thanks Thom,

    But this would just disappear the warning. Let me make it more clear about what i am looking for

    create table TEST_INSERT
    (id int , name varchar (3))

    GO 

    INSERT INTO TEST_INSERT
    SELECT 1, 'FOUR'

    In case the warnings are on i get the error, otherwise it says completed successfully and in both cases nothing gets inserted.
    What I am looking for is to have the first 3 characters inserted and the rest ignored.
    I cannot use a LEFT/SUBSTRING function as I am trying to fetch data from a linked server.

    Thanks.

  • Thom A

    SSC Guru

    Points: 98216

    funooni - Sunday, January 21, 2018 6:23 AM

    Thanks Thom,

    But this would just disappear the warning. Let me make it more clear about what i am looking for

    create table TEST_INSERT
    (id int , name varchar (3))

    GO 

    INSERT INTO TEST_INSERT
    SELECT 1, 'FOUR'

    In case the warnings are on i get the error, otherwise it says completed successfully and in both cases nothing gets inserted.
    What I am looking for is to have the first 3 characters inserted and the rest ignored.
    I cannot use a LEFT/SUBSTRING function as I am trying to fetch data from a linked server.

    Thanks.

    Have you tried what I provided? And what does using a linked server have to do with not being able to use LEFT?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Phil Parkin

    SSC Guru

    Points: 243480

    funooni - Sunday, January 21, 2018 6:23 AM

    What I am looking for is to have the first 3 characters inserted and the rest ignored. 

    As far as I know, there is no setting which will allow this to happen. You need to fix the data prior to insert.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • HappyGeek

    SSCoach

    Points: 18657

    Thom A - Sunday, January 21, 2018 6:43 AM

    Have you tried what I provided? And what does using a linked server have to do with not being able to use LEFT?

    +1, can't see issue.

    ...

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply