Crash when opening SQLServer 8.0 stored procedures

Hi,

Each time I try to open a stored procedure HeidiSQL crash with this error :

Erreur SQL (207): 'ROUTINE_COMMENT' : nom de colonne incorrect.

Here is complete crash report :

operating system : Windows 7 x64 Service Pack 1 build 7601
system language : French
system up time : 1 day 5 hours
program up time : 8 minutes 13 seconds
processors : 4x Intel(R) Core(TM) i5-3210M CPU @ 2.50GHz
physical memory : 4976/8065 MB (free/total)
free disk space : (C:) 78,92 GB
display mode : 1600x900, 32 bit
process id : $13d8
allocated memory : 71,26 MB
executable : heidisql.exe
exec. date/time : 2013-03-29 15:22
version : 7.0.0.4375
compiled with : Delphi XE
madExcept version : 3.0m beta 1
callstack crc : $b025dc71, $ca763814, $ca763814
exception number : 1
exception class : EDatabaseError
exception message : Erreur SQL (207): 'ROUTINE_COMMENT' : nom de colonne incorrect.

main thread ($5f8):
007238f9 heidisql.exe dbconnection 1697 +53 TAdoDBConnection.Query
7770012e ntdll.dll KiUserExceptionDispatcher
75efc408 KERNELBASE.dll RaiseException
0072633d heidisql.exe dbconnection 2165 +5 TDBConnection.GetResults
0072d153 heidisql.exe dbconnection 3533 +42 TDBConnection.ParseRoutineStructure
0074ea88 heidisql.exe routine_editor 163 +27 TfrmRoutineEditor.Init
00800dad heidisql.exe Main 8938 +17 TMainForm.PlaceObjectEditor
007fb507 heidisql.exe Main 7231 +67 TMainForm.DBtreeFocusChanged
00622aae heidisql.exe VirtualTrees TBaseVirtualTree.DoFocusChange
00626e4a heidisql.exe VirtualTrees TBaseVirtualTree.HandleMouseDown
004e97f4 heidisql.exe Controls TControl.DoMouseDown
0061ee4c heidisql.exe VirtualTrees TBaseVirtualTree.WMLButtonDown
0062b4ef heidisql.exe VirtualTrees TBaseVirtualTree.WndProc
004ed100 heidisql.exe Controls TWinControl.MainWndProc
004ab958 heidisql.exe Classes StdWndProc
74e47885 USER32.dll DispatchMessageW
00575ffb heidisql.exe Forms TApplication.ProcessMessage
0057603e heidisql.exe Forms TApplication.HandleMessage
00576369 heidisql.exe Forms TApplication.Run
00818be2 heidisql.exe heidisql 74 +22 initialization
750233a8 kernel32.dll BaseThreadInitThunk

Then, what's the name of ROUTINE_COMMENT in IS.ROUTINES on MSSQL 8?

There is no ROUTINE_COMMENT I see only ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_CATALOG.

and ROUTINE_BODY and ROUTINE_DEFINITION

There is also no SECURITY_TYPE and DEFINER column.

r4376 ignores errors due to non existent columns in IS.ROUTINES. Should be ok when we assume an empty string in such cases.

ok you did a SELECT * so no more problem but now if I click a stored procedure then on the right side I don't see the stored procedure code nor can edit it, the tabs "parameters" and "create code" are disabled/unselectable, the "Routine body" text area is empty.

The routine body area should be filled with the content of IS.ROUTINES.ROUTINE_DEFINITION. Is that column empty on your server?

there is no ROUTINE_DEFINITION but to retrieve procedure script manuelly I use :

sp_helptext 'dbo.MyStroredProcedure1'

ROUTINE_DEFINITION column is empty? That sounds like a bug in MS SQL, is it?

After some research, If I execute manually your standard request :

select * from INFORMATION_SCHEMA.ROUTINES where routine_name='MyStoredProcedure1' and ROUTINE_TYPE='PROCEDURE';

then I get a record that contain all datas including ROUTINE_DEFINITION, so may be it is the way you manage the query result that it is wrong ?

My mistake, your original request is wrong :

SELECT * FROM information_schema."ROUTINES" WHERE "ROUTINE_SCHEMA"='SDXArchiver' AND "ROUTINE_NAME"='dt_addtosourcecontrol_u' AND "ROUTINE_TYPE"='PROCEDURE';

ROUTINE_SCHEMA always contain 'dbo' and it is ROUTINE_CATALOG that contains 'SDXArchiver'

...so database name is in ROUTINE_CATALOG and ROUTINE_SCHEMA contain only 'dbo' value.

I also notice that you do not show the database sizes on left panel when you connect to server, you may use "EXEC sp_databases;" that will show you the list of all databases wich their size, for example :

"DATABASE_NAME";"DATABASE_SIZE";"REMARKS"
"master";"20160";""
"model";"1152";""
"msdb";"7424";""
"SDXArchiver";"2052224";""
"tempdb";"308992";""

size is KB so SDXArchiver is about 2GB.

sorry for my many posts but your forum do not permit post edition and by the way I never received any answer notification on my yahoo account.

No problem. Thanks in return for shedding light into the dark room of MS SQL development :)

r4381 should solve this, and should also show routine parameters.

Works fine !

...but tab "parameters" don't show any, here is a sample of stored procedure :

/*
** Drop one or all the associated properties of an object or an attribute
**
** dt_dropproperties objid, null or '' -- drop all properties of the object itself
** dt_dropproperties objid, property -- drop the property
*/
create procedure dbo.dt_droppropertiesbyid
@id int,
@property varchar(64)
as
set nocount on

if (@property is null) or (@property = '')
delete from dbo.dtproperties where objectid=@id
else
delete from dbo.dtproperties
where objectid=@id and property=@property

and beginning of an other one :

create proc dbo.dt_addtosourcecontrol_u
@vchSourceSafeINI nvarchar(255) = '',
@vchProjectName nvarchar(255) ='',
@vchComment nvarchar(255) ='',
@vchLoginName nvarchar(255) ='',
@vchPassword nvarchar(255) =''

as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId = 0

...
...
...
...
...

Your sample procedures both have two things which look like wrong code to me:
* parameters are not wrapped in parentheses. That's why Heidi does not detect them
* routine body is not wrapped in parentheses. That way Heidi also cannot detect the routine body. While in MySQL you can leave parentheses away if the body is a one-liner, I got SQL errors when trying to do that in MSSQL 10.

This has been taken out from Avaya telephony system database on which I am actually working so may be the engineers from Avaya did things wrong but the fact is that MSSQL 8.0 has no problem with such way of writing (and may be it was the right way for 8.0).

But if first filter fail to find sparameters then you may search for each occurence of @.*, and @.*\r\n between "create" and "as".