Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Creating Alerts in sql sever

Posted on 2016-11-24
2
Medium Priority
?
58 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 80

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 35

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

916 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