Enabling email notifications in the SQL Agent for Alerting & Job monitoring

 To enable email notifications in SQL Agent you will need to follow the following steps: 1. Configure Datbase Mail: create an email SMTP account and corresponding Email Profile. To do so please follow directions outlined in the blog post: “SQL Database Mail – Send Emails from SQL Server”. 2. In SSMS (SQL Server Managment Studio)…

0

Database Mail–when attachments don’t work

In a previous post (SQL Database Mail – Send Emails with Attachment), we described how to attach a file to an email. There is a known bug related to this. Under certain conditions, the mail attachment will not be sent. If a mail with an attachment is sent from within a transaction in snapshot isolation…

0

Scheduled Policy Evaluation on multiple servers using MSX/ TSX in SQL Agent

One of our customer was looking for ways to schedule a policy evaluation on multiple servers using their existing MSX/TSX infrastructure.  I thought it would be better to blog an article that could help others looking for similar information Before we get into details, here are some basics.  Please read reference material. If you have…

1

SQL Database Mail – Send Emails with Attachment

Database Mail allows you to attach files in emails.   Several examples are provided to show how you can do it. The article here is assumed that a SQL Database Mail profile “MailProfile1′ has been created.  The MailProfile1 tells Database Mail what account it should use to send emails.  For how to create a Database Mail profile, you can refer…


Writing Powershell Steps Caveat

Sql Agent provides tokens to be used with T-Sql job step scripts. Tokens are names (strings) that are replaced at runtime with a value. The tokens provide a feature similar to variables in code. The tokens are described in MSDN (http://msdn.microsoft.com/en-us/library/ms175575.aspx). In 2005 and 2008, the token syntax has changed and the tokens must now…

3

SQL Agent Issue with identifiers delimited by double quotation marks

The issue is related to default settings for quoted_identifier. In SSMS query window the default settings for quoted_identifier is ON. It means that the following code will work: DECLARE  @Days INT SELECT @Days = 5  SELECT ‘Test’, DateAdd(“dd”,@Days,GetDate()) But if you copy and paste this T-SQL statement to SQL Agent step (T-SQL) it will fail with…

0

Database Mail – Validating your SMTP mail server configuration using powershell

  Database mail relies on .NET framework’s SMTP client API to send emails. You could write a simple power shell script like this to test out if you are able to send mails using your smtp server Sample Powershell Code to send email: 1: $smtpServer = “emailserver” 2: $smtpPort = 25 3: $emailFrom = “from@domain.com”…


SQL Database Mail – Send T-SQL Results by Email

This post demonstrates how you can have T-SQL results being sent to you in emails by using SQL Database Mail. The article here is assumed that a SQL Database Mail profile “MailProfile1′ has been created.  The MailProfile1 tells Database Mail what account it should use to send emails.  For how to create a Database Mail profile, you can refer to the previous…