Trap errors and sent to specified email in sql ser

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

General SQL Server Forums

New to SQL Server Programming

Trap errors and sent to specified email in sql ser

Author

Topic

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-12-15 : 23:12:59

Is there a provision to set the db errors in sql server to a specific email id?

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-16 : 00:31:01

Hi,
Try this..
STEP 1:
First configure the email setting in ur database.

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MyMailAccount',
@description = 'Mail account for Database Mail',
@email_address = 'test@test.in',
@display_name = 'MyAccount',
@username='test@test.in',
@password='abcd',
@mailserver_name = 'newmail.test.in'

go
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MyMailProfile',
@description = 'Profile used for database mail'

Go

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MyMailProfile',
@account_name = 'MyMailAccount',
@sequence_number = 1

Go

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MyMailProfile',
@principal_name = 'public',
@is_default = 1 ;

Go

STEP 2:
You may use the below code to send mail to specific id

EXEC master.dbo.xp_sendmail
@recipients='youmailid@you.com', --- Your Specific Email ID
@message = @ErrorMsg, --- Error Message
@subject = 'DB Error Message.'

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 00:58:56

if you want capture errors happening in sql agent jobs and send it to email id, you can configure it inside sql agent job itself in notifications tab of job properties
If you want native errors to be capturing you can add logging steps to log errors to a table/file and then email the contents to specific email ids

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources