Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gpsk7
Starting Member
4 Posts |
 Posted - 2005-09-26 : 22:21:34
|
| How can I retrieve all the records in a table whose length is greater then 8060 bytes?Cheers,gav. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-27 : 00:40:06
|
| I think the maximum length of a row is 8060 bytesWhen creating a table you will get warning that it exceeds 8060 bytesMadhivananFailing to plan is Planning to fail |
 |
|
|
gpsk7
Starting Member
4 Posts |
Posted - 2005-09-27 : 02:09:45
|
Basically the data is entered through code, so there are some records in the table which exceeds the limit of 8060 bytes which is causing problem when I try to update the records using the application only. I just want to retrieve all those records and delete them.quote:
Originally posted by madhivanan
I think the maximum length of a row is 8060 bytesWhen creating a table you will get warning that it exceeds 8060 bytesMadhivananFailing to plan is Planning to fail
|
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-09-27 : 07:09:35
|
| "Basically the data is entered through code, so there are some records in the table which exceeds the limit of 8060 bytes which is causing problem when I try to update the records using the application only. I just want to retrieve all those records and delete them."...There can't be ANY EXISTING data which ALREADY exceeds 8060...SQL just won't allow it...what you do have is SOME records which MAY AFTER PASSING THROUGH your application and BEING ATTEMPTED TO BE SAVED back into the database NEXT TIME have a problem. 2 approaches...have your application deal with (or prevent) the error....or else id potential rows.You possibly have some large TEXT or VARCHAR columns.Lookinto using the LEN or DATALEN functions to evaluate the size of each current column.Something along the likes of SELECT LEN(cola) + LEN(colb) + LEN(colc) + ..... FROM mytable order by 1 descwill come close.... |
|
|
|
gpsk7
Starting Member
4 Posts |
Posted - 2005-09-27 : 20:42:29
|
| I had tried using len or DataLength functions to sum up the length of all the fields in that table and the maximum record size it shows is 7986 bytes which means that I can feed 74 bytes of more data. But when I try to manually update that particular record even with 1 byte it shows me an error.Initially I got the feeling that there must be some field type for whom these functions doesn't work. But I am using 4 types of field viz. char, varchar, nvarchar and datetime and I have checked that these functions work fine with these types of fields.So is there any other place where these 74 bytes are being used which I am missing it while calculating the length of a record? cheers. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-27 : 20:55:18
|
| Before we go any further, it would help if you posted the structure of the table. It's a lot easier to troubleshoot when there are column names, types, and sizes to look at.LEN() returns the number of characters in a string, not bytes, and it does not work correctly on non-character columns. Ntext, nchar, and nvarchar use 2 bytes per character as well. You need to use DATALENGTH() on your columns to get an accurate length for the entire row. Also, text, ntext, and image columns are not included in the row size unless you've specified a TEXT_IN_ROW setting for the table. |
|
|
|
|
|
|
|
|
How to calculate length of the record