January 9, 2004 at 2:02 pm
Hi All,
We've run into a curious problem and are looking for some way to track when a stroed procedure starts execution and stops execution.
I know through a trace it can be done but is there an easier debug style of something like a setting or a system stored proc that can be turned on and off.
The basics:
SQL 2000 Enterprise SP3
A cast in a print statement is causing the sp to not complete but it's not raising an error back. It's like it just stops executing.
print "@intDataChangeMode=" + cast(@intDataChangeMode as char)
The more frustrating part is that if the sp is ran on it own from query analyzer it always works. If we walk through the VB 6 code calling it, it always works. But letting run from the web pages (under MTS) it dies.
If anyone has any light to shed on a way to track the start and stop of a stored procedure (over 2500 sps so looking for more than adding prints or writes to a table) or why SQL would be behaving this way it would be greatly appreciated.
Thanks
K
January 9, 2004 at 2:44 pm
What if you split this into
select @newvar = "@intDataChangeMode=" + cast(@intDataChangeMode as char)
print @newvar
Does it still fail? I'm curious to see if you can trace one execution under MTS with Profiler and catch every statement start and end and see it fail? If no error is returned, then does it really fail or just hang? Control passes back to the calling routine under MTS?
January 9, 2004 at 3:03 pm
We'll try that.
Control is passed back to the app and it appears to be successful. But not all statements in the procedure were ran.
In trying to track this down we put insert statements to write a record to a log table at each step. It would write the entries until the print statment then no more entries but the web app would not error or respond different in any way. THe user would would carry on but then 2 pages later the user sees that the changes were only partially there.
We could reproduce the behaviour over and over.
I've never seen SQL behave inconsistantly like this.
K
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply