-
Stored Procedure Issue
Hi All,
I have this stored procedure:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure usp_job_schedule_rep
as
declare @tlbkup varchar(8)
declare @sqlstmt nvarchar(500)
declare tlbkup_cursor cursor
for
select top 100 percent Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), tl.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7) as 'Transaction Log Backup'
from
(select name, job_id, active_start_time
from sysjobschedules
where name like 'TLBkup%') as tl
join
(select name, job_id
from sysjobs
where name like 'TL Backup%') as sjt
on sjt.job_id = tl.job_id
where sjt.name not like '%Maintenance%'
order by sjt.name
open tlbkup_cursor
fetch next from tlbkup_cursor into @tlbkup
while @@fetch_status = 0
begin
print @tlbkup
update job_schedule_rep
set tlbkup = @tlbkup
FETCH NEXT FROM tlbkup_cursor INTO @tlbkup
END
CLOSE tlbkup_cursor
DEALLOCATE tlbkup_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
When I execute it, the update statement populates the tlbkup column with the last value from the cursor which is 1:40PM 19 times. How can I modify it so the tlbkup column is populated with all 19 different values from the cursor?
-
Do you get different values for
print @tlbkup
If all transaction log backups ran within a minute then you will get only 1:40PM
-
Yes, I get 19 different values.
-
Do you need where clause in your update statement? Or use join, otherwise you just update all rows 19 times.
-
I got it. Thanks for your help.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|