Stored procedure is trancated ( first 4000 characters )?
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.
WHERE OBJECT_DEFINITION(object_id) LIKE '%xxx%'
Then it will get the entire routine.
Am I correct?
Here is the article on web which suggest to use the above statement:
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))
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'
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?
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')
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.
http://download.microsoft.com/download/SQLSVR2000/Trial/2000/NT45/EN-US/SQLEVAL.exe (~330MB download file)
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.
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:
This is a manual run of sp_helptext:
There might be a logical explanation to this that I'm not aware of.
Please login to leave a reply, or register at first.