Solved

Updation Error

Posted on 2006-11-23
8
147 Views
Last Modified: 2008-02-01

i am trying to update thro ado recordset . i am getting the following error

"cannot create a row size of 13194 which is greater than allowable max size of 8060 in sql server 2000"

all field lengths is 500 chars, the length of data which are getting updated is less than 400chars

Pls let  us know the cause and remedy for the same

Regards
Venkat
0
Comment
Question by:venkataramanaiahsr
  • 4
  • 3
8 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 18006260
The cause is that the maximum size of a row in a table SQL Server is 8K, and you are trying to create one of 13K. There is no workaround to this.

HOWEVER, You can consider changing your table design and breaking the single row on a table up into multiple tables, referenced by a constraint. (this is generally considered best practice anyway). Please post you table structure here and we can have a look at it and advise.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18006293
>all field lengths is 500 chars, the length of data which are getting updated is less than 400chars
you probably have each field less than 400 chars, but the total of all the columns benig larger
now, if you use CHAR() instead of VARCHAR(), you will indeed store for each column 500 bytes, and not only the amount of data you think you store.

if you have CHAR() data type, change to VARCHAR().
if the total defined row size is >8060 bytes, you have to split the table into 2 parts
0
 

Author Comment

by:venkataramanaiahsr
ID: 18006472
TranID      int      4      0
0      CathNo      int      4      0
0      CaseID      int      4      0
0      DoP      varchar      8      0
0      ReportType      varchar      25      1
0      Intervention      nvarchar      500      1
0      Indication      nvarchar      500      1
0      LC_Lesion      varchar      500      1
0      LC_Type      varchar      500      1
0      LC_Length      varchar      500      1
0      LC_Calcification      varchar      500      1
0      LC_InvolvingBranch      varchar      500      1
0      LC_Thrombus      varchar      500      1
0      LC_Size      varchar      500      1
0      LC_Flow      varchar      500      1
0      LC_Balloon      varchar      500      1
0      LC_Wire      varchar      500      1
0      LC_GuidingCatheter      varchar      500      1
0      IP_NoOfInflation      varchar      500      1
0      IP_Pressure      varchar      500      1
0      IP_Duration      varchar      500      1
0      SD_Indication      varchar      500      1
0      SD_Type      varchar      500      1
0      SD_MaxPressure      varchar      500      1
0      SD_Duration      varchar      500      1
0      SD_NoOfInflation      varchar      500      1
0      SD_ResidualStenosis      varchar      500      1
0      SD_Result      varchar      500      1
0      Remarks      varchar      500      1
0      Cardiologist1      varchar      25      1
0      Cardiologist2      varchar      25      1
0      Cardiologist3      varchar      25      1
0      NoOfColumns      smallint      2      1
0      Finalized      bit      1      

The above structure does not work . i am getting the above error

where as the below mentioned structure is working fine


3      Caseid      int      4      0
0      FDiag1      varchar      500      0
0      FDiag2      varchar      500      0
0      FDiag3      varchar      500      0
0      FDiag4      varchar      260      0
0      FDiag5      varchar      260      0
0      FDiag6      varchar      260      0
0      FDiag7      varchar      260      0
0      Fdiag8      varchar      260      0
0      Fdiag9      varchar      260      0
0      Fdiag10      varchar      260      0
0      History      varchar      2000      0
0      OnExamination      varchar      2000      0
0      PulseRate      varchar      150      0
0      BP      varchar      150      0
0      JVP      varchar      150      0
0      CVS      varchar      150      0
0      RS      varchar      150      0
0      PA      varchar      150      0
0      CNS      varchar      150      0
0      Investigations      varchar      100      0
0      CourseinHospital      varchar      2500      0
0      Adviceondischarge      varchar      2000      0
0      Remarks      varchar      1000      0
0      Authorize      bit      1      0
0      EnteredbyCode      int      4      0
0      EnteredbyName      varchar      50      0
0      EnteredbyDesig      varchar      50      0
0      Authorizedbycode      int      4      0
0      AuthorizedbyName      varchar      50      0
0      AuthorizedbyDesig      varchar      50      0
0      Finalyzedbycode      int      4      0
0      FinalyzedbyName      varchar      50      0
0      FinalyzedbyDesig      varchar      50      0
0      Prescription      varchar      10      0
0      DoD      varchar      8      0
0      ToD      varchar      4      0
0      DsTypecode      int      4      1
0      DsType      varchar      50      1
0      concurrencyid      int      4      0
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18006518
The first thing I would look at wuold be to split non-specific data off to a different table, and link in with an ID, normalising your data structure.

