Improve company productivity with a Business Account.Sign Up

x
?
Solved

Creating Alerts in sql sever

Posted on 2016-11-24
2
Medium Priority
?
74 Views
Last Modified: 2016-11-24
Hi experts

i am reading about Creating Alerts:
what's the mean?, can you give me an example?
Amend error definition or raise errors using the RAISERROR WITH LOG option to force an error to be logged
0
Comment
Question by:enrique_aeo
2 Comments
 
LVL 82

Expert Comment

by:arnold
ID: 41901040
You need to elaborate on the types of alerts ..
You can using db Mail configure the SQL agent jobs to generate alerts/notifications.

You would first need to configure db Mail with destination mailserver to use in the event you do not have local iis SMTP service installed, as well as default sender using a valid email address.

Look at any of your jobs, look at properties ..

If you create jobs using tsql, ref notices in both, or either success or failure.
0
 
LVL 38

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41901046
In simple terms you can say if you are doing something and it fails or met a specific condition you get a notification. Lets say we are taking backup using a stored procedure. Now we can create and get alerts when the backup is done successfully or fails.

From Microsoft-

Events are basically generated by SQL Server and entered into the Microsoft Windows application log. SQL Server Agent reads the application log and compares events written there to alerts that you have defined. When SQL Server Agent finds a match, it fires an alert, which is an automated response to an event.

For alerts you need to mention 3 things-
1. Name
2. Condition/Event that triggers the alert.
3. Action - SQL Server Agent takes in response to Event / Condition.

Ref more at - https://msdn.microsoft.com/en-us/library/ms180982.aspx

Code to create alerts

--

USE msdb  
GO  
  
EXEC dbo.sp_add_alert  
			@name = N'Pawan',  
			@message_id = 55001,   
			@severity = 0,   
			@notification_message = N'Pawan - Error has occoured',   
			@job_name = N'Pawan_backup'  
GO  

--

Open in new window



https://msdn.microsoft.com/en-us/library/ms175166.aspx

Hope it helps!!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question