how to troubleshoot this issue??? function failing with error

  • Hi All,

    Need some tips to troubleshoot below issue. we are seeing  below error Intermitently and the error automatically gets dissappeared after some time 5-10 mins.
    Error message: "Failed to execute query. Error: String or binary data would be truncated".

    This is a SQL Azure PaaS db.

    select @@version
    go
    Microsoft SQL Azure (RTM) - 12.0.2000.8 Jun 8 2018 18:18:54 Copyright (C) 2018 Microsoft Corporation
    As a work around, developers are dropping and recerating the stored procedure which is calling this function which is returning a table variable.
    Tried  OPTION (RECOMPILE)  at stmt level to check if it is a plan cache issue, but  still we see the error.

    How to find the RCA and fix this issue?? Can anyone help in suggesting on how to troubleshoot the issue. pl suggest.
    Also, attaching the function source code. if any suggestions,. please let me know. I see one we need to do is Exception handling which isn't there.
    Another thing is , want to run a continous  extended event trace for the error number 8152.

    source code of the fucntion:

    One observation
    ============

    Do we need to explicitly say

    RETURN (@LinkedRowFields);

    OR simply

    RETURN;     stmt works ???? because I see only return in the above function.

    Thanks,

    Sam

  • vsamantha35 - Thursday, July 5, 2018 6:06 AM

    Hi All,

    Need some tips to troubleshoot below issue. we are seeing  below error Intermitently and the error automatically gets dissappeared after some time 5-10 mins.
    Error message: "Failed to execute query. Error: String or binary data would be truncated".

    This is a SQL Azure PaaS db.

    select @@version
    go
    Microsoft SQL Azure (RTM) - 12.0.2000.8 Jun 8 2018 18:18:54 Copyright (C) 2018 Microsoft Corporation

    Below is the piece of code which throws above error
    ====================================
    declare @ChangesetId UNIQUEIDENTIFIER,
                        @QuestionnaireId UNIQUEIDENTIFIER,
                        @ScopingTags Common.ListIDs ,
                        @RelatedEntityId UNIQUEIDENTIFIER

    select @ChangesetId=N'156C9944-751B-4385-3D9D-08D5E23B5C82'
    ,@QuestionnaireId = '4BAC4C5A-7DF9-40BB-453D-08D5E17A6526'
    ,@RelatedEntityId=N'4BAC4C5A-7DF9-40BB-4543-08D5E17A6526'

    SELECT
    RowFieldId,
    RowFieldValue,
    EntityType
    FROM Questionnaire.utfGetLinkedRowFields(@changesetid, @RelatedEntityId)

    ---Expected output
    Query succeeded: Affected rows: 0.
    Attaching the screenshot of good run
    https://social.msdn.microsoft.com/Forums/getfile/1290057

    --Intermittent error
    Failed to execute query. Error: String or binary data would be truncated.

    Attaching the screenshot of error run

    https://social.msdn.microsoft.com/Forums/getfile/1290058

    Other thing is that, when we connect to SQL 2017 SSMS and run this query , we dont see this error at all. Dont know why.
    However, from the .net application and If we run the above piece of code from Azure Portal Query editor (preview) ,

    Error:
    Failed to execute query. Error: String or binary data would be truncated.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    As a work around, developers are dropping and recerating the stored procedure which is calling this function which is returning a table variable.
    Tried  OPTION (RECOMPILE)  at stmt level to check if it is a plan cache issue, but  still we see the error.

    How to find the RCA and fix this issue?? Can anyone help in suggesting on how to troubleshoot the issue. pl suggest.
    Also, attaching the function source code. if any suggestions,. please let me know. I see one we need to do is Exception handling which isn't there.
    Another thing is , want to run a continous  extended event trace for the error number 8152.

    source code of the fucntion:

    CREATE FUNCTION [Questionnaire].[utfGetLinkedRowFields]
    (
           @ChangesetId UNIQUEIDENTIFIER,
           @TableQuestionId UNIQUEIDENTIFIER
    )
    RETURNS @LinkedRowFields TABLE
    (
        RowFieldId UNIQUEIDENTIFIER,
           RowFieldSequence int,
           RowFieldValue NVARCHAR(200),
           SourceColumnId UNIQUEIDENTIFIER,
           EntityType varchar(100)
    )
    AS
    BEGIN

    DECLARE @LinkedRowFields_Temp TABLE
    (
        RowFieldId UNIQUEIDENTIFIER,
           RowFieldSequence int,
           RowFieldValue NVARCHAR(200),
           SourceColumnId NVARCHAR(max),
           EntityType varchar(100)
    )

    insert into @LinkedRowFields_Temp

    select trf.Id as RowFieldId,
           trf.SequenceNumber as RowFieldSequence,
           rfpv.Value as RowFieldValue,
           RowFields.SourceColumnId as SourceColumId,
           'TableRowField' as EntityType from
                 (
                        select
                               trf.id as Id,
                               rfpv.Value as SourceColumnId,
                               pf.Name as PropertyFamily,
                               pd.Name as PropertyDefinition,
                               trf.SequenceNumber,
                               trf.ChangesetId as ChangesetId
                        from Questionnaire.TableElements te
                               JOIN Questionnaire.TableElementTypes tet on te.TableElementTypeId = tet.Id
                               JOIN Questionnaire.TableRowFields trf on te.ChangesetId = trf.ChangesetId and trf.TableRowId = te.Id and trf.IsDeleted = 0
                               JOIN Questionnaire.RowFieldPropertyValues rfpv on trf.ChangesetId = rfpv.ChangesetId and trf.id = rfpv.RowFieldId and rfpv.IsDeleted = 0
                               JOIN Questionnaire.PropertyDefinitions pd on rfpv.PropertyDefinitionId = pd.Id
                               JOIN Questionnaire.PropertyFamilies pf ON pf.Id = pd.PropertyFamilyId
                        where
                               te.TableQuestionId = @TableQuestionId
                               and te.ChangesetId = @ChangesetId
                               and tet.ElementType = 'Row'
                               AND pf.Name = 'RowField'
                               and pd.Name = 'SourceColumnId'
                               and te.IsDeleted = 0
                               AND te.IsActive = 1
                               AND trf.IsActive = 1
                               AND rfpv.Value IS NOT NULL
                               AND LEN(rfpv.Value ) > 0
                 ) RowFields
                 JOIN Questionnaire.TableRowFields trf on trf.Id = RowFields.Id and trf.ChangesetId = RowFields.ChangesetId and trf.IsDeleted = 0
                 JOIN Questionnaire.RowFieldPropertyValues rfpv on trf.ChangesetId = rfpv.ChangesetId and trf.id = rfpv.RowFieldId and rfpv.IsDeleted = 0
                 JOIN Questionnaire.PropertyDefinitions pd on rfpv.PropertyDefinitionId = pd.Id and pd.Name = 'DisplayId'
           where
           RowFields.ChangesetId = @ChangesetId
           AND trf.IsActive = 1

    insert into @LinkedRowFields (RowFieldId ,      RowFieldSequence ,  RowFieldValue,      SourceColumnId ,       EntityType)
    select RowFieldId ,  RowFieldSequence ,  RowFieldValue,      SourceColumnId ,    EntityType
    from @LinkedRowFields_Temp
    WHERE TRY_CONVERT(UNIQUEIDENTIFIER, SourceColumnId) IS NOT NULL

    RETURN;
    END

    One observation
    ============

    Do we need to explicitly say

    RETURN (@LinkedRowFields);

    OR simply

    RETURN;     stmt works ???? because I see only return in the above function.

    Thanks,

    Sam

    The simplest thing that comes to my mind is that you have values in column Value from Questionnaire.RowFieldPropertyValues that are over 200 characters long.
    I would change the function into an inline function instead of having it as a multi-statement function. This would make the execution a lot faster.

    CREATE FUNCTION [Questionnaire].[utfGetLinkedRowFields]
    (
    @ChangesetId UNIQUEIDENTIFIER,
    @TableQuestionId UNIQUEIDENTIFIER
    )
    RETURNS TABLE
    AS
    RETURN
    SELECT trf.Id as RowFieldId,
      trf.SequenceNumber as RowFieldSequence,
      rfpv.Value as RowFieldValue,
      RowFields.SourceColumnId as SourceColumId,
      'TableRowField' as EntityType
    FROM
    (
      SELECT
       trf.id as Id,
       rfpv.Value as SourceColumnId,
       pf.Name as PropertyFamily,
       pd.Name as PropertyDefinition,
       trf.SequenceNumber,
       trf.ChangesetId as ChangesetId
      FROM Questionnaire.TableElements te
      JOIN Questionnaire.TableElementTypes tet on te.TableElementTypeId = tet.Id
      JOIN Questionnaire.TableRowFields trf on te.ChangesetId = trf.ChangesetId and trf.TableRowId = te.Id and trf.IsDeleted = 0
      JOIN Questionnaire.RowFieldPropertyValues rfpv on trf.ChangesetId = rfpv.ChangesetId and trf.id = rfpv.RowFieldId and rfpv.IsDeleted = 0
      JOIN Questionnaire.PropertyDefinitions pd on rfpv.PropertyDefinitionId = pd.Id
      JOIN Questionnaire.PropertyFamilies pf ON pf.Id = pd.PropertyFamilyId
      WHERE te.TableQuestionId = @TableQuestionId
      AND  te.ChangesetId = @ChangesetId
      AND  tet.ElementType = 'Row'
      AND  pf.Name = 'RowField'
      AND  pd.Name = 'SourceColumnId'
      AND  te.IsDeleted = 0
      AND  te.IsActive = 1
      AND  trf.IsActive = 1
      AND  rfpv.Value IS NOT NULL
      AND  LEN(rfpv.Value ) > 0
    ) RowFields
    JOIN Questionnaire.TableRowFields trf on trf.Id = RowFields.Id and trf.ChangesetId = RowFields.ChangesetId and trf.IsDeleted = 0
    JOIN Questionnaire.RowFieldPropertyValues rfpv on trf.ChangesetId = rfpv.ChangesetId and trf.id = rfpv.RowFieldId and rfpv.IsDeleted = 0
    JOIN Questionnaire.PropertyDefinitions pd on rfpv.PropertyDefinitionId = pd.Id and pd.Name = 'DisplayId'
    WHERE RowFields.ChangesetId = @ChangesetId
    AND trf.IsActive = 1
    AND rfpv.Value IS NOT NULL;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks a lot Luis. I ll make that change and try and keep you posted. Tmr I will try this out.

    What I understand is, you have avoided the temp table creation and datatype mismatch. Is that correct??

  • Luis Cazares - Thursday, July 5, 2018 7:09 AM

    vsamantha35 - Thursday, July 5, 2018 6:06 AM

    Hi All,

    Need some tips to troubleshoot below issue. we are seeing  below error Intermitently and the error automatically gets dissappeared after some time 5-10 mins.
    Error message: "Failed to execute query. Error: String or binary data would be truncated".

    This is a SQL Azure PaaS db.

    select @@version
    go
    Microsoft SQL Azure (RTM) - 12.0.2000.8 Jun 8 2018 18:18:54 Copyright (C) 2018 Microsoft Corporation

    Below is the piece of code which throws above error
    ====================================
    declare @ChangesetId UNIQUEIDENTIFIER,
                        @QuestionnaireId UNIQUEIDENTIFIER,
                        @ScopingTags Common.ListIDs ,
                        @RelatedEntityId UNIQUEIDENTIFIER

    select @ChangesetId=N'156C9944-751B-4385-3D9D-08D5E23B5C82'
    ,@QuestionnaireId = '4BAC4C5A-7DF9-40BB-453D-08D5E17A6526'
    ,@RelatedEntityId=N'4BAC4C5A-7DF9-40BB-4543-08D5E17A6526'

    SELECT
    RowFieldId,
    RowFieldValue,
    EntityType
    FROM Questionnaire.utfGetLinkedRowFields(@changesetid, @RelatedEntityId)

    ---Expected output
    Query succeeded: Affected rows: 0.
    Attaching the screenshot of good run
    https://social.msdn.microsoft.com/Forums/getfile/1290057

    --Intermittent error
    Failed to execute query. Error: String or binary data would be truncated.

    Attaching the screenshot of error run

    https://social.msdn.microsoft.com/Forums/getfile/1290058

    Other thing is that, when we connect to SQL 2017 SSMS and run this query , we dont see this error at all. Dont know why.
    However, from the .net application and If we run the above piece of code from Azure Portal Query editor (preview) ,

    Error:
    Failed to execute query. Error: String or binary data would be truncated.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    As a work around, developers are dropping and recerating the stored procedure which is calling this function which is returning a table variable.
    Tried  OPTION (RECOMPILE)  at stmt level to check if it is a plan cache issue, but  still we see the error.

    How to find the RCA and fix this issue?? Can anyone help in suggesting on how to troubleshoot the issue. pl suggest.
    Also, attaching the function source code. if any suggestions,. please let me know. I see one we need to do is Exception handling which isn't there.
    Another thing is , want to run a continous  extended event trace for the error number 8152.

    source code of the fucntion:

    CREATE FUNCTION [Questionnaire].[utfGetLinkedRowFields]
    (
           @ChangesetId UNIQUEIDENTIFIER,
           @TableQuestionId UNIQUEIDENTIFIER
    )
    RETURNS @LinkedRowFields TABLE
    (
        RowFieldId UNIQUEIDENTIFIER,
           RowFieldSequence int,
           RowFieldValue NVARCHAR(200),
           SourceColumnId UNIQUEIDENTIFIER,
           EntityType varchar(100)
    )
    AS
    BEGIN

    DECLARE @LinkedRowFields_Temp TABLE
    (
        RowFieldId UNIQUEIDENTIFIER,
           RowFieldSequence int,
           RowFieldValue NVARCHAR(200),
           SourceColumnId NVARCHAR(max),
           EntityType varchar(100)
    )

    insert into @LinkedRowFields_Temp

    select trf.Id as RowFieldId,
           trf.SequenceNumber as RowFieldSequence,
           rfpv.Value as RowFieldValue,
           RowFields.SourceColumnId as SourceColumId,
           'TableRowField' as EntityType from
                 (
                        select
                               trf.id as Id,
                               rfpv.Value as SourceColumnId,
                               pf.Name as PropertyFamily,
                               pd.Name as PropertyDefinition,
                               trf.SequenceNumber,
                               trf.ChangesetId as ChangesetId
                        from Questionnaire.TableElements te
                               JOIN Questionnaire.TableElementTypes tet on te.TableElementTypeId = tet.Id
                               JOIN Questionnaire.TableRowFields trf on te.ChangesetId = trf.ChangesetId and trf.TableRowId = te.Id and trf.IsDeleted = 0
                               JOIN Questionnaire.RowFieldPropertyValues rfpv on trf.ChangesetId = rfpv.ChangesetId and trf.id = rfpv.RowFieldId and rfpv.IsDeleted = 0
                               JOIN Questionnaire.PropertyDefinitions pd on rfpv.PropertyDefinitionId = pd.Id
                               JOIN Questionnaire.PropertyFamilies pf ON pf.Id = pd.PropertyFamilyId
                        where
                               te.TableQuestionId = @TableQuestionId
                               and te.ChangesetId = @ChangesetId
                               and tet.ElementType = 'Row'
                               AND pf.Name = 'RowField'
                               and pd.Name = 'SourceColumnId'
                               and te.IsDeleted = 0
                               AND te.IsActive = 1
                               AND trf.IsActive = 1
                               AND rfpv.Value IS NOT NULL
                               AND LEN(rfpv.Value ) > 0
                 ) RowFields
                 JOIN Questionnaire.TableRowFields trf on trf.Id = RowFields.Id and trf.ChangesetId = RowFields.ChangesetId and trf.IsDeleted = 0
                 JOIN Questionnaire.RowFieldPropertyValues rfpv on trf.ChangesetId = rfpv.ChangesetId and trf.id = rfpv.RowFieldId and rfpv.IsDeleted = 0
                 JOIN Questionnaire.PropertyDefinitions pd on rfpv.PropertyDefinitionId = pd.Id and pd.Name = 'DisplayId'
           where
           RowFields.ChangesetId = @ChangesetId
           AND trf.IsActive = 1

    insert into @LinkedRowFields (RowFieldId ,      RowFieldSequence ,  RowFieldValue,      SourceColumnId ,       EntityType)
    select RowFieldId ,  RowFieldSequence ,  RowFieldValue,      SourceColumnId ,    EntityType
    from @LinkedRowFields_Temp
    WHERE TRY_CONVERT(UNIQUEIDENTIFIER, SourceColumnId) IS NOT NULL

    RETURN;
    END

    One observation
    ============

    Do we need to explicitly say

    RETURN (@LinkedRowFields);

    OR simply

    RETURN;     stmt works ???? because I see only return in the above function.

    Thanks,

    Sam

    The simplest thing that comes to my mind is that you have values in column Value from Questionnaire.RowFieldPropertyValues that are over 200 characters long.
    I would change the function into an inline function instead of having it as a multi-statement function. This would make the execution a lot faster.

    CREATE FUNCTION [Questionnaire].[utfGetLinkedRowFields]
    (
    @ChangesetId UNIQUEIDENTIFIER,
    @TableQuestionId UNIQUEIDENTIFIER
    )
    RETURNS TABLE
    AS
    RETURN
    SELECT trf.Id as RowFieldId,
      trf.SequenceNumber as RowFieldSequence,
      rfpv.Value as RowFieldValue,
      RowFields.SourceColumnId as SourceColumId,
      'TableRowField' as EntityType
    FROM
    (
      SELECT
       trf.id as Id,
       rfpv.Value as SourceColumnId,
       pf.Name as PropertyFamily,
       pd.Name as PropertyDefinition,
       trf.SequenceNumber,
       trf.ChangesetId as ChangesetId
      FROM Questionnaire.TableElements te
      JOIN Questionnaire.TableElementTypes tet on te.TableElementTypeId = tet.Id
      JOIN Questionnaire.TableRowFields trf on te.ChangesetId = trf.ChangesetId and trf.TableRowId = te.Id and trf.IsDeleted = 0
      JOIN Questionnaire.RowFieldPropertyValues rfpv on trf.ChangesetId = rfpv.ChangesetId and trf.id = rfpv.RowFieldId and rfpv.IsDeleted = 0
      JOIN Questionnaire.PropertyDefinitions pd on rfpv.PropertyDefinitionId = pd.Id
      JOIN Questionnaire.PropertyFamilies pf ON pf.Id = pd.PropertyFamilyId
      WHERE te.TableQuestionId = @TableQuestionId
      AND  te.ChangesetId = @ChangesetId
      AND  tet.ElementType = 'Row'
      AND  pf.Name = 'RowField'
      AND  pd.Name = 'SourceColumnId'
      AND  te.IsDeleted = 0
      AND  te.IsActive = 1
      AND  trf.IsActive = 1
      AND  rfpv.Value IS NOT NULL
      AND  LEN(rfpv.Value ) > 0
    ) RowFields
    JOIN Questionnaire.TableRowFields trf on trf.Id = RowFields.Id and trf.ChangesetId = RowFields.ChangesetId and trf.IsDeleted = 0
    JOIN Questionnaire.RowFieldPropertyValues rfpv on trf.ChangesetId = rfpv.ChangesetId and trf.id = rfpv.RowFieldId and rfpv.IsDeleted = 0
    JOIN Questionnaire.PropertyDefinitions pd on rfpv.PropertyDefinitionId = pd.Id and pd.Name = 'DisplayId'
    WHERE RowFields.ChangesetId = @ChangesetId
    AND trf.IsActive = 1
    AND rfpv.Value IS NOT NULL;

    Louis, one thing I didnt understand , you mentioned
    "The simplest thing that comes to my mind is that you have values in column Value from Questionnaire.RowFieldPropertyValues that are over 200 characters long."

    How do you know it is over 200 characters? and how it can create an issue? just trying to understand.  Can u pl elaborate a little bit.

  • vsamantha35 - Thursday, July 5, 2018 8:25 AM

    Thanks a lot Luis. I ll make that change and try and keep you posted. Tmr I will try this out.

    What I understand is, you have avoided the temp table creation and datatype mismatch. Is that correct??

    Yes, and kept all as a single statement (select).
    If you still find problems, check for string lengths and find which could be too short  for the values inserted on it.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks Luis. One more clarification, why SSMS is not showing that error and why only Azure portal Query editor was able to detect it ?

  • vsamantha35 - Thursday, July 5, 2018 8:29 AM

    Louis, one thing I didnt understand , you mentioned
    "The simplest thing that comes to my mind is that you have values in column Value from Questionnaire.RowFieldPropertyValues that are over 200 characters long."

    How do you know it is over 200 characters? and how it can create an issue? just trying to understand.  Can u pl elaborate a little bit.

    I mentioned that because you're inserting that column into this: RowFieldValue NVARCHAR(200)
    That's the only string with a limit that has variable value which is unknown for me. SourceColumnId limit is 2GB and EntityType always gets the string 'TableRowField'.

    vsamantha35 - Thursday, July 5, 2018 8:41 AM

    Thanks Luis. One more clarification, why SSMS is not showing that error and why only Azure portal Query editor was able to detect it ?

    This might be caused by different data depending on where you run it.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Its the same parameters. still ssms was not showing up this errors.

  • vsamantha35 - Thursday, July 5, 2018 9:59 AM

    Its the same parameters. still ssms was not showing up this errors.

    It's not about the parameters, it's about the data in the tables.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Still didn't get it. If same parameters, then same data has to get returned. That's what my point is.

Viewing 10 posts - 1 through 9 (of 9 total)

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