truncation error on varchar(max) column

  • I have a table that I use to log validation messages received by users so that we know what they are having trouble with. 
    CREATE TABLE [dbo].[ValidationLog](
        [ValidationID] [int] IDENTITY(1,1) NOT NULL,
        [UserID] [char](9) NOT NULL,
        [ValidationMessage] [varchar](max) NOT NULL,
        [DataSubmitted] [varchar](max) NOT NULL,
        [InstanceDate] [datetime] NOT NULL

    I save the data using this stored procedure. 
    CREATE PROCEDURE [dbo].[LogValidationMessage]

    @user-id char(9),
    @ValidationMessage varchar(max),
    @DataSubmitted varchar(max)

    AS

    SET NOCOUNT ON

    INSERT INTO
        dbo.ValidationLog
    VALUES
    (
        @UserID,
        @ValidationMessage,
        @DataSubmitted,
        GETDATE()
    )

    This morning I had an email that said an error was thrown while trying to log some of this information.   My error emails pass me the data being posted so that I can see what is going on.  From the data passed in the error email I can see that the validation was telling the user they had skipped a number of required fields.   The error message I received said it was a SQL Server error   String data, right truncation  and pointed to the line of my code that executes the parameterized query.   I am curious why this was trying to truncate something.   Didn't the use of varchar(max) allow for up to 2gb?

  • mjohnson 71479 - Tuesday, February 12, 2019 10:22 AM

    I have a table that I use to log validation messages received by users so that we know what they are having trouble with. 
    CREATE TABLE [dbo].[ValidationLog](
        [ValidationID] [int] IDENTITY(1,1) NOT NULL,
        [UserID] [char](9) NOT NULL,
        [ValidationMessage] [varchar](max) NOT NULL,
        [DataSubmitted] [varchar](max) NOT NULL,
        [InstanceDate] [datetime] NOT NULL

    I save the data using this stored procedure. 
    CREATE PROCEDURE [dbo].[LogValidationMessage]

    @user-id char(9),
    @ValidationMessage varchar(max),
    @DataSubmitted varchar(max)

    AS

    SET NOCOUNT ON

    INSERT INTO
        dbo.ValidationLog
    VALUES
    (
        @UserID,
        @ValidationMessage,
        @DataSubmitted,
        GETDATE()
    )

    This morning I had an email that said an error was thrown while trying to log some of this information.   My error emails pass me the data being posted so that I can see what is going on.  From the data passed in the error email I can see that the validation was telling the user they had skipped a number of required fields.   The error message I received said it was a SQL Server error   String data, right truncation  and pointed to the line of my code that executes the parameterized query.   I am curious why this was trying to truncate something.   Didn't the use of varchar(max) allow for up to 2gb?

    Could it be an invalid UserID?
    It's also consider a good practice to specify the destination columns when doing an insert to avoid problems with structure changes.

    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
  • Nope,  ID's are actually system assigned so they never go over 9 characters.    Beyond that though, in the error email it showed the data the user was trying to submit so I can see the ID that was being passed.  It is how I knew the user did not fill out a lot of the required fields.   Therefore the validation message would have been quite large and the data submitted would have been quite small

Viewing 3 posts - 1 through 2 (of 2 total)

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