Need a help with table Update issue

Question

Post reply

Need a help with table Update issue

Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
September 20, 2010 at 8:27 am

#387829

Hi All,
Problem 1:
I have a table with 15 k rows of datam but when i upate the columns i got the below error,
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8077 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.
But Have only 38 columns,
Please help me to update the table bulky,
Problem 2
I have a trigger on table for updated,
if i update the table if the trigger having error the updated is rollbacked, but i have exceptions handling in Trigger , but the main update also rollbacked,but i did not use any begin tran or commit in exceptions handling
Incase the trigger face the proble but the table should update..
tell me the proper excetions for trigger

Viewing 5 posts - 1 through 4 (of 4 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

What is the datatype of the column that throws the error in problem 1 ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Saravanan_tvr (9/20/2010)
Hi All,
Problem 1:
I have a table with 15 k rows of datam but when i upate the columns i got the below error,
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8077 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.
But Have only 38 columns,

Can you please post the CREATE statement for this table?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

For problem 1 see this blog post by Kalen Delaney.

For problem 2 you are out of luck. Triggers fire as part of the original transaction and any error or rollback in the trigger will rollback the originally update. You'd want to handle the "trigger" code in your business layer or use some kind of asynchronous process, possibly involving Service Broker to do the second part.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 4

Thanks a lot for valuable solutions ....

The first problem I overcame by using the Alter table with rebuild partition all now bulk update is going..

But the second problem ?