September 4, 2007 at 12:58 pm
I was using code similar to what appears below to extract the text of stored procedures and UDFs, and it was working perfectly until earlier today. Since making some minor changes (re-arranging the order of the fields in the SELECT clause and adding one new field there), it no longer works properly - the [text] field is now truncated after 330 characters - until this morning, the entire field (up to 4000 characters) was always displayed. What am I doing wrong?
SELECT O.[id], C.number, C.colid, O.[name], O.xtype, O.crdate,
ObjectType =
CASE
WHEN O.xtype = 'P' THEN 'Stored Procedure'
ELSE 'Function'
END,
LEN(C.[text]) AS LenText, C.[text]
FROM sysobjects AS O
INNER JOIN syscomments AS C
ON O.[id] = C.[id]
WHERE (O.xtype IN ('P', 'FN', 'IF', 'TF'))
AND (O.[name] NOT LIKE 'dt_%')
ORDER BY ObjectType DESC, O.[name], O.[id], C.number, C.colid
September 4, 2007 at 2:23 pm
with a copy/paste of your code, i get this error:
Server: Msg 1540, Level 16, State 1, Line 1
Cannot sort a row of size 8101, which is greater than the allowable maximum of 8094.
Lowell
September 4, 2007 at 2:27 pm
Hmm, that's odd. I was getting a similar error when I first started developing this code, but am not now. But it's still truncating the [text] field and I can't figure out why.
September 4, 2007 at 2:49 pm
truncating where? in query analyzer? QA has a famous display issue with large varchar columns, it might be the default value of 256 here:
Tools>>Options>>Results>>Maximum Characters Per Column>>change to 8000
if you are dumping it to a recordset or datatable, it should be fine.
Lowell
September 4, 2007 at 3:43 pm
I fixed the truncation in QA by changing the appropriate setting there. But I'm using the same code in a stored procedure called by a DTS package. The DTS package has a single data transformation task, which uses a local database connection as its source object and a plain text file as its destination object. The task simply copies all of the fields generated by the stored procedure (which consists of the T-SQL code I included here) to the text file. It runs without error, except that the field C.[text] is truncated at 330 characters. When I change the stored procedure and DTS package so that the output goes to a newly created SQL table rather than a text file, no truncation occurs. I may just stick with that, since I was just advised that all subsequent processing must be done within SQL Server, so there's no longer any reason to generate a text file. But I'd sure like to know why the text file generation worked yesterday but does not work today.
September 4, 2007 at 6:02 pm
The problem is that c.Text is an NVARCHAR column of 4000... don't forget that this is UniCode and contains 2 bytes per visible character so it's really 8k if the LEN is 4k. Take a look at the code below which "fixes" this problem... also note the OBJECTPROPERTY line to ignore objects that Microsoft incorrectly identifies as "user" objects... I also added a DATALENGTH column so you can see what the real length of c.Text is...
SELECT o.[ID],
c.Number,
c.ColID,
o.[Name],
o.XType,
o.CrDate,
ObjectType = CASE
WHEN o.XType = 'P' THEN 'Stored Procedure'
ELSE 'Function'
END,
LenText = LEN(c.[Text]),
LenReal = DATALENGTH(c.[Text]),
[Text] = CAST(c.[Text] AS VARCHAR(4000))
FROM dbo.SysObjects o
INNER JOIN dbo.SysComments c
ON o.[ID] = c.[ID]
WHERE o.XType IN ('P', 'FN', 'IF', 'TF')
AND OBJECTPROPERTY(o.ID,'IsMsShipped') = 0
ORDER BY ObjectType DESC,
o.[Name],
o.[ID],
c.Number,
c.ColID
--Jeff Moden
September 5, 2007 at 7:56 am
Thanks, Jeff!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply