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

Question

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

vsamantha35

SSChampion

Points: 11551
More actions
July 5, 2018 at 6:06 am

#354096

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

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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
--Intermittent error
Failed to execute query. Error: String or binary data would be truncated.
Attaching the screenshot of error run
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

vsamantha35 SSChampion Points: 11551 More actions · Answer 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??

vsamantha35 SSChampion Points: 11551 More actions · Answer 3

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
--Intermittent error
Failed to execute query. Error: String or binary data would be truncated.
Attaching the screenshot of error run
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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

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

vsamantha35 SSChampion Points: 11551 More actions · Answer 5

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

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

vsamantha35 SSChampion Points: 11551 More actions · Answer 7

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

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

vsamantha35 SSChampion Points: 11551 More actions · Answer 9

Still didn't get it. If same parameters, then same data has to get returned. That's what my point is.