Solved

Creating Alerts in sql sever

Posted on 2016-11-24
2
35 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

Title # Comments Views Activity
sql 2016 data tools breakdown.. 1 36
how to make geography query faster?  SQL 7 64
Estimating my database size 7 51
EF5: Update Model from Database not working 3 83
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.

751 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