Solved

Creating Alerts in sql sever

Posted on 2016-11-24
2
34 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 78

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 28

Accepted Solution

by:
Pawan Kumar earned 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Backup & Restore 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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

749 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