For example:
Create a table called cardiologist where you store the cardiologists information
tblCardiologist
CardioID int idendity(1,1)
CardioDescription varchar(25)

And then on the other table change
0     Cardiologist1     int
0     Cardiologist2     int
0     Cardiologist3     int
And then simply store the ID of the cardiologists in there - You should also create a foreign key to enforce referential integrity. This alone will knock off 60 odd bytes from your row size.

Another good candidate would be the data that you have logically grouped (not really knowing the data it is a little difficult, but)

TransactionSD
SDID int
SD_Indication     varchar     500
SD_Type     varchar     500
SD_MaxPressure     varchar     500
SD_Duration     varchar     500
SD_NoOfInflation     varchar     500
SD_ResidualStenosis     varchar     500
SD_Result     varchar     500

And then removing all those columns from the Trans table and replacing with a single column
SDID int
to link to the relevant TransactionSD row.

This would knock another 3,500 odd bytes off your row size.

True, it makes the reporting a little more complex (you have to use joins), but not impossibly so.

Hope this gives you a good idea of how to go about this (you can do the same for IP... and LD ...)

Cheers
Night
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 29

Expert Comment

by:Nightman
ID: 18006525
As I said, I don't know your data - it is possible that you want to link on the CaseID instead of the TranID, but I think you get the idea.
0
 

Author Comment

by:venkataramanaiahsr
ID: 18025808
I Got your point but what is puzzling me is  with the following table  , i am not getting any update error .
on first look, the size of the table row > the size of the table row in the table on which i am getting update error

am i missing something here?????????. Pls getback as it is very urgent

3     Caseid     int     4     0
0     FDiag1     varchar     500     0
0     FDiag2     varchar     500     0
0     FDiag3     varchar     500     0
0     FDiag4     varchar     260     0
0     FDiag5     varchar     260     0
0     FDiag6     varchar     260     0
0     FDiag7     varchar     260     0
0     Fdiag8     varchar     260     0
0     Fdiag9     varchar     260     0
0     Fdiag10     varchar     260     0
0     History     varchar     2000     0
0     OnExamination     varchar     2000     0
0     PulseRate     varchar     150     0
0     BP     varchar     150     0
0     JVP     varchar     150     0
0     CVS     varchar     150     0
0     RS     varchar     150     0
0     PA     varchar     150     0
0     CNS     varchar     150     0
0     Investigations     varchar     100     0
0     CourseinHospital     varchar     2500     0
0     Adviceondischarge     varchar     2000     0
0     Remarks     varchar     1000     0
0     Authorize     bit     1     0
0     EnteredbyCode     int     4     0
0     EnteredbyName     varchar     50     0
0     EnteredbyDesig     varchar     50     0
0     Authorizedbycode     int     4     0
0     AuthorizedbyName     varchar     50     0
0     AuthorizedbyDesig     varchar     50     0
0     Finalyzedbycode     int     4     0
0     FinalyzedbyName     varchar     50     0
0     FinalyzedbyDesig     varchar     50     0
0     Prescription     varchar     10     0
0     DoD     varchar     8     0
0     ToD     varchar     4     0
0     DsTypecode     int     4     1
0     DsType     varchar     50     1
0     concurrencyid     int     4     0
0
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 18025998
Because you are using varchar (variable length char), you will only get this error if the data that you are inserting/updating is longer than the max. So if you had 1 character in each column there would be no error.

If you had to change that to char (which pads the column with spaces) you would not even be able to save the table structure.
0
 

Author Comment

by:venkataramanaiahsr
ID: 18026598
Thanks for  your suggestion. we just trimmed the fields at the time of updation and is working fine


0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now