Post reply

Avoid Statement Termination due to possible string truncation.

  • January 21, 2018 at 5:23 am

    #342389

    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

  • January 21, 2018 at 5:44 am

    #1976646

    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.
    Larnu.uk

  • January 21, 2018 at 6:23 am

    #1976647

    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.

  • January 21, 2018 at 6:43 am

    #1976648

    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.
    Larnu.uk

  • January 21, 2018 at 10:07 am

    #1976655

    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.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • January 21, 2018 at 10:33 am

    #1976658

    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 5 (of 5 total)

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