Why does my curser have errors

[hlbyrdman]

I have posted this before and recieved no help. I here rephrase the question and pared down the code. Hopefully I will get it right and someone will be able to help.

my task is to move data from MS Access into SQL. I created a DTS package which creates a new table in SQL filled with the Access data. I then need to update some existing SQL tables. Since I want it to happen programatically I create a SP to move the data to other tables. The other tables use row id's so I extract the last row id used and insert it along with each row. I also must pickup some data from other existing SQL tables and include it. The problem is the proc (?) will delete some rows of data and duplicate others. Always returning the correct number of rows of data, just with errors.

CREATE PROCEDURE update_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],
@task_code_13 [varchar](16),
@task_type_14 [varchar](16),
@task_name_15 [varchar](80))

AS UPDATE [SMS_1_0].[dbo].[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,
[task_code] = @task_code_13,
[task_type] = @task_type_14,
[task_name] = @task_name_15

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 @TaskCode varchar (16)
declare @TaskType int
declare @TaskName varchar(80)

declare U_cursor Cursor for select job_number,job_name,charge_date,charge_time,start_time,end_time,task_code,task_type,task_name from Updatetbl
open U_cursor
fetch next from u_cursor into @JobNumber,@JobName,@ChargeDate,@ChargeTime,@StartTime,@endTime,@TaskCode,@TaskType,@TaskName

While @@Fetch_status <>-1

Begin
fetch next from u_cursor into @JobNumber,@JobName,@ChargeDate,@ChargeTime,@StartTime,@endTime,@TaskCode,@TaskType,@TaskName

exec sp_core_get_next_row_id 'job_charges', @row_id output

select @JobNumber=Updatetbl.Job_Number, @JobName=Updatetbl.job_name,@ChargeDate=Updatetbl.charge_date,@ChargeTime=Updatetbl.charge_time,@StartTime=Updatetbl.start_time,
@endTime=Updatetbl.end_time,@TaskCode=tasks.task_code,@TaskType=tasks.task_type,@TaskName=Updatetbl.task_name
FROM Updatetbl join tasks on Updatetbl.task_name = tasks.task_name,job_charges
order by Updatetbl.Job_Number
insert into job_charges (row_id,job_number,job_name,charge_date,charge_time,start_time,end_time,task_code,task_type,task_name)
values (@row_id,@JobNumber, @JobName,@ChargeDate,@ChargeTime,@StartTime,@endTime,@taskCode,@TaskType,@TaskName)

end
close u_cursor
deallocate u_cursor
drop procedure update_Updatetbl

 

[branko]

Hi,

I don't have much time, not enough to study your code.
What I did notice quickly is that you put the fetch next in the first line of your while-loop.
This way you are skipping the first record, that you already fetched right after opening the cursor.
I think it is better to put the fetch at the end of your while-loop.

good luck

Branko

 

[tlbroadbent]

1) Why are you using a cursor and returning all the values from the table when you don't utilize those values later? The overwrite the values in the Select statment.

2) The job_charges table is included in the Select statement prior to the insert. There is no criteria defined for the joining the that table to the other two tables. This statement will most likely produce errors because it it will return multiple rows when job_charges contains multiple rows. In addition, no columns are selected from the table.

3) I don't see any execution of update_Updatetbl. The code creates the SP and later Drops it. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[hlbyrdman]

Thank you,
It turns out I had a combination of errors. In an earlier version I was trying to do this without a curser, and neglected to remove the select statement when I shifted gears. It didn't make it fail but it was VERY slow. I had to rewrite the select statement. To only pick up the task code from [tasks]. I also misunderstood the &quot;@@while fetch_status <>-1&quot; I was thinking It was standing ready before the first row so I moved the fetch next to after the insert.
Now it works, 100 records in 3 seconds correct instead of 100 records in 5 min. wrong.
Thanks for the help
Lee Byrd