PLS HELP! Problem with column width

Hi,
We have a "bad" table in our database - sum of all 30 field widths
exceeds 8000 bytes. It still works fine unless all fields are filled
completely, and that's what happened to one of our clients. Now he can't
do any updates/inserts to the table.
I need to find which row created the problem, so does anybody know how
to get the real length of the row in a table (i mean length of actual
data) other that doing "select len(field1) + len(field2) + etc. from tbl"?
Any ideas are highly appreciated!
Thank you,
MuZZyUse LATALENGTH instead of LEN. And, you can generate that command,if you don
't want to type the
columns:
USE pubs
DECLARE @.tbl sysname, @.sql nvarchar(3000), @.crlf char(2)
SET @.sql = ''
SET @.crlf = CHAR(13) + CHAR(10)
SET @.tbl = 'titles'
SET @.sql = @.sql + 'SELECT TOP 10'
SELECT @.sql = @.sql + @.crlf + ' ISNULL(DATALENGTH(' + name + '), 0) + '
FROM syscolumns
WHERE id = OBJECT_ID(@.tbl)
SET @.sql = LEFT(@.sql, LEN(@.sql) -1)
SET @.sql = @.sql + @.crlf + ' AS theLength'
SET @.sql = @.sql + @.crlf + 'FROM ' + @.tbl
SET @.sql = @.sql + @.crlf + 'ORDER BY theLength DESC'
PRINT @.sql
Edit the sql command printed to add the primary or natural key column and ex
ecute it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MuZZy" <tnr@.newsgroups.nospam> wrote in message news:%23VBxDhnWGHA.4768@.TK2MSFTNGP05.phx.g
bl...
> Hi,
> We have a "bad" table in our database - sum of all 30 field widths exceeds
8000 bytes. It still
> works fine unless all fields are filled completely, and that's what happen
ed to one of our
> clients. Now he can't do any updates/inserts to the table.
> I need to find which row created the problem, so does anybody know how to
get the real length of
> the row in a table (i mean length of actual data) other that doing "select
len(field1) +
> len(field2) + etc. from tbl"?
> Any ideas are highly appreciated!
> Thank you,
> MuZZy