This is my first try with SQL Server's JOB, and I have a strange problem.

If I create a JOB with only one step, everithing works fine.
But when I create a job with 2 or more step... SQL Server JOB Agents Crash

Windows event viewer report:
==============================
==============================

Exception 5 caught at line 173 of file ..\src\refreshr.c. SQLServerAgent
initiating self-termination.
==============================
==============================


SQL Server JOB Agents errors log report:
==============================
==============================

[LOG] Exception 5 caught at line 173 of file ..\src\refreshr.c.
SQLServerAgent initiating self-termination
[240] Killed 1 thread of engine after 30 second wait
[311] Thread 'JobInvocationEngine' (ID 1584) is running
==============================
==============================

(I traduced the last 2 error, there was in Italian)


It is strange, because it ever crash, no matter what i put inside the job (I
ever use T-SQL job)
I tried to create 2 subsequent identical step, one alone works, 2 or more
crash.
I ever tried job like
select 'pluto'
one works, 2 or more crash...

Where is the problem?

System: Windows 2000 Server SP2, IIS 5.0, SQL Server 2000 SP2, MDAC 2.7, no
mapi mail acount

Now I report the DDL code of my tow JOB, the firs is a simple one-step job,
thar work fine, the second is a tow step job, that crash my SQL Agent :-(

You'll probably need to change the owner of the job with a valid acount on
you server

==============================
==============================
===============
-- This JOB WORK FINE

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database
Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'JOB Test that run
fine') > 0
PRINT N'Il processo "JOB Test that run fine" esiste gią e pertanto non
verrą sostituito.'
ELSE
BEGIN

-- Aggiungi processo
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
@job_name = N'JOB Test that run fine', @owner_login_name = N'max',
@description = N'Nessuna descrizione disponibile.', @category_name =
N'Database Maintenance', @enabled = 1, @notify_level_email = 0,
@notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog =
3, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Aggiungi passaggi di processo
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
1, @step_name = N'the only one step', @command = N'seelct ''pluto''',
@database_name = N'tempdb', @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'', @on_success_step_id = 0,
@on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 1
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

-- Aggiungi server target
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:
==============================
==============================
===============
==============================
==============================
===============

==============================
==============================
===============
==============================
==============================
===============
-- This JOB CRASH

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database
Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'JOB Test that
crash') > 0
PRINT N'Il processo "JOB Test that crash" esiste gią e pertanto non verrą
sostituito.'
ELSE
BEGIN

-- Aggiungi processo
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
@job_name = N'JOB Test that crash', @owner_login_name = N'max', @description
= N'Nessuna descrizione disponibile.', @category_name = N'Database
Maintenance', @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

-- Aggiungi passaggi di processo
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
1, @step_name = N'The first step', @command = N'select ''pluto''',
@database_name = N'tempdb', @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'', @on_success_step_id = 0,
@on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
2, @step_name = N'Second step', @command = N'select ''pippo''',
@database_name = N'tempdb', @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'', @on_success_step_id = 0,
@on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 1
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

-- Aggiungi server target
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:
==============================
==============================
===============


Thanks, Max

Report this post to a moderator

See reply in sqlserver.Tools

Cheers
Jasper Smith

"MegaMax" <zzzzz@sssss.com> wrote in message
news:afhqaj$eh0ir$2@ID-141456.news.dfncis.de...
> This is my first try with SQL Server's JOB, and I have a strange problem.
>
> If I create a JOB with only one step, everithing works fine.
> But when I create a job with 2 or more step... SQL Server JOB Agents Crash
>
> Windows event viewer report:
> ==============================
==============================

> Exception 5 caught at line 173 of file ..\src\refreshr.c. SQLServerAgent
> initiating self-termination.
> ==============================
==============================

>
> SQL Server JOB Agents errors log report:
> ==============================
==============================

> [LOG] Exception 5 caught at line 173 of file ..\src\refreshr.c.
> SQLServerAgent initiating self-termination
> [240] Killed 1 thread of engine after 30 second wait
> [311] Thread 'JobInvocationEngine' (ID 1584) is running
> ==============================
==============================

> (I traduced the last 2 error, there was in Italian)
>
>
> It is strange, because it ever crash, no matter what i put inside the job
(I
> ever use T-SQL job)
> I tried to create 2 subsequent identical step, one alone works, 2 or more
> crash.
> I ever tried job like
> select 'pluto'
> one works, 2 or more crash...
>
> Where is the problem?
>
> System: Windows 2000 Server SP2, IIS 5.0, SQL Server 2000 SP2, MDAC 2.7,
no
> mapi mail acount
>
> Now I report the DDL code of my tow JOB, the firs is a simple one-step
job,
> thar work fine, the second is a tow step job, that crash my SQL Agent :-(
>
> You'll probably need to change the owner of the job with a valid acount on
> you server
>
>
==============================
==============================
===============
> -- This JOB WORK FINE
>
> BEGIN TRANSACTION
> DECLARE @JobID BINARY(16)
> DECLARE @ReturnCode INT
> SELECT @ReturnCode = 0
> IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database
> Maintenance') < 1
> EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
> IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'JOB Test that run
> fine') > 0
> PRINT N'Il processo "JOB Test that run fine" esiste gią e pertanto non
> verrą sostituito.'
> ELSE
> BEGIN
>
> -- Aggiungi processo
> EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
> @job_name = N'JOB Test that run fine', @owner_login_name = N'max',
> @description = N'Nessuna descrizione disponibile.', @category_name =
> N'Database Maintenance', @enabled = 1, @notify_level_email = 0,
> @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog
=
> 3, @delete_level= 0
> IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
>
> -- Aggiungi passaggi di processo
> EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id
=
> 1, @step_name = N'the only one step', @command = N'seelct ''pluto''',
> @database_name = N'tempdb', @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'', @on_success_step_id =
0,
> @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 1
> 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
>
> -- Aggiungi server target
> 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:
>
==============================
==============================
===============
>
==============================
==============================
===============
>
>
==============================
==============================
===============
>
==============================
==============================
===============
> -- This JOB CRASH
>
> BEGIN TRANSACTION
> DECLARE @JobID BINARY(16)
> DECLARE @ReturnCode INT
> SELECT @ReturnCode = 0
> IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database
> Maintenance') < 1
> EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
> IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'JOB Test that
> crash') > 0
> PRINT N'Il processo "JOB Test that crash" esiste gią e pertanto non
verrą
> sostituito.'
> ELSE
> BEGIN
>
> -- Aggiungi processo
> EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
> @job_name = N'JOB Test that crash', @owner_login_name = N'max',
@description
> = N'Nessuna descrizione disponibile.', @category_name = N'Database
> Maintenance', @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
>
> -- Aggiungi passaggi di processo
> EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id
=
> 1, @step_name = N'The first step', @command = N'select ''pluto''',
> @database_name = N'tempdb', @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'', @on_success_step_id =
0,
> @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
> IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
> EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id
=
> 2, @step_name = N'Second step', @command = N'select ''pippo''',
> @database_name = N'tempdb', @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'', @on_success_step_id =
0,
> @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 1
> 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
>
> -- Aggiungi server target
> 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:
>
==============================
==============================
===============
>
>
> Thanks, Max
>
>

Report this post to a moderator