String or binary data would be truncated

Question

Post reply

String or binary data would be truncated

Judy Why

Hall of Fame

Points: 3656
More actions
May 4, 2009 at 11:29 am

#155653

Hello everyone,
When one developer tried to update the records using Management studio, got error:String or binary data would be truncated. I have checked that data type is varchar(255). The orginal data is 28 characters. He just updated one letter from lower case to upper case.
And then I tested on another database. the weird thing is even though in the same table,same column, some records are able to be modified and some get the same error.
Does anybody know what is happening? BTW, SQl server is 2005 SP3.
Thanks

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

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

JamieX Hall of Fame Points: 3763 More actions · Answer 1

JamieX

Hall of Fame

Points: 3763

May 4, 2009 at 11:34 am

#988239

Perhaps a trigger causing this?

David-1011936 Ten Centuries Points: 1378 More actions · Answer 2

Have you tried issuing the update statement, for the same row, from T-SQL?

Judy Why Hall of Fame Points: 3656 More actions · Answer 3

Judy Why

Hall of Fame

Points: 3656

May 4, 2009 at 11:39 am

#988247

No Triggers

Judy Why Hall of Fame Points: 3656 More actions · Answer 4

David (5/4/2009)
Have you tried issuing the update statement, for the same row, from T-SQL?

Yes, they are working.

WayneS SSC Guru Points: 95460 More actions · Answer 5

I think at this point that we're going to need to see the data being modified. It would also help to see the table structure, and the command(s) being issues in SSMS.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

David-1011936 Ten Centuries Points: 1378 More actions · Answer 6

Then what's probably happening is that the management window is updating multiple records at a time, based on what it sees as the primary key to the table. When you write the update statement in TSQL, its being limited to just the row you are trying to update, and everything works as advertised.

Try running profiler, and doing the update through the management window; that may shed some light on what's happening.

Judy Why Hall of Fame Points: 3656 More actions · Answer 7

WayneS (5/4/2009)
I think at this point that we're going to need to see the data being modified. It would also help to see the table structure, and the command(s) being issues in SSMS.

Table structure

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tbl_pages](

[page_id] [int] NOT NULL,

[image_id] [int] NULL,

[pdf_e] [varchar](255) NULL,

[pdf_f] [varchar](255) NULL,

[text_e] [text] NOT NULL,

[text_f] [text] NULL,

[intro_e] [text] NULL,

[intro_f] [text] NULL,

[next_page] [smallint] NOT NULL CONSTRAINT [DF_tbl_pages_show_next_page] DEFAULT (0),

[prev_page] [smallint] NOT NULL CONSTRAINT [DF_tbl_pages_prev_page] DEFAULT (0),

CONSTRAINT [PK_tblPages] PRIMARY KEY CLUSTERED

(

[page_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF

I just updated column "PAF_e" from null to letter 'n'

Judy Why Hall of Fame Points: 3656 More actions · Answer 8

sql profile trace result like below when I am using management studio to update record. from the trace, I can see it was really tring to update only one resoce at the same time.

see my last reply to see table structure.

exec sp_executesql N'UPDATE tbl_pages SET pdf_e = @pdf_e WHERE (page_id = @Param1) AND (image_id IS NULL) AND (pdf_e IS NULL) AND (pdf_f IS NULL) AND (text_e LIKE @Param2) AND (text_f LIKE @Param3) AND (intro_e LIKE @Param4) AND (intro_f LIKE @Param5) AND (next_page = @Param6) AND (prev_page = @Param7)',N'@pdf_e nvarchar(1),@Param1 int,@Param2 nvarchar(3265),@Param3 nvarchar(max) ,@Param4 nvarchar(345),@Param5 nvarchar(422),@Param6 smallint,@Param7 smallint',@pdf_e=N'n',@Param1=3,@Param2=N'orginal text',@Param6=0,@Param7=0.

the error message is

"No row was updated.

the data in row 1 was not commmited.

error Source: .Net SqlClient data Provider

Error Message: String ot binary data would be reuncated

The Statement has been terminated.

Correct the errors and retry or press ESC to cancel the change(s)."

David-1011936 Ten Centuries Points: 1378 More actions · Answer 9

So, that's the equivalent of

update tbl_pages set pdf_e ='n'

where page_id =3 and image_id is null and pdf_e is null and pdf_f is null and text_e like 'orginal text' and text_f like <> and intro_e like <> and intro_f like and next_page = 0 and prev_page = 0.

I couldn't see the values for a couple of the variables in what you posted, but I may have missed something there. But, what you're saying is that if you run that update statement, it will only update one row? Try running that (make sure to put the in the values for text_f and intro_e), and see what happens.