August 21, 2003 at 1:15 pm
Hi,All!
I've run across a critical issue and can't find way out ((
I have a set of stored procedures which use dynamic SQL statements inside. Sometimes batch fails with error message "The charindex function requires 2 to 3 arguments." I run profiler and include ALL events but couldn't find any record with such error .
Can somebody help me ?
August 22, 2003 at 4:35 am
Isn't impossible to discover compilation errors when dynamic queries are used ?
August 24, 2003 at 8:17 am
Correct me f I am wrong.
As far as I know the dynamic statement fails before execution (due to syntax error).
So you can't find anything in the profiler.
Why don't you try to print the dynamic statements (or dump the dynamic statements into a table). when the sp fails you can check the statement.
G.R. Preethiviraj Kulasingham
Chief Technology Officer.
Softlogic Information Systems Limited,
14 De Fonseka Place,
Colombo 05.
Sri Lanka.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
August 24, 2003 at 2:39 pm
to Preethi:
It would be nice to do as you adviced. But there're some limitations:
--Batch is executed within transaction so any table changes would be rollbacked.
--The number of queries is too large(up to 200) so it's hard to dump ALL queries into some log(I will need to rewrite all the sps!).
Actual answer is to use sp_altermessage stored procedure so that 189 error(function requires arguments) would be logged. So topic is closed.Thanks to all for help.
August 24, 2003 at 4:22 pm
Preethi, execute the following with all profiler's events logged.
Quit interesting, obviously in SQL 2000 one can step through the SP if one can get the parameters.
Create Table TblComm(c Varchar(1000))
GO
Insert TblComm
Select 'Select charindex(Name) from SysObjects '
GO
create procedure test as
Declare @c varchar(1000)
Select @c=c from TblComm
Exec (@c)
Return
GO
Exec Test
GO
Drop procedure Test
go
Drop Table TblComm
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply