Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Stored procedure is trancated ( first 4000 characters )?

stephen.lai's profile image stephen.lai posted 7 years ago in General Permalink
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's profile image ansgar posted 7 years ago Permalink
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's profile image stephen.lai posted 7 years ago Permalink
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/
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
dodfr's profile image dodfr posted 7 years ago Permalink
not working on MSSQL 8, OBJECT_DEFINITION is unknown
dodfr's profile image dodfr posted 7 years ago Permalink
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's profile image dodfr posted 7 years ago Permalink
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's profile image itsho posted 6 years ago Permalink
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's profile image ansgar posted 6 years ago Permalink
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's profile image ansgar posted 6 years ago Permalink
http://msdn.microsoft.com/en-us/library/ms188757%28SQL.90%29.aspx says:

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's profile image itsho posted 6 years ago Permalink
Hi.
yes, when i've used sys.sql_modules, it returned the full source. smile
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
itsho's profile image itsho posted 6 years ago Permalink
and so does
exec sp_helptext 'your_routine_name'
ansgar's profile image ansgar posted 6 years ago Permalink
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's profile image ansgar posted 6 years ago Permalink
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's profile image itsho posted 6 years ago Permalink
@ansgar - I have installed MSSQL 8.0 (2000) on a VM. How can I send you details privately ?
ansgar's profile image ansgar posted 6 years ago Permalink
Oh that's nice. You can send anything to my email address listed on the imprint page.

Code modification/commit from ansgar.becker, 6 years ago, revision 4786
Fix truncation of routine body to 4000 characters, as a limitation of nvarchar(4000). See http://www.heidisql.com/forum.php?t=12495 . Fixes issue #3503.
ansgar's profile image ansgar posted 6 years ago Permalink
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's profile image itsho posted 6 years ago Permalink
That's great. smile

thank you very much !
itsho's profile image itsho posted 6 years ago Permalink
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's profile image ansgar posted 6 years ago Permalink
Very usefull. Just installing on a virtual XP computer.
fuchsyve's profile image fuchsyve posted 4 years ago Permalink
Hi,

It seems that SQLServer views with a large amount of charcaters are also truncated and are limited to 4000 characters in the editor.
I have a big view with a lot of "union".
Even if it is working when I validate the view, when reading back the content, the body is truncated.

ansgar's profile image ansgar posted 4 years ago Permalink
HeidiSQL still uses the following query to get the view body:
EXEC sp_helptext 'dbo.nameofview';

@fuchsyve: can you please verify that query truncates the view body for you, by running it in a query tab?
KjellH's profile image KjellH posted 2 weeks ago Permalink

Hi. It seems that some of the first part of the text from sp_helptext is lost when getting data from MS SQL. I've just recently started using Heidi SQL on MS SQL, so I don't know if it only relates to newer versions of Heidi SQL and/or newer versions of MS SQL.

Example from my SQL Server 2014 database: This is what Heidi SQL 10.2.0.5599 show as routine body: Description

This is a manual run of sp_helptext:

Description

There might be a logical explanation to this that I'm not aware of.

ansgar's profile image ansgar posted 2 weeks ago Permalink

Yes, this is also reported in issue #667

KjellH's profile image KjellH posted 1 week ago Permalink

Thank you - sorry I did not find this issue before posting my comment.

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