Cursor in Job with raiserror - 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

SQL Server Administration (2000)

Cursor in Job with raiserror

Author

Topic

beekrishna
Starting Member

1 Post

Posted - 2007-03-16 : 04:26:03

Hi Everybody,

I have an interesting issue with one of the job.

I created a cursor which will process for 1000 records. And i am generating an error at 200th record. To generate an error, i used raiserror function with severiaty level 16.

But when i run through query analyser, eventhough raiserror is available it continues for 1000 records and processing. But When i run the same throguh job, it doesn't complete and it doesn't stop at 200th records. Below is the sample script. did anybody face the similar issue ? awaiting for the resolution.

find the code below

-- drop table Test_RPN_1
-- drop table Test_RPN_2

-- creating the table to hold 1000 records
create table master..Test_RPN_1 (a int)
go
-- fill the table Test_RPN_1
declare @i int
set @i = 0
while @i <> 1000
begin
insert into master..Test_RPN_1 select @i
set @i = @i + 1
end
go
--creating the table to store the values inside the cursor
create table master..Test_RPN_2 (a int)
go

use master
go

-- creating the procedure
CREATE procedure sp_test_rpn
as
--SET NOCOUNT ON
declare @i bigint
set @i = 0
truncate table master..Test_RPN_2

declare @a int
declare cur cursor for
select a from master..Test_RPN_1
open cur
fetch next from cur into @a
while @@fetch_status <> -1
begin
insert into master..Test_RPN_2 select @i
if @a = 100
begin
Raiserror('sdasdasd',16,1)
end
set @i = @i + 1
print @i
fetch next from cur into @a
end
close cur
deallocate cur
go

-- create the job

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'test_rpn')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''test_rpn'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'test_rpn'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'test_rpn', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'step1', @command = N'exec sp_test_rpn', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'D:\test_rpn', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

go

-- test through sp
exec sp_test_rpn
select * from Test_RPN_2

-- test through job
exec msdb..sp_start_job @job_name = 'test_rpn'
select * from Test_RPN_2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources