My curser is returning random rows!!!

[hlbyrdman]

I have created a stored procedure that picks data from one table and appends it to another. I used a curser so it would step through the table a row at a time. My problem is it sometimes duplicates a row or two and creates multiple copies of the same record. The procedure always returns the correct number of rows. So if it duplicates one it will delete another. I am at a loss as to what is going on. Help would be greatly appreciated.

 

[crystalized]

You should probably post your code, it will help people to help you trouble shoot your problem. Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[hlbyrdman]

I was hoping someone would say that..
CREATE PROCEDURE [update_JM_Updatetbl]
( @charge_date_1 [datetime],
@charge_time_2 [datetime],
@job_number_3 [int],
@job_name_4 [nvarchar](40),
@charge_date_5 [datetime],
@charge_time_6 [datetime],
@start_time_7 [datetime],
@end_time_8 [datetime],
@department_code_9 [varchar](50),
@cost_center_10 [varchar](16),
@employee_number_11 [int],
@work_classification_12 [varchar](3),
@task_code_13 [varchar](16),
@task_type_14 [varchar](16),
@task_name_15 [varchar](80),
@note_16 [varchar](254),
@qty_17 [numeric],
@unit_name_18 [varchar](20),
@unit_cost_19 [numeric],
@total_cost_20 [numeric])

AS UPDATE [SMS_1_0].[dbo].[JM_Updatetbl]

SET [job_number] = @job_number_3,
[job_name] = @job_name_4,
[charge_date] = @charge_date_5,
[charge_time] = @charge_time_6,
[start_time] = @start_time_7,
[end_time] = @end_time_8,
[department_code] = @department_code_9,
[cost_center] = @cost_center_10,
[employee_number] = @employee_number_11,
[work_classification] = @work_classification_12,
[task_code] = @task_code_13,
[task_type] = @task_type_14,
[task_name] = @task_name_15,
[note] = @note_16,
[qty] = @qty_17,
[unit_name] = @unit_name_18,
[unit_cost] = @unit_cost_19,
[total_cost] = @total_cost_20

WHERE
( [charge_date] = @charge_date_1 AND
[charge_time] = @charge_time_2)
GO

declare @row_id int
declare @JobNumber int
declare @JobName varchar (40)
declare @ChargeDate datetime
declare @ChargeTime datetime
declare @StartTime datetime
declare @EndTime datetime
declare @DepartmentCode varchar (16)
declare @CostCenterCode varchar (16)
declare @EmployeeNumber int
declare @WorkClassification varchar (3)
declare @TaskCode varchar (16)
declare @TaskType int
declare @TaskName varchar(80)
declare @note varchar (254)
declare @Qty decimal (15,2)
declare @UnitName varchar (20)
declare @UnitCost decimal (15,4)
declare @TotalCost decimal (15,2)

declare U_cursor Cursor for select job_number,job_name,charge_date,charge_time,start_time,end_time,department_code,cost_center,employee_number,
work_classification,task_code,task_type,task_name,note,qty,unit_name,unit_cost,total_cost from JM_Updatetbl
open U_cursor
fetch next from u_cursor into @JobNumber,@JobName,@ChargeDate,@ChargeTime,@StartTime,@EndTime,@DepartmentCode,@costCenterCode,@employeeNumber,
@WorkClassification,@TaskCode,@TaskType,@TaskName,@note,@Qty,@UnitName,@UnitCost,@TotalCost

While @@Fetch_status <>-1
Begin
fetch next from u_cursor into @JobNumber,@JobName,@ChargeDate,@ChargeTime,@StartTime,@EndTime,@DepartmentCode,@costCenterCode,@employeeNumber,
@WorkClassification,@TaskCode,@TaskType,@TaskName,@note,@Qty,@UnitName,@UnitCost,@TotalCost
exec sp_core_get_next_row_id 'job_charges', @row_id output
select @JobNumber=JM_Updatetbl.job_number, @JobName=JM_Updatetbl.job_name,@ChargeDate=JM_Updatetbl.charge_date,@ChargeTime=JM_Updatetbl.charge_time,@StartTime=JM_Updatetbl.start_time,
@EndTime=JM_Updatetbl.end_time,@DepartmentCode=JM_Updatetbl.department_code,@costCenterCode=JM_Updatetbl.Cost_center,
@employeeNumber=JM_Updatetbl.employee_number,@WorkClassification=JM_Updatetbl.work_classification,@TaskCode=tasks.task_code,
@TaskType=tasks.task_type,@TaskName=JM_Updatetbl.task_name,@note=JM_Updatetbl.note,@Qty=JM_Updatetbl.qty,@UnitName=tasks.unit_name,
@UnitCost=tasks.unit_cost,@TotalCost=(job_charges.unit_cost*JM_Updatetbl.qty)
FROM JM_Updatetbl join tasks on JM_Updatetbl.task_name = tasks.task_name,job_charges
insert job_charges (row_id,job_number,job_name,charge_date,charge_time,start_time,end_time,department_code,cost_center_code,employee_number,
work_classification,task_code,task_type,task_name,note,qty,unit_name,unit_cost,total_cost)
values (@row_id,@JobNumber, @JobName,@ChargeDate,@ChargeTime,@StartTime,@EndTime,@DepartmentCode,@CostCenterCode,@EmployeeNumber,
@workClassification,@taskCode,@TaskType,@TaskName,@note,@Qty,@UnitName,@UnitCost,@TotalCost)

end
close u_cursor
deallocate u_cursor
drop procedure update_JM_Updatetbl

 

[crystalized]

I am not the greatest cursor person in the world but my first suspect in strange problems with this would be the

exec sp_core_get_next_row_id 'job_charges'

line. Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[hlbyrdman]

First I inherited this data base, Job_charges includes row_id the new data is coming in from several other sources, so I need a way to generate new row_id's that are contiguose with the existing row_id's. I am not married to the cursor as a method for inserting data, if anyone has other alternatives I would be tickled to try them.

 

[hlbyrdman]

By inherited I am explaining that the database is what it is with 200,000 records already there so changing the way it relates data is not an option.