SQL server procedure gets cut off before completion

[Neil Mondal]

I have run into the following problem: I am trying to call a SQL Server stored procedure from VB but the
procedure only handles a few records and then just stops. I have tried extending the value of the 'commandtimeout' property of the ADODB command that executes the stored procedure, and even tried eliminating it entirely, but these changes made no difference. The stored procedure runs fine when run directly in SQL Server.
Does anyone know what the problem might be?

[Neil Mondal]

Hi BG,
I apologise for not replying earlier - I had problems with Netscape. I don't think I should post a copy of my stored procedure on the bulletin board because it is over 3000 lines long! I have given a brief description of the pseudocode below:

CREATE PROCEDURE Attend
(parameters)
AS
DECLARE
(variable names)

CREATE table #tblAttend
( field1, ... fieldN)

CREATE unique index idx_attend_key on #tblAttend(field1)

/*The select statement immediately below is repeated for 5 more temp tables, #temptableB - F*/
SELECT distinct (field names)
into
#temptableA
from
table1, ... tableN
where
(selection criteria)


DECLARE cursAttend cursor static for
SELECT #tblAttend.field1, ... fieldN
from #tblAttend

OPEN cursAttend

FETCH next from cursAttend into @variable1, ... @variableN

WHILE (@@fetch_status =0)
BEGIN
-- Debug, show field value.
PRINT 'Field1 is: ' + @variable1

FETCH next from cursAttend into @variable1, ... @variableN
END

CLOSE cursAttend
DEALLOCATE cursAttend


There is more code in the stored procedure, but
the part that gets cut off is the WHILE loop just above (it cycles halfway through the loop then just stops).
Furthermore, the cutoff-point seems arbitrary - sometimes the program gets a bit further than halfway, sometimes not so far. This made me think the problem was a timeout. But when I purposely introduced a 10 second-delay in the program after the variable declarations, then the VB front end (that calls the stored procedure) waited for those 10 seconds before stopping in more or less the same place (in the middle of the WHILE loop).
Any ideas you have are welcome!

[kingd9]

I just loaded the service pack 4 for VB and stored procedures that ran fine now get connection timeout. Just wondering if you or anyone else is having similar problems

[JHausmann]

Originally posted by kingd9
I just loaded the service pack 4 for VB and stored procedures that ran fine now get connection timeout. Just wondering if you or anyone else is having similar problems

If you're using ADO you can specify the connection timeout value (in seconds, I believe).

dim adoConn as new adodb.connection
adoConn.ConnectionTimeout = 7200 ' 2 hrs
adoConn.CommandTimeout=1800 '30 mins

[kingd9]

Yes, that's what we had to do, its just odd that it times out at a place where it never did before, other than that the application runs faster. It uses alot of flex grids and ado controls and has intensive inserts into a SQL db. We only get the improvement if we reference ADO 2.5, so I don't know if it was the SP4 or the ADO 2.5, which installs with SP4.

[Neil Mondal]

Just in case anyone was wondering, I solved the problem - the stored procedure was getting cut off when called from VB because of all the debug statements left inside it! It was due to SELECT * FROM statements and (to a lesser extent) PRINT statements - which apparently slow down SQL server considerably.