Can SQL send me an email alert when log files are getting large?
Thanks,
JM
You can set up an alert that fires when the % used space in a log goes above
a certain threshold. Unfortunately, you'd need to set up one such alert per
database.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Joe Murphy" <spam@.spamthis.com> wrote in message
news:OdCtSbHPGHA.1832@.TK2MSFTNGP11.phx.gbl...
Can SQL send me an email alert when log files are getting large?
Thanks,
JM
|||How to?
_______________________________
http://boston.craigslist.org/about/b.../62974620.html
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uPDMueHPGHA.536@.TK2MSFTNGP09.phx.gbl...
> You can set up an alert that fires when the % used space in a log goes
> above
> a certain threshold. Unfortunately, you'd need to set up one such alert
> per
> database.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Joe Murphy" <spam@.spamthis.com> wrote in message
> news:OdCtSbHPGHA.1832@.TK2MSFTNGP11.phx.gbl...
> Can SQL send me an email alert when log files are getting large?
> Thanks,
> JM
>
|||What version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Murphy" <spam@.spamthis.com> wrote in message news:%23soU6wHPGHA.3728@.tk2msftngp13.phx.gbl...
> How to?
> --
> _______________________________
> http://boston.craigslist.org/about/b.../62974620.html
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uPDMueHPGHA.536@.TK2MSFTNGP09.phx.gbl...
>
|||I'd be interested in SQL2000 (SP3)
Regards,
Hank Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23cPC6SIPGHA.3984@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> What version of SQL Server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Murphy" <spam@.spamthis.com> wrote in message
> news:%23soU6wHPGHA.3728@.tk2msftngp13.phx.gbl...
|||Enterprise Manager, Management, SQL Server Agent, right-click Alerts, New Alert.
Specify a performance condition alert with the desired perf mon counter.
And first you create your operator and make sure that SQL Mail is functioning properly.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hank Arnold" <rasilon@.aol.com> wrote in message news:%23Y6DHDSPGHA.3408@.TK2MSFTNGP12.phx.gbl...
> I'd be interested in SQL2000 (SP3)
> --
> Regards,
> Hank Arnold
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23cPC6SIPGHA.3984@.TK2MSFTNGP14.phx.gbl...
>
|||OK, but that doesn't seem to work when trying to measure a threshold. Say
when the transaction log reaches N% of total allocated size.
That's the kind of thing I'm looking for.
Thanks,
JM
_______________________________
http://boston.craigslist.org/about/b.../62974620.html
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OPnG5UVPGHA.3264@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Enterprise Manager, Management, SQL Server Agent, right-click Alerts, New
> Alert.
> Specify a performance condition alert with the desired perf mon counter.
> And first you create your operator and make sure that SQL Mail is
> functioning properly.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Hank Arnold" <rasilon@.aol.com> wrote in message
> news:%23Y6DHDSPGHA.3408@.TK2MSFTNGP12.phx.gbl...
|||Did you specify *performance condition alert*? I'm not talking about the regular event alerts...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Murphy" <spam@.spamthis.com> wrote in message news:%23Nw4iLuPGHA.3848@.TK2MSFTNGP12.phx.gbl...
> OK, but that doesn't seem to work when trying to measure a threshold. Say
> when the transaction log reaches N% of total allocated size.
> That's the kind of thing I'm looking for.
> Thanks,
> JM
> --
> _______________________________
> http://boston.craigslist.org/about/b.../62974620.html
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OPnG5UVPGHA.3264@.TK2MSFTNGP11.phx.gbl...
>
|||Here's a sample script:
-- Script generated on 3/3/2006 8:48 PM
-- By: BMCI04\Administrator
-- Server: BMCI04\SQL2000
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'Backup Northwind Log')
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 ''Backup Northwind Log'' 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'Backup Northwind Log'
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'Backup Northwind Log', @.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'Backup', @.command = N'backup log Northwing to disk =
''C:\Temp\Northwind.trn''', @.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'', @.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:
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Backup
Northwind Log'))
-- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @.name = N'Backup Northwind Log'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @.name = N'Backup Northwind Log', @.message_id =
0, @.severity = 0, @.enabled = 1, @.delay_between_responses = 60,
@.performance_condition = N'MSSQL$SQL2000:Databases|Log File(s) Used Size
(KB)|Northwind|>|25', @.include_event_description_in = 5, @.job_name =
N'Backup Northwind Log', @.category_name = N'[Uncategorized]'
END
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Joe Murphy" <spam@.spamthis.com> wrote in message
news:%23Nw4iLuPGHA.3848@.TK2MSFTNGP12.phx.gbl...
OK, but that doesn't seem to work when trying to measure a threshold. Say
when the transaction log reaches N% of total allocated size.
That's the kind of thing I'm looking for.
Thanks,
JM
_______________________________
http://boston.craigslist.org/about/b.../62974620.html
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OPnG5UVPGHA.3264@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Enterprise Manager, Management, SQL Server Agent, right-click Alerts, New
> Alert.
> Specify a performance condition alert with the desired perf mon counter.
> And first you create your operator and make sure that SQL Mail is
> functioning properly.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Hank Arnold" <rasilon@.aol.com> wrote in message
> news:%23Y6DHDSPGHA.3408@.TK2MSFTNGP12.phx.gbl...
Showing posts with label alert. Show all posts
Showing posts with label alert. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
email alert on job failure
Hi all,
Is there a way to send an email alert when a sql server job fails?
Thanks in advance.Hi
If you are using SQL 2000 check the notifications tab on the job properties.
John
"helpful sql" wrote:
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>
>|||Hi
Add two additional steps. One is for success (OK) and another is for failure
(NO)
Job's step is going to the (OK) on success action and to (NO) on failure
action
"helpful sql" <nospam@.stopspam.com> wrote in message
news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>|||This example job may help!
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Error
Handling') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Error Handling'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Error Handling Example')
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 ''Error Handling Example'' 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'Error Handling Example'
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'Error Handling Example', @.owner_login_name = N'sa',
@.description = N'Error Handling Example.', @.category_name = N'Error
Handling', @.enabled = 0, @.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'SQL DUMP', @.command = N'BACKUP DATABASE [MyDatabase] TO
DISK = N''C:\Backup\MyDatabase.bak'' WITH INIT , NOUNLOAD , NAME =
N''Database Backup'', NOSKIP , STATS = 10, NOFORMAT', @.database_name =
N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =
N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 2,
@.on_success_action = 4, @.on_fail_step_id = 3, @.on_fail_action = 4
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
2, @.step_name = N'Inform Success', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Success - Error Handler Job'',
@.message = N''Error Handler Test Succeeded'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Completed Successfully'' , 16,1)', @.database_name =
N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =
N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.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_add_jobstep @.job_id = @.JobID, @.step_id =
3, @.step_name = N'Inform Failure', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Failure - Error Handler Job'',
@.message = N''Error Handler Test Failed'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Failed see log ''' for details'' , 16,1)',
@.database_name = N'MyDatabase', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 2, @.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 job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Error Handling Example', @.enabled = 1, @.freq_type = 4, @.active_start_dat
e
= 19900101, @.active_start_time = 214500, @.freq_interval = 1,
@.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval =
0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_tim
e
= 235959
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:
"Uri Dimant" wrote:
> Hi
> Add two additional steps. One is for success (OK) and another is for failu
re
> (NO)
> Job's step is going to the (OK) on success action and to (NO) on failure
> action
>
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
>
>
Is there a way to send an email alert when a sql server job fails?
Thanks in advance.Hi
If you are using SQL 2000 check the notifications tab on the job properties.
John
"helpful sql" wrote:
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>
>|||Hi
Add two additional steps. One is for success (OK) and another is for failure
(NO)
Job's step is going to the (OK) on success action and to (NO) on failure
action
"helpful sql" <nospam@.stopspam.com> wrote in message
news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>|||This example job may help!
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Error
Handling') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Error Handling'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Error Handling Example')
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 ''Error Handling Example'' 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'Error Handling Example'
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'Error Handling Example', @.owner_login_name = N'sa',
@.description = N'Error Handling Example.', @.category_name = N'Error
Handling', @.enabled = 0, @.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'SQL DUMP', @.command = N'BACKUP DATABASE [MyDatabase] TO
DISK = N''C:\Backup\MyDatabase.bak'' WITH INIT , NOUNLOAD , NAME =
N''Database Backup'', NOSKIP , STATS = 10, NOFORMAT', @.database_name =
N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =
N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 2,
@.on_success_action = 4, @.on_fail_step_id = 3, @.on_fail_action = 4
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
2, @.step_name = N'Inform Success', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Success - Error Handler Job'',
@.message = N''Error Handler Test Succeeded'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Completed Successfully'' , 16,1)', @.database_name =
N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =
N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.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_add_jobstep @.job_id = @.JobID, @.step_id =
3, @.step_name = N'Inform Failure', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Failure - Error Handler Job'',
@.message = N''Error Handler Test Failed'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Failed see log ''' for details'' , 16,1)',
@.database_name = N'MyDatabase', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 2, @.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 job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Error Handling Example', @.enabled = 1, @.freq_type = 4, @.active_start_dat
e
= 19900101, @.active_start_time = 214500, @.freq_interval = 1,
@.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval =
0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_tim
e
= 235959
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:
"Uri Dimant" wrote:
> Hi
> Add two additional steps. One is for success (OK) and another is for failu
re
> (NO)
> Job's step is going to the (OK) on success action and to (NO) on failure
> action
>
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
>
>
email alert on job failure
Hi all,
Is there a way to send an email alert when a sql server job fails?
Thanks in advance.Hi
If you are using SQL 2000 check the notifications tab on the job properties.
John
"helpful sql" wrote:
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>
>|||Hi
Add two additional steps. One is for success (OK) and another is for failure
(NO)
Job's step is going to the (OK) on success action and to (NO) on failure
action
"helpful sql" <nospam@.stopspam.com> wrote in message
news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>|||This example job may help!
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Error
Handling') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Error Handling'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Error Handling Example')
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 ''Error Handling Example'' 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'Error Handling Example'
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'Error Handling Example', @.owner_login_name = N'sa',
@.description = N'Error Handling Example.', @.category_name = N'Error
Handling', @.enabled = 0, @.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'SQL DUMP', @.command = N'BACKUP DATABASE [MyDatabase] TO
DISK = N''C:\Backup\MyDatabase.bak'' WITH INIT , NOUNLOAD , NAME =N''Database Backup'', NOSKIP , STATS = 10, NOFORMAT', @.database_name =N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 2,
@.on_success_action = 4, @.on_fail_step_id = 3, @.on_fail_action = 4
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =2, @.step_name = N'Inform Success', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Success - Error Handler Job'',
@.message = N''Error Handler Test Succeeded'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Completed Successfully'' , 16,1)', @.database_name =N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.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_add_jobstep @.job_id = @.JobID, @.step_id =3, @.step_name = N'Inform Failure', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Failure - Error Handler Job'',
@.message = N''Error Handler Test Failed'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Failed see log ''' for details'' , 16,1)',
@.database_name = N'MyDatabase', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 2, @.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 job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Error Handling Example', @.enabled = 1, @.freq_type = 4, @.active_start_date
= 19900101, @.active_start_time = 214500, @.freq_interval = 1,
@.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval =0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_time
= 235959
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:
"Uri Dimant" wrote:
> Hi
> Add two additional steps. One is for success (OK) and another is for failure
> (NO)
> Job's step is going to the (OK) on success action and to (NO) on failure
> action
>
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
> > Hi all,
> > Is there a way to send an email alert when a sql server job fails?
> >
> > Thanks in advance.
> >
>
>
Is there a way to send an email alert when a sql server job fails?
Thanks in advance.Hi
If you are using SQL 2000 check the notifications tab on the job properties.
John
"helpful sql" wrote:
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>
>|||Hi
Add two additional steps. One is for success (OK) and another is for failure
(NO)
Job's step is going to the (OK) on success action and to (NO) on failure
action
"helpful sql" <nospam@.stopspam.com> wrote in message
news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>|||This example job may help!
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Error
Handling') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Error Handling'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Error Handling Example')
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 ''Error Handling Example'' 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'Error Handling Example'
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'Error Handling Example', @.owner_login_name = N'sa',
@.description = N'Error Handling Example.', @.category_name = N'Error
Handling', @.enabled = 0, @.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'SQL DUMP', @.command = N'BACKUP DATABASE [MyDatabase] TO
DISK = N''C:\Backup\MyDatabase.bak'' WITH INIT , NOUNLOAD , NAME =N''Database Backup'', NOSKIP , STATS = 10, NOFORMAT', @.database_name =N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 2,
@.on_success_action = 4, @.on_fail_step_id = 3, @.on_fail_action = 4
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =2, @.step_name = N'Inform Success', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Success - Error Handler Job'',
@.message = N''Error Handler Test Succeeded'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Completed Successfully'' , 16,1)', @.database_name =N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.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_add_jobstep @.job_id = @.JobID, @.step_id =3, @.step_name = N'Inform Failure', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Failure - Error Handler Job'',
@.message = N''Error Handler Test Failed'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Failed see log ''' for details'' , 16,1)',
@.database_name = N'MyDatabase', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 2, @.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 job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Error Handling Example', @.enabled = 1, @.freq_type = 4, @.active_start_date
= 19900101, @.active_start_time = 214500, @.freq_interval = 1,
@.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval =0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_time
= 235959
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:
"Uri Dimant" wrote:
> Hi
> Add two additional steps. One is for success (OK) and another is for failure
> (NO)
> Job's step is going to the (OK) on success action and to (NO) on failure
> action
>
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
> > Hi all,
> > Is there a way to send an email alert when a sql server job fails?
> >
> > Thanks in advance.
> >
>
>
email alert on job failure
Hi all,
Is there a way to send an email alert when a sql server job fails?
Thanks in advance.Hi
If you are using SQL 2000 check the notifications tab on the job properties.
John
"helpful sql" wrote:
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>
>|||Hi
Add two additional steps. One is for success (OK) and another is for failure
(NO)
Job's step is going to the (OK) on success action and to (NO) on failure
action
"helpful sql" <nospam@.stopspam.com> wrote in message
news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>|||This example job may help!
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Error
Handling') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Error Handling'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Error Handling Example')
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 ''Error Handling Example'' 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'Error Handling Example'
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'Error Handling Example', @.owner_login_name = N'sa',
@.description = N'Error Handling Example.', @.category_name = N'Error
Handling', @.enabled = 0, @.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'SQL DUMP', @.command = N'BACKUP DATABASE [MyDatabase] T
O
DISK = N''C:\Backup\MyDatabase.bak'' WITH INIT , NOUNLOAD , NAME =
N''Database Backup'', NOSKIP , STATS = 10, NOFORMAT', @.database_name =
N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =
N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 2,
@.on_success_action = 4, @.on_fail_step_id = 3, @.on_fail_action = 4
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
2, @.step_name = N'Inform Success', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Success - Error Handler Job'',
@.message = N''Error Handler Test Succeeded'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Completed Successfully'' , 16,1)', @.database_name =
N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =
N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.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_add_jobstep @.job_id = @.JobID, @.step_id =
3, @.step_name = N'Inform Failure', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Failure - Error Handler Job'',
@.message = N''Error Handler Test Failed'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Failed see log ''' for details'' , 16,1)',
@.database_name = N'MyDatabase', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 2, @.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 job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Error Handling Example', @.enabled = 1, @.freq_type = 4, @.active_start_dat
e
= 19900101, @.active_start_time = 214500, @.freq_interval = 1,
@.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval =
0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_tim
e
= 235959
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:
"Uri Dimant" wrote:
> Hi
> Add two additional steps. One is for success (OK) and another is for failu
re
> (NO)
> Job's step is going to the (OK) on success action and to (NO) on failure
> action
>
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
>
>
Is there a way to send an email alert when a sql server job fails?
Thanks in advance.Hi
If you are using SQL 2000 check the notifications tab on the job properties.
John
"helpful sql" wrote:
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>
>|||Hi
Add two additional steps. One is for success (OK) and another is for failure
(NO)
Job's step is going to the (OK) on success action and to (NO) on failure
action
"helpful sql" <nospam@.stopspam.com> wrote in message
news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>|||This example job may help!
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Error
Handling') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Error Handling'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Error Handling Example')
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 ''Error Handling Example'' 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'Error Handling Example'
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'Error Handling Example', @.owner_login_name = N'sa',
@.description = N'Error Handling Example.', @.category_name = N'Error
Handling', @.enabled = 0, @.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'SQL DUMP', @.command = N'BACKUP DATABASE [MyDatabase] T
O
DISK = N''C:\Backup\MyDatabase.bak'' WITH INIT , NOUNLOAD , NAME =
N''Database Backup'', NOSKIP , STATS = 10, NOFORMAT', @.database_name =
N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =
N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 2,
@.on_success_action = 4, @.on_fail_step_id = 3, @.on_fail_action = 4
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
2, @.step_name = N'Inform Success', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Success - Error Handler Job'',
@.message = N''Error Handler Test Succeeded'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Completed Successfully'' , 16,1)', @.database_name =
N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =
N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.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_add_jobstep @.job_id = @.JobID, @.step_id =
3, @.step_name = N'Inform Failure', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Failure - Error Handler Job'',
@.message = N''Error Handler Test Failed'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Failed see log ''' for details'' , 16,1)',
@.database_name = N'MyDatabase', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 2, @.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 job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Error Handling Example', @.enabled = 1, @.freq_type = 4, @.active_start_dat
e
= 19900101, @.active_start_time = 214500, @.freq_interval = 1,
@.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval =
0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_tim
e
= 235959
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:
"Uri Dimant" wrote:
> Hi
> Add two additional steps. One is for success (OK) and another is for failu
re
> (NO)
> Job's step is going to the (OK) on success action and to (NO) on failure
> action
>
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
>
>
email alert log shipping out of sync?
Hello all,
Windows 2000 Log shipping Qu.
I have successfully set up log shipping but would like the monitor server to
send an email if the servers become out of sync. It seems that the default
is only to log the event in the app log does anyone know how to set up an
email alert?
Thanks in advance foir the help, Simon.If you do not have SQL mail setup, one easy alternative is to use
xp_smtp_mail. All the required information you needed regarding this can be
found at:
http://www.sqldev.net/xp/xpsmtp.htm
Anith|||Thanks for the advice - I can use SQL mail but I'm not sure how to fire the
event:
Do the monitor server jobs (listed below) fail when there is a problem or do
they simply log the event in app log?
Log Shipping Alert Job - Backup &
Log Shipping Alert Job - Restore
Thanks again.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:Olz9OPbHFHA.2420@.TK2MSFTNGP14.phx.gbl...
> If you do not have SQL mail setup, one easy alternative is to use
> xp_smtp_mail. All the required information you needed regarding this can
> be found at:
> http://www.sqldev.net/xp/xpsmtp.htm
> --
> Anith
>|||In your job, create a new step ( say step 2 ) with the functionality to
email required addressee. On the initial step, set the "on failure action"
to goto step 2. Make sure set the "on success action" as Quit with success.
Anith
Windows 2000 Log shipping Qu.
I have successfully set up log shipping but would like the monitor server to
send an email if the servers become out of sync. It seems that the default
is only to log the event in the app log does anyone know how to set up an
email alert?
Thanks in advance foir the help, Simon.If you do not have SQL mail setup, one easy alternative is to use
xp_smtp_mail. All the required information you needed regarding this can be
found at:
http://www.sqldev.net/xp/xpsmtp.htm
Anith|||Thanks for the advice - I can use SQL mail but I'm not sure how to fire the
event:
Do the monitor server jobs (listed below) fail when there is a problem or do
they simply log the event in app log?
Log Shipping Alert Job - Backup &
Log Shipping Alert Job - Restore
Thanks again.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:Olz9OPbHFHA.2420@.TK2MSFTNGP14.phx.gbl...
> If you do not have SQL mail setup, one easy alternative is to use
> xp_smtp_mail. All the required information you needed regarding this can
> be found at:
> http://www.sqldev.net/xp/xpsmtp.htm
> --
> Anith
>|||In your job, create a new step ( say step 2 ) with the functionality to
email required addressee. On the initial step, set the "on failure action"
to goto step 2. Make sure set the "on success action" as Quit with success.
Anith
email alert log shipping out of sync?
Hello all,
Windows 2000 Log shipping Qu.
I have successfully set up log shipping but would like the monitor server to
send an email if the servers become out of sync. It seems that the default
is only to log the event in the app log does anyone know how to set up an
email alert?
Thanks in advance foir the help, Simon.
If you do not have SQL mail setup, one easy alternative is to use
xp_smtp_mail. All the required information you needed regarding this can be
found at:
http://www.sqldev.net/xp/xpsmtp.htm
Anith
|||Thanks for the advice - I can use SQL mail but I'm not sure how to fire the
event:
Do the monitor server jobs (listed below) fail when there is a problem or do
they simply log the event in app log?
Log Shipping Alert Job - Backup &
Log Shipping Alert Job - Restore
Thanks again.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:Olz9OPbHFHA.2420@.TK2MSFTNGP14.phx.gbl...
> If you do not have SQL mail setup, one easy alternative is to use
> xp_smtp_mail. All the required information you needed regarding this can
> be found at:
> http://www.sqldev.net/xp/xpsmtp.htm
> --
> Anith
>
|||In your job, create a new step ( say step 2 ) with the functionality to
email required addressee. On the initial step, set the "on failure action"
to goto step 2. Make sure set the "on success action" as Quit with success.
Anith
Windows 2000 Log shipping Qu.
I have successfully set up log shipping but would like the monitor server to
send an email if the servers become out of sync. It seems that the default
is only to log the event in the app log does anyone know how to set up an
email alert?
Thanks in advance foir the help, Simon.
If you do not have SQL mail setup, one easy alternative is to use
xp_smtp_mail. All the required information you needed regarding this can be
found at:
http://www.sqldev.net/xp/xpsmtp.htm
Anith
|||Thanks for the advice - I can use SQL mail but I'm not sure how to fire the
event:
Do the monitor server jobs (listed below) fail when there is a problem or do
they simply log the event in app log?
Log Shipping Alert Job - Backup &
Log Shipping Alert Job - Restore
Thanks again.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:Olz9OPbHFHA.2420@.TK2MSFTNGP14.phx.gbl...
> If you do not have SQL mail setup, one easy alternative is to use
> xp_smtp_mail. All the required information you needed regarding this can
> be found at:
> http://www.sqldev.net/xp/xpsmtp.htm
> --
> Anith
>
|||In your job, create a new step ( say step 2 ) with the functionality to
email required addressee. On the initial step, set the "on failure action"
to goto step 2. Make sure set the "on success action" as Quit with success.
Anith
email alert log shipping out of sync?
Hello all,
Windows 2000 Log shipping Qu.
I have successfully set up log shipping but would like the monitor server to
send an email if the servers become out of sync. It seems that the default
is only to log the event in the app log does anyone know how to set up an
email alert?
Thanks in advance foir the help, Simon.If you do not have SQL mail setup, one easy alternative is to use
xp_smtp_mail. All the required information you needed regarding this can be
found at:
http://www.sqldev.net/xp/xpsmtp.htm
--
Anith|||Thanks for the advice - I can use SQL mail but I'm not sure how to fire the
event:
Do the monitor server jobs (listed below) fail when there is a problem or do
they simply log the event in app log?
Log Shipping Alert Job - Backup &
Log Shipping Alert Job - Restore
Thanks again.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:Olz9OPbHFHA.2420@.TK2MSFTNGP14.phx.gbl...
> If you do not have SQL mail setup, one easy alternative is to use
> xp_smtp_mail. All the required information you needed regarding this can
> be found at:
> http://www.sqldev.net/xp/xpsmtp.htm
> --
> Anith
>|||In your job, create a new step ( say step 2 ) with the functionality to
email required addressee. On the initial step, set the "on failure action"
to goto step 2. Make sure set the "on success action" as Quit with success.
--
Anith
Windows 2000 Log shipping Qu.
I have successfully set up log shipping but would like the monitor server to
send an email if the servers become out of sync. It seems that the default
is only to log the event in the app log does anyone know how to set up an
email alert?
Thanks in advance foir the help, Simon.If you do not have SQL mail setup, one easy alternative is to use
xp_smtp_mail. All the required information you needed regarding this can be
found at:
http://www.sqldev.net/xp/xpsmtp.htm
--
Anith|||Thanks for the advice - I can use SQL mail but I'm not sure how to fire the
event:
Do the monitor server jobs (listed below) fail when there is a problem or do
they simply log the event in app log?
Log Shipping Alert Job - Backup &
Log Shipping Alert Job - Restore
Thanks again.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:Olz9OPbHFHA.2420@.TK2MSFTNGP14.phx.gbl...
> If you do not have SQL mail setup, one easy alternative is to use
> xp_smtp_mail. All the required information you needed regarding this can
> be found at:
> http://www.sqldev.net/xp/xpsmtp.htm
> --
> Anith
>|||In your job, create a new step ( say step 2 ) with the functionality to
email required addressee. On the initial step, set the "on failure action"
to goto step 2. Make sure set the "on success action" as Quit with success.
--
Anith
Email Alert in SQL Server2000
can somebody help me to setup email alerts in SQL Server 2000 standard edition whenever, backup is successful OR failed to complete.
Thanks,
I'll assume you're doing scheduled backups via SQL Server Agent
1. Configure SQL Server to use SQL Mail (Check Books Online SQL Mail)
2. Add an operator to SQL Server Agent with the relevant email address
3. In your SQL Job, notifications tab, check the box to email, select your operator and also selec to alert whenever the job completes.
HTH!
Email Alert
I have a Windows 2003 server running SQL 2000 SP4. I want to start setting
up email alerts and reporting on the SQL server. what is the most safe and
best way to achive this?Hi docsql,
Welcome to use MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to know
how to setup a email notification for an alert in SQL Server 2000. If I
misunderstood your concern, please feel free to point it out.
In SQL Server 2000, you can define the Response to an alert and choose the
email operator to notify.
To setup a SQL Mail, you need to configure a mail profile first.
SQL Mail must have a mail profile created in the same user domain account
or context that is used to start an instance of SQL Server. You can create
the profile with a mail client, such as Microsoft Outlook, that is
installed locally on the SQL Server computer.
You can find how to configure SQL Mail in the following article.
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_1_server_2ecs.asp
Also, following KB article may help you to set up a SQL Agent Mail.
263556 INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
315886 INF: Common SQL Mail Problems
http://support.microsoft.com/?id=315886
321183 HOW TO: Troubleshoot Your SQL Mail Issue
http://support.microsoft.com/?id=321183
311231 INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231
Hope this will be helpful.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
up email alerts and reporting on the SQL server. what is the most safe and
best way to achive this?Hi docsql,
Welcome to use MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to know
how to setup a email notification for an alert in SQL Server 2000. If I
misunderstood your concern, please feel free to point it out.
In SQL Server 2000, you can define the Response to an alert and choose the
email operator to notify.
To setup a SQL Mail, you need to configure a mail profile first.
SQL Mail must have a mail profile created in the same user domain account
or context that is used to start an instance of SQL Server. You can create
the profile with a mail client, such as Microsoft Outlook, that is
installed locally on the SQL Server computer.
You can find how to configure SQL Mail in the following article.
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_1_server_2ecs.asp
Also, following KB article may help you to set up a SQL Agent Mail.
263556 INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
315886 INF: Common SQL Mail Problems
http://support.microsoft.com/?id=315886
321183 HOW TO: Troubleshoot Your SQL Mail Issue
http://support.microsoft.com/?id=321183
311231 INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231
Hope this will be helpful.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
Friday, March 9, 2012
Email Alert
I'm trying to develop a email alert feature on my project. I was trying to approach with SQL trigger wich I think is the best option. Basically, when a new record is inserted into ad table, a email alert goes to peolpe who selected to receive alert wehen certain conditions are met. What would be the best approach? any examples?
ThanksThat sounds a lot likeSQL Server Notification Services, a free download if you are using SQL Server.
|||Thanks for the reply. Is MS Notification Services SP1 what I need?
ThanksThat sounds a lot likeSQL Server Notification Services, a free download if you are using SQL Server.
|||Thanks for the reply. Is MS Notification Services SP1 what I need?
Email Alert
I have a Windows 2003 server running SQL 2000 SP4. I want to start setting
up email alerts and reporting on the SQL server. what is the most safe and
best way to achive this?
Hi docsql,
Welcome to use MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to know
how to setup a email notification for an alert in SQL Server 2000. If I
misunderstood your concern, please feel free to point it out.
In SQL Server 2000, you can define the Response to an alert and choose the
email operator to notify.
To setup a SQL Mail, you need to configure a mail profile first.
SQL Mail must have a mail profile created in the same user domain account
or context that is used to start an instance of SQL Server. You can create
the profile with a mail client, such as Microsoft Outlook, that is
installed locally on the SQL Server computer.
You can find how to configure SQL Mail in the following article.
http://msdn.microsoft.com/library/de...us/adminsql/ad
_1_server_2ecs.asp
Also, following KB article may help you to set up a SQL Agent Mail.
263556 INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
315886 INF: Common SQL Mail Problems
http://support.microsoft.com/?id=315886
321183 HOW TO: Troubleshoot Your SQL Mail Issue
http://support.microsoft.com/?id=321183
311231 INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231
Hope this will be helpful.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
up email alerts and reporting on the SQL server. what is the most safe and
best way to achive this?
Hi docsql,
Welcome to use MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to know
how to setup a email notification for an alert in SQL Server 2000. If I
misunderstood your concern, please feel free to point it out.
In SQL Server 2000, you can define the Response to an alert and choose the
email operator to notify.
To setup a SQL Mail, you need to configure a mail profile first.
SQL Mail must have a mail profile created in the same user domain account
or context that is used to start an instance of SQL Server. You can create
the profile with a mail client, such as Microsoft Outlook, that is
installed locally on the SQL Server computer.
You can find how to configure SQL Mail in the following article.
http://msdn.microsoft.com/library/de...us/adminsql/ad
_1_server_2ecs.asp
Also, following KB article may help you to set up a SQL Agent Mail.
263556 INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
315886 INF: Common SQL Mail Problems
http://support.microsoft.com/?id=315886
321183 HOW TO: Troubleshoot Your SQL Mail Issue
http://support.microsoft.com/?id=321183
311231 INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231
Hope this will be helpful.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
Email Alert
I have a Windows 2003 server running SQL 2000 SP4. I want to start setting
up email alerts and reporting on the SQL server. what is the most safe and
best way to achive this?Hi docsql,
Welcome to use MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to know
how to setup a email notification for an alert in SQL Server 2000. If I
misunderstood your concern, please feel free to point it out.
In SQL Server 2000, you can define the Response to an alert and choose the
email operator to notify.
To setup a SQL Mail, you need to configure a mail profile first.
SQL Mail must have a mail profile created in the same user domain account
or context that is used to start an instance of SQL Server. You can create
the profile with a mail client, such as Microsoft Outlook, that is
installed locally on the SQL Server computer.
You can find how to configure SQL Mail in the following article.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
_1_server_2ecs.asp
Also, following KB article may help you to set up a SQL Agent Mail.
263556 INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
315886 INF: Common SQL Mail Problems
http://support.microsoft.com/?id=315886
321183 HOW TO: Troubleshoot Your SQL Mail Issue
http://support.microsoft.com/?id=321183
311231 INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231
Hope this will be helpful.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
up email alerts and reporting on the SQL server. what is the most safe and
best way to achive this?Hi docsql,
Welcome to use MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to know
how to setup a email notification for an alert in SQL Server 2000. If I
misunderstood your concern, please feel free to point it out.
In SQL Server 2000, you can define the Response to an alert and choose the
email operator to notify.
To setup a SQL Mail, you need to configure a mail profile first.
SQL Mail must have a mail profile created in the same user domain account
or context that is used to start an instance of SQL Server. You can create
the profile with a mail client, such as Microsoft Outlook, that is
installed locally on the SQL Server computer.
You can find how to configure SQL Mail in the following article.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
_1_server_2ecs.asp
Also, following KB article may help you to set up a SQL Agent Mail.
263556 INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
315886 INF: Common SQL Mail Problems
http://support.microsoft.com/?id=315886
321183 HOW TO: Troubleshoot Your SQL Mail Issue
http://support.microsoft.com/?id=321183
311231 INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231
Hope this will be helpful.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)