sp_helptext has bug - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

sp_helptext has bug

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-26 : 03:37:05

i have sql2k personal
when i execute sp_help sp_reconnect
i get this error

Server: Msg 536, Level 16, State 3, Procedure sp_helptextk, Line 143
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter passed to the substring function.

The statement has been terminated.

i copyed the script and created procdure of mine and debug it i found error in this line

INSERT #CommentText VALUES ( @LineId,
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))

@AddOnLen value is -1
how can i handle it

======================================
Ask to your self before u ask someone

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-04-26 : 07:24:18

Sp_help works fine for me. Where did you get sp_reconnect?

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-30 : 01:32:16

thanks

it works fine for all the procedure but only for a procedure it gives this error.....

======================================
Ask to your self before u ask someone

Nazim
A custom title

1408 Posts

Posted - 2002-04-30 : 04:44:45

khalik, nothing is wrong with sp_helptext .
its ur procedure which is erroneous.

you cant have a negative value as the length in a substring

select substring('SqlTeam',-1,1)
will return Null

select substring('SqlTeam',1,-1)
will return
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

--------------------------------------------------------------

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-30 : 05:41:07

quote:
you cant have a negative value as the length in a substring

thanks nazim

when i execute sp_helptext reconn_event

i get this

Server: Msg 536, Level 16, State 3, Procedure sp_helptext, Line 144
Invalid length parameter passed to the substring function.
The statement has been terminated.

for other procedures it works fine only in this procedure..
when i tried to debug... by creating another procedure..
i found that

INSERT #CommentText VALUES ( @LineId,
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))

@AddOnLen value is -1

======================================
Ask to your self before u ask someone

Nazim
A custom title

1408 Posts

Posted - 2002-04-30 : 08:32:14

khalik, you havent understood the point.

the sp which gives you that error has a invalid paramerter in substrin function. (i.e -ve value in lenth parameter). if you put a +ve value there. the error will goaway. and i dont understand what does a -ve signify there. wht do u want to do??

--------------------------------------------------------------

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-05-03 : 04:46:32

i tried out in diff server and i was able to get the sp_helptext for same procedure.

its problem with only on my server and and only with one procedure... and i check it my code there is no substrin function.

the procedure work well but only when execute sp_helptext it gives problem.

======================================
Ask to your self before u ask someone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources