February 12, 2019 at 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?
February 12, 2019 at 11:32 am
mjohnson 71479 - Tuesday, February 12, 2019 10:22 AMI 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.
February 12, 2019 at 12:52 pm
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