ASPHostCentral.com SQL Reporting Service (SSRS) 2012 Hosting BLOG

All about SQL Reporting Service (SSRS) 2012 Hosting articles

SQL 2005/2008 Hosting :: How to Send Automated Job Email Notifications in SQL Server with SMTP

clock November 6, 2011 13:56 by author darwin

When you have automated backup jobs running on your database server, sometimes you forget that they are even running. Then you forget to check to see if they are running successfully, and don’t realize until your database crashes and you can’t restore it since you don’t have a current backup.

That’s where email notifications come in, so you can see the job status every morning when you are sipping your coffee and pretending you are working.

SQL Server provides a built-in method of sending emails, but unfortunately it requires you to have Outlook and a profile installed on the server, which isn’t necessarily the ideal way to send an email. Thankfully there is another method, that involves installing a stored procedure on your server that will allow you to send email via SMTP.

You will want to edit one line in the stored procedure to put the IP address of your SMTP server:

EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields(“http://schemas.microsoft.com/cdo/configuration/smtpserver”).Value’, ’10.1.1.10′

Install the stored procedure into the master database, so it can be easily used from wherever needed.

Open up the SQL Server Agent \ Jobs list, and select the properties for the job you are trying to create a notification for:



Click on the Steps tab, and you should see a screen that looks like this:



Click the New button to create a new job step. We will use this step to send the email notification on success.

Step Name: Email Notification Success

Enter this SQL into the Command window as seen below. You will want to customize the email addresses and message subject to match your environment:

exec master.dbo.sp_SQLNotify ‘[email protected]’,'[email protected]’,'Backup Job Success’,'The Backup Job completed successfully’



Click OK and then click the New button again to create another step. This will be the failure notification step.

Step Name: Email Notification Failure

SQL:

exec master.dbo.sp_SQLNotify ‘[email protected]’,'[email protected]’,'Backup Job Failure,’The Backup Job failed’

Now the idea is to make the items follow a specific workflow. First click Edit on step 1, and set the properties as shown here:



What we are saying is that on success, go to the success step, and on failure, go to the failure step. Pretty simple stuff.

Now edit the second step, the one labled “Email Notification Success”, and set the properties as seen here:



We are saying that if the notification job is successful, then just quit the job without running step 3. If we don’t specify this, then we will end up getting two emails, one with success and one with failure.

Now edit the third step, the one labled “Email notification failure”, and set the properties as seen here:



Now your job steps should look like this:



You should now have email notifications in your inbox for either success or failure. 

Note: The stored procedure used in this article was found
here.

Currently rated 1.8 by 56 people

  • Currently 1.785712/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5


SQL 2008 Hosting :: How to Fix Error: 26 - Error Locating Server/Instance Specified

clock November 2, 2011 15:21 by author darwin

This is the error message that I almost find everyday on forum. So, I decide to make this post to help the people who face this problem. Actually, the solution is very simple and I hope this tutorial below can help you.

First of all, you get this error message only if you are trying to connect to a SQL Server named instance. For default instance, you never see this. Why? Because even if we failed at this stage (i.e. error locating server/instance specified), we will continue to try connect using default values, e.g defaul TCP port 1433, default pipe name for Named Pipes. You may see other error message due to failure later, but not this error message.

Every time client makes a connection to SQL Server named instance, we will send a SSRP UDP packet to the server machine UDP port 1434. We need this step to know configuration information of the SQL instance, e.g., protocols enabled, TCP port, pipe name etc. Without these information, client does know how to connect the server and it fails with this specified error message.

In a word, the reason that we get this error message is the client stack could not receive SSRP response UDP packet from SQL Browser. It's easy to isolate the issue. Here are the steps:

1. Make sure your server name is correct, e.g., no typo on the name.
2. Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \\ to \. If you are not sure about your application, please try both Server\Instance and Server\\Instance in your connection string]
3. Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
4. Make sure SQL Browser service is running on the server.
5. If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

If follow the steps above correctly, you should not see this error message anymore. Good luck.

If you're looking for Windows hosting, please check our site at http://www.asphostcentral.com.

Currently rated 3.0 by 10 people

  • Currently 3/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5


SSRS 2012 Hosting

ASPHostCentral is a premier web hosting company where you will find low cost and reliable web hosting. We have supported the latest ASP.NET 4.5 hosting and ASP.NET MVC 4 hosting. We have supported the latest SQL Server 2012 Hosting and Windows Server 2012 Hosting too!


Tag cloud

Sign in