Long Running Stored Proc in VB - HELP

[coolphilboy]

I'm calling an SQL Server SP in VB. The procedure runs for about 30 mins. in the Query Analyzer. But when I call the procedure in VB it seems that the Procedure is terminated abnormally without any warning from VB.

I've set the ConnectionTimeout=0 and the CommandTimeout=0, but it doesn't seem to work. The same procedure is working normally when executed in the Query Analyzer. Is there something i've missed to set? PLEASE HELP !!!

 

[MarkSweetland]

Try "Set nocount ON" in the beginning of the stored procedure to keep any extraneous messages from popping up as the SP is executing which maybe confusing the VB portion..

Mark

 

[coolphilboy]

Thanks for the suggestion Mark, but the "SET NOCOUNT ON" has already been set.

Do you have other suggestions? TIA

 

[MarkSweetland]

What version of ADO are you referencing, and how are you calling the SP? Verison 2.5 and prior have a bug in the Connection object's Commandtimeout property. A Commandobject does not use the Connection object's setting when referenced. Whereas setting the Commandtimeout to 0 in the command object directly does cause the infinite wait:

Set MyConn = CreateObject("ADODB.Connection&quot
MyConn.ConnectionTimeout = 0
MyConn.CommandTimeout = 0 ' *** THIS HAS
*** NO EFFECT BECAUSE OF THE BUG ***
MyConn.Open ConStr, UserId, PassWord

Set cmdTemp = CreateObject("ADODB.Command&quot
' *** T H I S L I N E W A S A D D E D ***
cmdTemp.CommandTimeout = 0 ' *** SETTING IT TO ZERO CAUSES IT TO WAIT
FOREVER ***

This bug was corrected in MDAC 2.6.

Mark