SQL Insert - String or binary data would be truncated for INT values

Question

I'm getting the "String or binary data would be truncated" error when trying to insert integers to one of my tables.

I've read several post about the length of the column vs the length of the value one is inserting, but it doesn't seem to be my case once the columns are all int or smallint type and the values are all maximum two digits.

The table structure is the following:

CREATE TABLE [tblvUserLocation] (
    [User_Location_ID] [int] IDENTITY (1, 1) NOT NULL ,
    [Location_ID] [int] NULL ,
    [Line_Type_ID] [int] NULL ,
    [User_ID] [int] NULL ,
    [Active] [smallint] NULL CONSTRAINT [DF_tblvUserLocation_Active] DEFAULT (1),
    [Last_Updated] [smalldatetime] NULL CONSTRAINT [DF_tblvUserLocation_Last_Updated] DEFAULT (getdate()),
    [Last_Updated_By] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblvUserLocation_Last_Updated_By] DEFAULT (suser_sname())
) ON [PRIMARY]
GO

The insert I'm trying to run is the following:

insert into tblvUserLocation (Location_ID, Line_Type_ID, [User_ID], Active)
values (20, 2, 41, 1)

And the error I'm getting is the following:

Server: Msg 8152, Level 16, State 2, Line 1 String or binary data would be truncated. The statement has been terminated.

If that makes any difference, I'm using SQL Server 2000.

Please let me know what your thoughts are.

Thanks!

Answer 1

Looks like the problem comes from your [DF_tblvUserLocation_Last_Updated_By] constraint.

It's pulling the current username which is more than likely longer than the length of your [Last_Updated_By] column VARCHAR(10).

Update your DDL to:

[Last_Updated_By] [varchar] (128)