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

Stored procedure is trancated ( first 4000 characters )?

stephen.lai posted 2 years ago in General
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?
ansgar posted 2 years ago
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?
stephen.lai posted 2 years ago
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/
dodfr posted 2 years ago
not working on MSSQL 8, OBJECT_DEFINITION is unknown
dodfr posted 2 years ago
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))
dodfr posted 2 years ago
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'
itsho posted 4 months ago
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?
ansgar posted 4 months ago
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?
ansgar posted 4 months ago
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')

itsho posted 4 months ago
Hi.
yes, when i've used sys.sql_modules, it returned the full source. smile
itsho posted 4 months ago
and so does

exec sp_helptext 'your_routine_name'

ansgar posted 4 months ago
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.
ansgar posted 4 months ago
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.
itsho posted 4 months ago
@ansgar - I have installed MSSQL 8.0 (2000) on a VM. How can I send you details privately ?
ansgar posted 4 months ago
Oh that's nice. You can send anything to my email address listed on the imprint page.

ansgar posted 4 months ago
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!
itsho posted 4 months ago
That's great. smile

thank you very much !
itsho posted 4 months ago
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)
ansgar posted 4 months ago
Very usefull. Just installing on a virtual XP computer.

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