August 27, 2005 at 3:27 am
Hi there
Was trying to update various columns from varchar(2000) to nvarchar(2000) in a table and received a "...exceeds 8060 byte limit." error message.
I know you can't have more then 8060 bytes of data in a recordset so I need to retrieve the rows that contain the most data from the table.
I tried:
SELECT MAX(LEN(COLUMN1)+LEN(COLUMN2)+LEN(COLUMN3)) FROM TABLE1
This works if the columns don't contain any NULL values. As soon as a column contains NULL then the result is NULL even if I set CONCAT_NULL_YIELDS_NULL to FALSE.
Anybody have an idea how to retrieve the biggest recordset from a table if some of the columns contain NULL values? (It's a table with 40+ columns)
Cheers!
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
August 27, 2005 at 4:03 am
Try this:
SELECT MAX(LEN(isnull(COLUMN1,''))+LEN(isnull(COLUMN2,''))+LEN(isnull(COLUMN3,''))) FROM TABLE1
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 27, 2005 at 4:09 am
Would you please post your DDL, and the statements you are using to alter the columns.
Thanks
Mike
August 27, 2005 at 4:14 am
You might also want to check BOL for information on varchar and nvarchar. nVarchar has a max length of 4000 characters as it requires 2 bytes for each character. If your varchar(2000) will require nvarchar(4000).
HTH
Mike
August 29, 2005 at 1:35 am
Just what the doctor ordered.
Cheers!
I am now running the script like this:
SELECT * FROM TABLE1
WHERE LEN(isnull(COLUMN1,''))+LEN(isnull(COLUMN2,''))+LEN(isnull(COLUMN3,'')) in (
SELECT MAX(LEN(isnull(COLUMN1,''))+LEN(isnull(COLUMN2,''))+LEN(isnull(COLUMN3,''))) FROM TABLE1
)
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
August 29, 2005 at 1:45 am
I don't think the DDL for an 80 columns table would be very interesting. I know myself that it's a freaky table.
Thanks anyway.
I've done the ADMIN courses for MS-SQL and did realize that converting from varchar to nvarchar would require double the space. It was all about finding that one recordset or the recordsets that had lengths above 4030 bytes.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
August 29, 2005 at 4:03 am
Yep an 80 column table would not be very interesting but I was thinking that the data type of some of the columns would allow you to eliminate them from your select statement.
As your original post stated that you wanted to convert a char(2000) to a varchar(2000) I was not sure that you realized that the varchar required 2 bytes for every byte of a char data type.
Glad you solved the problem.
Mike
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply