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

Crash when opening SQLServer 8.0 stored procedures

User, date Message
Written by dodfr
1 year ago
Category: General
46 posts since Mon, 24 Sep 12
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
Written by ansgar
1 year ago
4793 posts since Fri, 07 Apr 06
Then, what's the name of ROUTINE_COMMENT in IS.ROUTINES on MSSQL 8?
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
There is no ROUTINE_COMMENT I see only ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_CATALOG.
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
and ROUTINE_BODY and ROUTINE_DEFINITION
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
There is also no SECURITY_TYPE and DEFINER column.
Written by ansgar
1 year ago
4793 posts since Fri, 07 Apr 06
r4376 ignores errors due to non existent columns in IS.ROUTINES. Should be ok when we assume an empty string in such cases.
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
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.
Written by ansgar
1 year ago
4793 posts since Fri, 07 Apr 06
The routine body area should be filled with the content of IS.ROUTINES.ROUTINE_DEFINITION. Is that column empty on your server?
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
there is no ROUTINE_DEFINITION but to retrieve procedure script manuelly I use :

sp_helptext 'dbo.MyStroredProcedure1'
Written by ansgar
1 year ago
4793 posts since Fri, 07 Apr 06
ROUTINE_DEFINITION column is empty? That sounds like a bug in MS SQL, is it?
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
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 ?

Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
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'
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
...so database name is in ROUTINE_CATALOG and ROUTINE_SCHEMA contain only 'dbo' value.
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
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";""



Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
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.
Written by ansgar
1 year ago
4793 posts since Fri, 07 Apr 06
No problem. Thanks in return for shedding light into the dark room of MS SQL development :)
Written by ansgar
1 year ago
4793 posts since Fri, 07 Apr 06
r4381 should solve this, and should also show routine parameters.
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
Works fine !
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
...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

Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
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

...
...
...
...
...
Written by ansgar
1 year ago
4793 posts since Fri, 07 Apr 06
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.
Written by dodfr
1 year ago
46 posts since Mon, 24 Sep 12
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".
 

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