SQL Server Performance Forum – Threads Archive
Setting max row size
Probably a bit of a beginner question, but I was wondering if it was possible for a DBA to set the maximum rowsize for a server or db. If so how?The maximum rowsize for SQL Server is by default 8000 bytes (if you are not planning to use text / image columns. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Yeah I’m aware of the default max size. However I’ve got a client who’s having a small problem with one of my applications. A particular insert fails when they add too much text in a particular field. I thought I had narrowed the error down to them putting too much text in so the row was larger than the maximum row size. I put in some validation to stop this but they still seem to be having the same problem. I want to make sure that they haven#%92t somehow lowered their maximum row size, I couldn#%92t see a way. Just want to eliminate it as a possibility.
I don’t thonk there is a way to do that. What is the error recieved? Are you sure it’s not the application that is creating the problem? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
There isn’t an error reported (its an ASP application by the way), the insert simply fails without reporting back an error through ADO (while my application happily informs the user they’ve updated the record), you can see the error if you try the same insert in query analyser. I managed to reproduce the error on our version of the application after adding thousands of characters of text to the field(s) I mentioned. I was a bit suspicious at the time, as they would have had to dump essays worth of text into fields meant for automatic email messages. That seemed unlikely but was the only thing that re-produced the problem. Oh well thanks for your help I’ll keep looking, at least I eliminated it as the possible problem.
you say you reproduced the error in query analyser. Surely you recieved an error message at this point ?
What was the output?
Yes it was this error message:
Server: Msg 8152, Level 16, State 9, Procedure usp_siteAdminAction, Line 79
String or binary data would be truncated.
The statement has been terminated.
I’m sure this error occurs when the length of value entered into a column is longer than the max. length of the column. For instance inserting ‘SQL’ into a char(2) column. If you feel truncation of the data is fine and to avoid the error then turn off ANSI WARNINGS by using the following SET command: SET ANSI_WARNINGS OFF. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
As Satya said, this happens only when the insert is happening in a field having size less than the size of the data being inserted. Please note that this is applicable incase you are storing the data in some variable having less lenght as well. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Ah sorry guys false alert, I forgot that I had been mucking about with the field sizes in the database. I had quartered in length the fields in discussion hence that error, which I don#%92t think, was the original one. Sorry I just tried to recreate the error and copied and pasted it without thinking. Trying to do too many different things at once. I’ll set the fields back to their original sizes and try again. Something I just thought of though: When I first set up the table in question I set the two fields that seem to be problematic with a very high length something like varChar(7000). When this problem was reported to me I simply sent a update that added a JavaScript validation function that wouldn’t allow them to put in more than 2000 chars. However I didn’t give them an update script to run on the table to shorten the field lengths. Do the large field sizes, even if not fully utilised, take up extra space in the row? I changed it on our version of the DB without thinking that it might effect the result.
No if the fiels in question is Varchar, it won’t use the extra space.
BTW, What is the total length of data that you are trying to insert in the table? Is itmore than 8000 characters? If it is I guess SQL Server should give the same error. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
This is the table design: siteadminIDsmallint20
chat_availbit10
chat_newbit11
forum_availbit10
forum_allbit10
class_availbit10
class_selfbit10
online_selfbit10
reg_availbit10
reg_selfbit10
reg_defbit10
workflowbit10
esSignaturesVPbit11
password_periodint41
emailNewUservarchar20001
emailExistingUservarchar20001
emailNewUser_subjectvarchar2551
emailExistingUser_subjectvarchar2551
emailFailedAddressReturnvarchar2551
passwords_expirebit11
nt_loginbit11
esSignaturesPagesbit11
esSignaturesPubbit11
esSignaturesLPbit11
esSignaturesCoursePublishbit11
esSignaturesLPPublishbit11
esUsernamevarchar2551
esPasswordvarchar2551
esDatedatetime81 The two troublesome fields are "emailNewUser" and "emailExistingUser", this is from the latest version of our development DB. The clients DB which is having the problem is slightly different with those fields currently set to bit type as smallInts and the length of emailNewUser and emailExistingUser set to 7500. I’ve tested with the data they are putting in and it works fine on our db. They’re putting in only 650 or so chars in each of the problem fields. Its the second one in the table "emailExistingUser" into which the insert or update seems to fail (the sp does both first an insert for their records and then an update to the table that is referenced by the rest of the application) Gah!I don’t know I’m running out of ideas. Any help much appreciated
Did you try breaking the SP into parts and checking which of the process insert / update fails? IS there a trigger on any of the tables? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The sp fails on the update on the table I gave the design for. The insert is done into am identical but separate table (may seem a strange system but don’t blame me one of my colleagues did it in a rush) with the content of the first table (which only ever contains one row). After that the update is done on the first table with the data from a html form. I presume the insert would fail to if the system ever got that far but it never gets updated into the first table to be inserted into the second. There are no triggers on either table.
I still suspect some local variable truncating the data. Check for the same! Do LTRIM and RTRIM of the data coming from HTML form to truncate any leading and trailing spaces. Try debugging the SP and check the size of data at various points. If the size is always same as passed from HTML form and the table field size is greater than that, I have run out of ideas as well. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Which fields are being updated? All of them or only the ones you previously listed?(emailNewUser,emailExistingUser) My only on-the-fly thought is that if you are having problems inserting into the varchar fields and those fields do not contain searchable data, then perhaps try changing them to the TEXT data type. However, I would be examining other fields because those varchar fields are pretty big and should support most inserts, especially if the average length of those fields is 650 characters.
Oh dear, I finally caught up with the clients DBA and got him to send me down a copy of the DB. Turns out some Muppet had set the field length for the troublesome field to 255. I thought this would produce an error in ADO (always seems to on our development servers) but apparently not. Anyway thanks for your help and sorry it was a bit of a waste of time.
]]>