OK, I'm confused...

[DaveMaxwell]

I've asked others here at work and they don't see the error, so I'll ask here. I've got a SQL query which is throwing me an error I don't understand.

Basically, what I'm doing is looping through all of the tables in a database looking for remnants of a scripting attack in the (n)char, (n)varchar and (n)text fields. As I'm looping through, I'm writing the results out of a temp table so we can report on them.

The problem I'm running into is when I'm hitting some of the text columns in the database. Not all - just some...

Here is the SQL statement for one of the text columns:

INSERT INTO zzz_SearchResults(TableName
       , PrimaryKeyColumn
       , PrimaryKeyValue
       , ColumnName
       , ColumnValue) 
      SELECT 'tbl_quest'
    , 'questionID'
    , [questionID]
    , 'critque'
    , CONVERT(VARCHAR(4000), [critque]) 
        FROM [tbl_quest] (NOLOCK) WHERE [critque] LIKE '%<SCRIPT%'

And the error I'm getting is:
Msg 511, Level 16, State 0, Line 1
Cannot create a row of size 8073 which is greater than the allowable maximum of 8060.
The statement has been terminated.

The crtque field is the text field and the zzz_SearchResults table structure is

• ResultID - identity
• TableName - nvarchar(50)
• PrimaryKeyColumn - nvarchar(50)
• PrimaryKeyValue - int
• ColumnName - nvarchar(50)
• ColumnValue - nvarchar(4000)

This version uses the CONVERT function to convert the field from text to varchar, but I've also tried CAST and SUBSTRING and I'm getting the error with all of these methods.

What blatantly obvious piece of the puzzle am I missing?

[swampBoogie]

The data that you insert into the columnValue column may potentially contain 8000 bytes and together with the other columns that may exceed the maximum record length for a table. Either use a text column or insert a substring. Your other columns will at most take 320 bytes which leaves room for 7740/2=3870 characters in the columnValue column.

[DaveMaxwell]

nvarchar vs varchar....doh! nvarchar takes up double the space.....that cleared it up - though I knew that and forgot all about it....

Where's that wall? Oh, wait, there it is.



<goes of muttering to self about being stupid>