Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Can Stored Procedure behave different at times?

Author  Topic  rk903 Starting Member 1 Post Posted - 2008-12-09 : 21:23:22 Hi, I have a Transaction database at client site that inserts data say 3000 rows per minute. ( this is in sqlserver express) The application that runs locally collects the top 1000 records every minute and pushes to the server via a webservice. The webservice then inserts the recordsinto a temptable thru a BCP. The count of that updated rows is maintained in a log there is a job that call a Stored Procedure in the serverthat picks up the inserted rows from the temp table @ 5000 records to insert into an another table with additional processing. After the process the data is deleted from the Temp table . The Job is executed every minute. 90% of the times i get everything right but few times the data goes missing in the processed table. For Example in I see the client has sent 1000 records. The Log in the server says the 1000 records has been inserted in to the temp table. But the processed table has < 1000 records. The records are missed as one batch mostly in the middle of the 1000 records. Additional info : There is a cursor used in the Stored Procedure. There are no errors or exceptions. Any help to debug this will be of great help. Below is the stored procedure that processes every minute BEGIN -- begin try -- begin transaction declare @NoOfRecords bigint set @NoOfRecords = 5000 insert into UNITS(unitalias,fktransactionid,macid,fkuser,transdate,status,authorizedby,Tnumber,FKDistributorId,FKLocationId,fktypeid,FkUPToken) select top (@NoOfRecords) unitalias,transactionid,macid,Pid as fkuser,[timestamp] as transdate,status, null as authorizedby,Tnumber,distributorid, locationid, TType,FkUPToken from TEMPSTATUS, TRetrieve where transactionid=transid and unitid = cast(slno as varchar(20))order by id DECLARE c1 cursor FORWARD_ONLY FOR select top (@NoOfRecords) id, distributorid, locationid, TType, status from TEMPSTATUS order by id, distributorid, locationid, TType, status declare @id bigint declare @distributorid int declare @locationid int declare @TType int declare @status int OPEN c1 FETCH c1 INTO @id, @distributorid, @locationid, @TType, @status WHILE (@@fetch_status = 0) BEGIN if @status = 3 begin update unitI set UsedQuantity = UsedQuantity+1 where fkdistributorid = @distributorid and fklocationid = @locationid and fktypeid = @TType end if @status = 4 begin update unitI set validatedQuantity = validatedQuantity+1 where fkdistributorid = @distributorid and fklocationid = @locationid and fktypeid = @TType end if @status = 5 begin update unitI set VerifiedQuantity = VerifiedQuantity+1 where fkdistributorid = @distributorid and fklocationid = @locationid and fktypeid = @TType end FETCH c1 INTO @id, @distributorid, @locationid, @TType, @status END close c1 deallocate c1 delete from TEMPSTATUS where id in (select top (@NoOfRecords) id from TEMPSTATUS order by id) -- commit transaction -- end try -- begin catch -- rollback transaction -- EXEC RethrowError; -- end catch END