[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Updation Error

Posted on 2006-11-23
8
Medium Priority
?
161 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

650 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