Returning the length and IDs of recordsets

  • 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.

  • Try this:

    SELECT MAX(LEN(isnull(COLUMN1,''))+LEN(isnull(COLUMN2,''))+LEN(isnull(COLUMN3,''))) FROM TABLE1

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Would you please post your DDL, and the statements you are using to alter the columns.

    Thanks

    Mike

  • 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

  • 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.

  • 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.

  • 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