January 24, 2007 at 10:48 am
Is there a way to incorporate xp_SMTPmail for SQL Server AGENT mail notifications?
January 24, 2007 at 1:25 pm
Do you mean for alerts ? Yes, I have done this when the email server was Sun by having the alert response run a job that executes xp_SMTPmail. See BOL under sp_add_jobstep for a list of the alert tokens.
Here is an example that runs a select that is recorded in file 'C:\AlertResponse.txt'. You can change the job step to perform any supported action, including executing xp_SMTPmail
exec sp_addmessage @msgnum = 60000 , @severity = 10 , @msgtext = 'Test of Alerts Message' , @with_log = 'true'
go
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') 0
PRINT N'The job "Alert Job" already exists so will not be replaced.'
ELSE
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Alert Job', @owner_login_name = N'sa', @description = N'This job runs whenerver a alert is noticed by the SQL Server Agent', @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
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Alert Response', @command = N'Select''[A-DBN]''AS DatabaseName
,''[A-SVR]''AS ServerName
,''[A-ERR]'' AS ErrorNumber
,''[A-SEV]'' AS ErrorSeverity
,''[A-MSG]'' AS MessageText
,''[DATE]'' AS CurrentDate
,''[JOBID]'' AS JobID
,''[MACH]'' AS ComputerName
,''[MSSA]'' AS MasterSQLServerAgentServiceName
,''[SQLDIR]'' AS SQLServerDirectory
,''[STEPCT]'' AS JobStepRetryCount
,''[STEPID]'' AS StepID
,''[TIME]'' AS CurrentTime
,''[STRTTM]'' AS JobStartTime
,''[STRTDT]'' AS JobStartDate
', @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'C:\AlertResponse.txt', @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
EXECUTE msdb.dbo.sp_add_alert @name = N'Alert Test', @message_id = 60000, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @job_name = N'Alert Job', @category_name = N'[Uncategorized]'
go
raiserror (60000,10,1)
SQL = Scarcely Qualifies as a Language
January 29, 2007 at 11:57 am
I think its more to the point if a job fails (be it a sql statement or standard backup task). I want to work more or less like SQLAgentMail works with its job notifications.
January 29, 2007 at 6:19 pm
xp_SMTPmail mail is to replace sql mail not sql agent mail.
MohammedU
Microsoft SQL Server MVP
January 29, 2007 at 6:26 pm
I know that. It's just xp_SMTPmail is what I have to work with. They do not want to install a mapi client sio I am trying to work with what I got.
January 29, 2007 at 6:52 pm
I believe you are out of luck in that case....
or
Check for third party tools if you guys want to spend money...
MohammedU
Microsoft SQL Server MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply