Please help

Hello everybody,
I have stored procedure that calls number of other stored procedures.  If I
run stored procedure from query analyzer it never fails.
However it fails from VB. And to be more specific, it does not fail, it
stops at certain point and exits (there is no error msg in sql Profiler)
I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
It goes to SP6 up to the curtain point and without error leaves and it does
not go at all to SP7 and leaves sp SP1 immediately.
It leaves SP6 every time in the same place, however if I add waitfor
(delay) somewhere on the top, it would leave stored procedure earlier
VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
command or connection timeout, both values =0
The same stored procedure never fails (with the same data) when running from
query analyzer
I can assure you that it is not permission problem, it is not query problem,
because with  added delay, it would leave stored procedure earlier

I mean without delay it fails after 8th query, with delay
it can leave stored procedure after 3rd query (for instance).
. It reference mdac2.5, also I tried to
reference 2.7 with service pack 1 without any luck


This stored procedure has few delete statements. If I would replace delete
with truncate -> stored procedure would not fail(100% successful). But I
can't do it, because the client who is running sp is not admin
Also the stored procedure sp6, that fails is using tempdb. If I increase
tempdb log file size, the failure rate decreases to around 50%.
I have solved the problem temporary by splitting sp1 into 2 stored procedues
Now sp1 calls  sp2,sp3,sp4,sp5
sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
from vb.
Please help

Any suggestions would be appreciated
thanks,
Diana M

Hi Diana,

I do not think that someone here can give a specific solution to that proble
m.
i think you should open profiler and performance monitor and try to find the
problem.
may be you have a dead lock there? or an open transaction and this is
explains the wait for delay helps it. i would have focuced on the filters of
text data at your trace adding event of sp:start and at the perfmon add
counters of cpu and try to see if there is a jump there when you call your s
p
from vb. also take a look at sysprocesses able and try and see if you have a
n
spid in waiting for a while.
hope it helps,
tomer

"Diana M" wrote:

> Hello everybody,
> I have stored procedure that calls number of other stored procedures.  If 
I
> run stored procedure from query analyzer it never fails.
> However it fails from VB. And to be more specific, it does not fail, it
> stops at certain point and exits (there is no error msg in sql Profiler)
>  I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
> It goes to SP6 up to the curtain point and without error leaves and it doe
s
> not go at all to SP7 and leaves sp SP1 immediately.
> It leaves SP6 every time in the same place, however if I add waitfor
> (delay) somewhere on the top, it would leave stored procedure earlier
> VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
> command or connection timeout, both values =0
> The same stored procedure never fails (with the same data) when running fr
om
> query analyzer
> I can assure you that it is not permission problem, it is not query proble
m,
>   because with  added delay, it would leave stored procedure earlier
>
> I mean without delay it fails after 8th query, with delay
> it can leave stored procedure after 3rd query (for instance).
> . It reference mdac2.5, also I tried to
> reference 2.7 with service pack 1 without any luck
>
>
> This stored procedure has few delete statements. If I would replace delete
> with truncate -> stored procedure would not fail(100% successful). But I
> can't do it, because the client who is running sp is not admin
> Also the stored procedure sp6, that fails is using tempdb. If I increase
> tempdb log file size, the failure rate decreases to around 50%.
>   I have solved the problem temporary by splitting sp1 into 2 stored proce
dues
>  Now sp1 calls  sp2,sp3,sp4,sp5
>  sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are calle
d
> from vb.
> Please help
>
> Any suggestions would be appreciated
> thanks,
> Diana M
>