distal-attribute
distal-attribute
distal-attribute
distal-attribute

Stored procedure is trancated ( first 4000 characters )?

User, date Message
Written by stephen.lai
1 year ago
Category: General
2 posts since Tue, 09 Apr 13
I am using MSSQL.
When I click on stored procedure, HeidiSQL will call

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='xxx' AND ROUTINE_TYPE='FUNCTION' AND ROUTINE_CATALOG='xxx';

However, it will only show the first 4000 characters in the editor.

I would suggest use following statement to get the definition of the routine.

SELECT OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%xxx%'

Then it will get the entire routine.

Am I correct?
Written by ansgar
1 year ago
4958 posts since Fri, 07 Apr 06
That's a good hint. However, MSSQL seems to do things veeery version dependant. I modified quite some logic recently to get the procedure editor running on MSSQL, but everything I do seems to be specific to one or few server versions. So, is your suggestion valid for most other than your server versions as well?
Written by stephen.lai
1 year ago
2 posts since Tue, 09 Apr 13
I am not sure. Because I have MSSQL Server 11.0.3000.0 on hand only.

Here is the article on web which suggest to use the above statement:

http://beginsql.wordpress.com/2011/10/01/how-to-find-all-the-stored-procedures-having-a-given-text-in-it/
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
not working on MSSQL 8, OBJECT_DEFINITION is unknown
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
On MSSQL 8 I get this for ROUTINE_DEFINITION (that also have the char(4000) limitation) :

ROUTINE_DEFINITION = convert(nvarchar(4000),
(SELECT TOP 1 CASE WHEN encrypted = 1 THEN NULL ELSE com.text END
FROM syscomments com WHERE com.id=o.id AND com.number<=1 AND com.colid = 1))
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
By the way here is a screen taken side-by-side left is Seven 64bits, right is Windows XP, retrieved information is full of squared characters see screenshot http://i.imgur.com/Lg7yPSK.png

I also have MSSQL 7.0 and trying access stored procedures make HeidiSQL crash with exception message SQL :

Error(208) : Invalid object name 'information_schema.ROUTINES'
Written by itshoMoney, Euro
2 months ago
7 posts since Tue, 10 Jun 14
i'm using
client :
win7 7601 sp1 x64
HeidiSQL 8.3.0.4694 &

server:
win 7 7601 sp1 x64
SQL Server 2008 r2 10.50.1600.1 x64

and I can't see the whole stored procedures. it is being truncated - only 3510 characters appear unhappy
can you help me with this?
Written by ansgar
2 months ago
4958 posts since Fri, 07 Apr 06
It's obviously a limitation of the underlying ROUTINE_DEFINITION column in IS.ROUTINES. This query will show you the truncated data:

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='your_routine_name' AND ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA='dbo';


I am thinking I can fix that by casting to NText, but I am unsure. Any suggestions?
Written by ansgar
2 months ago
4958 posts since Fri, 07 Apr 06
http://msdn.microsoft.com/en-us/library/ms188757%28SQL.90%29.aspx says:

msdocs wrote: ROUTINE_DEFINITION
nvarchar(4000)
Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL.
To ensure you obtain the complete definition, query the OBJECT_DEFINITION function or the definition column in the sys.sql_modules catalog view.


Please check if this one is working for you:

SELECT * FROM sys.sql_modules WHERE object_id=OBJECT_ID('your_routine_name')

Written by itshoMoney, Euro
2 months ago
7 posts since Tue, 10 Jun 14
Hi.
yes, when i've used sys.sql_modules, it returned the full source. smile
Written by itshoMoney, Euro
2 months ago
7 posts since Tue, 10 Jun 14
and so does

exec sp_helptext 'your_routine_name'

Written by ansgar
2 months ago
4958 posts since Fri, 07 Apr 06
Thanks for feedback.

@dodfr: Could you please check which one also works on MSSQL 8. You just said which one is *not* working, and I have no MSSQL 8 for testing out.
Written by ansgar
2 months ago
4958 posts since Fri, 07 Apr 06
MSSQL 7.0 is from 1998, and MSSQL 8 is MSSQL 2000. Is there any chance I get access to some test servers? Probably some public servers on the net, which just throw away written data. I have no clue how to make Heidi more stable for these old MSSQL servers. Everytime I fix something for the newer versions, some user of an older version complains about incompatibility.
Written by itshoMoney, Euro
2 months ago
7 posts since Tue, 10 Jun 14
@ansgar - I have installed MSSQL 8.0 (2000) on a VM. How can I send you details privately ?
Written by ansgar
2 months ago
4958 posts since Fri, 07 Apr 06
Oh that's nice. You can send anything to my email address listed on the imprint page.

Written by ansgar
2 months ago
4958 posts since Fri, 07 Apr 06
Super. Just tested and committed that "exec helptext" approach, see r4786. Guess you can turn your server off for me now. Although it could be quite helpful for other issues as well. Anyway, thanks so far!
Written by itshoMoney, Euro
2 months ago
7 posts since Tue, 10 Jun 14
That's great. smile

thank you very much !
Written by itshoMoney, Euro
2 months ago
7 posts since Tue, 10 Jun 14
if anyone needs it, I've downloaded MSSQL Server 2000 RTM (8.00.194) evaluation from here:
http://download.microsoft.com/download/SQLSVR2000/Trial/2000/NT45/EN-US/SQLEVAL.exe (~330MB download file)
Written by ansgar
2 months ago
4958 posts since Fri, 07 Apr 06
Very usefull. Just installing on a virtual XP computer.
 

Please login to leave a reply, or register at first.