?
Solved

Updation Error

Posted on 2006-11-23
8
Medium Priority
?
160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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
 
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 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

770 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