Solved

Updation Error

Posted on 2006-11-23
8
154 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

749 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