problem querying system tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

problem querying system tables

Author

Topic

AlanS
Starting Member

28 Posts

Posted - 2007-09-04 : 15:40:03

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-04 : 15:54:25

That's just a display issue. In Query Analyzer, go to Tools..Options..Results tab, change the maximum characters per column field to 8000.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

AlanS
Starting Member

28 Posts

Posted - 2007-09-04 : 16:07:42

OK, that fixes the problem in Query Analyzer, but I'm actually using the code as part of a stored procedure that is invoked by a DTS package, and when that runs I still get the same truncation even after making the change in Query Analyzer. Any other ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-04 : 16:26:46

I don't have enough information about the DTS package to help you out. Please provide more details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

AlanS
Starting Member

28 Posts

Posted - 2007-09-04 : 16:31:20

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.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-04 : 16:43:19

they're blobs (i think that's what they're called) and you need to take that into consideration. BOL have a very good documentation on how to go about retrieving all of the text...

otherwise, much simpler is to use sp_helptext 'routine_name'

--------------------
keeping it simple...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-04 : 16:49:58

Check the transformation and verify what size you have the text column set to. If it's not a largish number, then change your query to convert(text, C.[text]) as [text] where it says C.[text]. Go to destination tab, click define columns, click populate from source and then click execute. You'll need to resetup the transformation for this mapping.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources