February 3, 2009 at 3:41 am
Hi guys,
I recently picked up a new project on one of old SQL 2000 servers that is proving to be quite difficult and time consuming. I'm looking for some shortcuts
One of our (developer built) reports recently started breaking for certain set of criteria - the long and the short of it is that the total potential data width of a single row exceeded 8060 bytes (by some way) and therefore the statement was bombing out at the order by clause
so my challenge is to find all the reports that may potentially come up with this error in the future if the total row width exceeds 8060 bytes
problems
- some of the reports don't necessarily have one big column that takes up the whole row width- there may be 8 Nvarchar(500) columns in there - (we have to use Nvarchar)
-there is dynamic SQL in some of the procs
-some of the procs use functions to return concatenated text data
I looked at things like SET FMTONLY to return just the metadata - but that's not much use (especially for procs)
i have sample proc calls for all of the procs i want to check, but obviously i can't guarantee i'll hit the right criteria to go over the 8060 limit. so i need the fixed width of the maximum possible output if all columns were populated to the max
Any help? is there system view i don't know of.........
MVDBA
February 4, 2009 at 8:59 am
Hi Michael,
I was attempting find a solution for you but after I ran a query against the system tables for a possible solution I realized it was probably a simplification of your problem. I will post it anyway to perhaps help get the creative juices flowing. My query was as follows:
SELECT o.id, o.name, o.type, SUM(c.length)
FROM sysobjects o
INNER JOIN syscolumns c ON o.id = c.id
WHERE o.type IN ('U', 'P')
GROUP BY o.id, o.name, o.type
ORDER BY o.name
/*This gives the aggregate totals of the lengths for the columns in a table and the variables in a stored procedure.*/
After reading your post again I realized you were indicating it was with you stored procedure's row size exceeding the 8060 not a particular table. I don't know how exactly you would do this, but you may want to try somehow comparing the syscolumns information from the database in question with the syscomments table. You would need to somehow parse the text column of the syscomments table trying to find the columns and variables used in the stored procedure then getting there sizes from syscolumns.
I know I probably didn't answer your question but maybe it will spark a thought and give you a jumpstart in the right direction. 🙂
February 4, 2009 at 9:05 am
nice try -
unfortunatly the output of a proc can be anyting and is not stored in syscomments or syscolumns....
i went that way to start with ..... i think this one is unfixable by lazy script!!! i think i'll have to eyeball them all and indetify by hand.
i looked at dbcc outputbuffer(x) but no help also tried the query plans to see if i could interrogate the output... hell no.
MVDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply