Stored Procedure Issue - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Stored Procedure Issue

Author

Topic

inka
Starting Member

8 Posts

Posted - 2007-05-10 : 14:47:02

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?

Kristen
Test

22859 Posts

Posted - 2007-05-10 : 15:04:17

I don't get it, I'm reading this and thinking "why would you care"?

It would help if you could explain what you are trying to achieve here.

In general I look to being able to do a Set Based update - rather than a Cursor - in such circumstances, but that's going to require that the original data has the date details that are required for the Update!

Kristen

inka
Starting Member

8 Posts

Posted - 2007-05-10 : 15:22:09

Here is what I am trying to achieve. I have created a table and I am trying to update it with the values from another table.

I've tried to do it without the cursor just as you see it below:

update job_schedule_rep
set tlbkup = (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)

but I get the following error:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

How can I resolve this error?

Kristen
Test

22859 Posts

Posted - 2007-05-10 : 15:30:04

This:

set tlbkup = (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'

must return a SINGLE value to be useable at this point.

You can "force" that by using, say, MAX(...), or SELECT TOP 1 ... ORDER BY MyColumn - if that helps?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources