Stored Procedure doesnt finish

[marsss]

Hello, i m having a weird problem and can t find a solution to fix it.

I have a stored procedure which run ok with my Query Analyser. It do take around 3-4 sec

When I call it from VB with my ADODB.Connection, it look like to stop in the midle of the process. Even if i setup commandtimout to 0, 30, 60, or more than that it do no difference. I dont even get any error from execution.

I did setup some debug option in my styored proc to see where it stop. It can stop at different point if i remove some part of it.

Weird thing is it dont even freeze up for 30 sec if i set up my timeout to 30 sec and put a big loop in my stored proc. I did check with VB debug if my timeout was ok right before call my exec and it s ok

According to me it look like to timeout some how since my proc run pretty smooth in my query analyser, but i can t understand why it stop in midle of execution before reach the timeout i setup.

Any tip will be appreciate

P.S.: This whole process been working well for a litle while, until i add up some more stuff in it

 

[SQLBill]

This whole process been working well for a litle while, until i add up some more stuff in it

Have you tested that 'more stuff' in Query Analyzer?

When you test your procedure in QA, do you run it as a script or as a procedure (using EXEC procname)?

Is the procedure supposed to return anything?

Do you have SET NOCOUNT ON in your procedure?
(with that off, it will return how many rows are affected and that could cause problems).

-SQLBill

Posting advice: FAQ481-4875

 

[marsss]

Thx SQLBill

didn t had SET NOCOUNT ON

That fixed my problem thx much

 

[SQLBill]

Glad to help.

-SQLBill

Posting advice: FAQ481-4875