Command object stops prematurely without error?

The following script works flawlessly when run via OSQL or in SQL Query
Analyzer.
However, when run via the ADO Command object, the outer loop Applications
table cursor seems to end prematurely before all records are processed,
particularly if there are more than about 4 records in the UCM_Applications
table.

The actual symptom is that not all records in the UCM_Indexes table
have had their DisplayOrder value set (they remain at a NULL value). It does
not raise an error. It does not seem to be related to the records themselves
as I can change the direction of the "Order by" clause and it successfully
processes records that didn't process in the other direction, although there
are still records in the UCM_Indexes table with NULL values in the
DisplayOrder fields. Is there an error in this approach?

This is not a stored procedure and there are no stored procedures involved.

-- Set order for index fields
Declare @AppID int,
@Order smallint,
@IdxID int

Declare cApps CURSOR for Select ApplicationID from UCM_Applications
Open cApps
Fetch NEXT from cApps into @AppID
While (@@FETCH_STATUS = 0)
BEGIN
Declare cIdxs CURSOR for Select IndexID from UCM_Indexes where
ApplicationID = @AppID order by IndexLevel
Open cIdxs
Fetch NEXT from cIdxs into @IdxID
Set @Order = 1
While (@@FETCH_STATUS = 0)
BEGIN
Update UCM_Indexes set DisplayOrder = @Order where current of cIdxs
Fetch NEXT from cIdxs into @IdxID
Set @Order = @Order + 1
END
close cIdxs
deallocate cIdxs

Fetch NEXT from cApps into @AppID
END
close cApps
deallocate cApps

The outer loop (cursor cApps) steps through every record in the
UCM_Applications table. It gets the ApplicationID from the table, then starts
an inner loop (cursor cIdxs) which steps through every record in the
UCM_Indexes table where the ApplicationID field matches the ApplicationID
retrieved in the outer loop.

As it steps through the UCM_Index table records for that Application, it
updates the "DisplayOrder" field with the value of a counter variable, then
increments the counter variable.

Once the end of the cursor is reached, the @@FETCH_STATUS variable will be
non-zero and it steps out of the loop and destroys the cIdxs cursor. At this
point the outer cursor tries to fetch the next record from the
UCM_Applications table. This process continues for each record in the
UCM_Applications table until it reaches the end of its cursor (@@FETCH_STATUS
variable non-zero), where it steps out of the outer loop and destroys the
cApps cursor.

At this point, every record in the UCM_Applications table and the
UCM_Indexes table should be processed. We know this because there is a
foreign key constraint from the UCM_Indexes table ApplicationID field into
the UCM_Applications table ApplicationID field, so there should be no orphan
records in the UCM_Indexes table.

Additional information:

Within ADO, it runs partially. If the number of records in the outer cursor
loop is small enough, it actually completes successfully via ADO as well. It
is only when there are a larger number of records in the outer cursor loop
that it seems to stop prematurely without raising an error.

The ADO Code:

' NOTE: strCmd is set to the SQL text noted in the original post...

Function ExecSQL(ByVal strCmd as String) as Boolean

On Error goto ErrHandler

Dim conData As ADODB.Connection
Dim cmdData As ADODB.Command
Dim strDSN as String

Set conData = New ADODB.Connection
Set cmdData = New ADODB.Command

strDSN = "MyDSN"

conData.ConnectionString = "DSN=" & strDSN & ";"
conData.CursorLocation = adUseClient
conData.Open

Set cmdData.ActiveConnection = conData
cmdData.CommandText = strCmd

' Execute
cmdData.Execute()

' loop
Do While cmdData.State = (adStateOpen + adStateExecuting)
DoEvents
Loop ' while executing

Set cmdData = Nothing
conData.Close
Set conData = Nothing

ExecSQL = True

Exit Function

ErrHandler:
MsgBox "ERROR - " & Err.Description

End Sub
--
Tom Hintz
.

Relevant Pages

  • RE: Oracle cursor help
    ... rids dbms_utility.uncl_array; ... where <your where clause> ... Subject: Re: Oracle cursor help ... exit the loop: */ ...
    (perl.dbi.users)
  • Re: Calling a SP inside a cursor loop..
    ... every loop iteration of the cursor. ... Fetch next From EffectiveDate_Cursor Into @FLD1,@FLD2 ... and If the Fetch stmt is below the begin Stmt, the loop iterations are ... the proper way to program a cursor loop is: ...
    (comp.databases.ms-sqlserver)
  • Re: => Trigger to split Trailer Loads
    ... I am using a Trigger and a cursor with a while loop, however the recursive behaviour of the cursor is causing me ... I can successfully copy an order from tblOrders to tblSplitOrders, duplicating order details and splitting the load into ... Enters LineSplit>0 ...
    (microsoft.public.sqlserver.mseq)
  • Re: X-windows: changing cursor from and AST ?
    ... > Since the loop is outside the X-events main loop, ... > the cursor to a busy cursor? ... it simply cancels the timer and then reverts ... > canceling AST delivery before you make a call to an X function (and ...
    (comp.os.vms)
  • Re: Cursor loop
    ... I've created a stored procedure that loops through a cursor, ... DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods ... The problem is that this loop only executes one time, ...
    (comp.databases.ms-sqlserver)