Execution stops .. why?

[koinkoin]

Hi,

I have the following code which executes a procedure. The procedure does a loop and process row by row. When I execute the procedure using Query Analyzer, everything works fine. However, when I execute the procedure using VB it processes only 18 rows (out of 180) and always stops after the second error. Can someone help me debug this issue?

Sub Main()

On Error GoTo ErrHandler

Dim myConn
Dim myCmd
Dim myRs

Dim myConnStr As String

myConnStr = "driver={SQL Server};server=APP;database=Rk;Trusted_Connection=Yes"

' instantiate the ADO objects
myCmd = CreateObject("ADODB.Command")
myRs = CreateObject("ADODB.Recordset")

myConn = CreateObject("ADODB.Connection")
With myConn
.Provider = "MSDASQL"
.CursorLocation = 3
.ConnectionString = myConnStr
.Open()
End With

myCmd.ActiveConnection = myConn
With myCmd
.CommandType = 4
.CommandText = "process_hierarchy" 'sArgs(2)
End With

' execute the command
myRs = myCmd.Execute

 

[zemp]

What are the errors? Where are they occuring?

zemp

 

[koinkoin]

Both errors occur in the procedure ..
Same error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 7: Inccorect syntax near 'account_name'.

What I do is that i flag these errors (by updating a field in the source table) and move to the next record. When I execute the same procedure in Query Analyzer, everything works fine.

Thanks.

 

[zemp]

ADO is slightly different than the Query analyzer. Post your SP.

zemp

 

[koinkoin]

Main procedure:
---------------
while 1 = 1
begin
fetch my_cursor into @count

select @last_error = @@ERROR
if @last_error <> 0 or @@FETCH_STATUS <> 0 break

begin transaction
-- merge the parent issuer into the table issuer
exec @last_error = test_proc @count
if @last_error <> 0 goto handle_error

-- everything went well, set the prcs_flag to 'S' (Success)
update source_table
set prcs_flag = 'S'
where row_id = @row_id

commit transaction
select @last_error = @@ERROR
if @last_error <> 0 break

-- handle the next record
continue

handle_error:
-- the record could not be properly processed, rollback the entire transaction
rollback transaction
select @msg = 'process_hierarchy->ERROR: Could not process record' + ', row_id:' + isnull(convert(varchar(10),@row_id),'null')
print @msg
-- set the prcs_flag to 'F' (Failure)
update source_table
set prcs_flag = 'F'
where row_id = @row_id
end

close security_cursor
deallocate security_cursor

return @last_error


test_proc procedure:
--------------------
select @sql = N'insert into process_hierarchy (count,last_upd_date,last_upd_user) values (@count,GETDATE(),SYSTEM_USER)'
exec sp_executesql @sql, N'@count int', @count
select @last_error = @@ERROR, @last_rowcount = @@ROWCOUNT

if @last_error != 0 goto handle_error

return 0

handle_error:
select @msg = 'ERROR: Could not update/insert investment hierarchy' + ', count:' + isnull(convert(varchar(10),@count),'null') + ', last_upd_date:' + convert(varchar,GETDATE(),120) + ', last_upd_user:' + SYSTEM_USER
print @msg
return @last_error

 

[zemp]

A bit beyond my SP knowledge. You might want to post in forum183.

zemp