Starting Jobs in Powershell using Job Name

  When starting jobs in powershell with smo, you have to use the jobname and categoryID. If you have same job name on multiple servers that could be in different category, and if you are running a single script that connects to multiple servers and starts job, it will not work because JobName+CategoryID combination might…

3

Troubleshooting: SQL Agent can’t start without SQLAGENT.OUT telling the reason

SQLAGENT.OUT, the log file for SQL Agent, is usually the first place to investigate what could go wrong.  However, when SQL Agent cannot start,  a new SQLAGENT.OUT even is not generated at all.   What should we investigate?  If this kind of things happens, a possible reason is due to the file permission on SQLAGENT.OUT that…


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…