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.

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.

Applies to:

- Internet Information Services 7.o (IIS7)
- Microsoft Report Viewer Redistributable 2005

Symptoms:

- Unable to render ReportViewer on ASP.NET Web pages while running on IIS7.
- You have no problem viewing your reports when running on debug mode with your Visual Studio 2005.
- You are able to view your reports on Report Manager but not able to view them on IIS7.
- You encounter JavaScript error when loading your report page with ReportViewer. Image buttons such as calendar appear as red 'X'.

Cause:

- When the ReportViewer control is added to Web Form (.aspx), the Reserved.ReportViewerWebControl.axd httpHandler is added to System.Web section of the Web.Config file. In IIS7, it should be added under System.Webserver section.
- IIS7 Handler Mappings does not contain Reserved.ReportViewerWebControl.axd httpHandler, and therefore unable to render the ReportViewer elements needed by the JavaSript.

Resolution:

- Open Internet Information Services (IIS) Manager and select your Web application.
- Under IIS area, double-click on Handler Mappings icon.
- At the Action pane on your right, click on Add Managed Handler.
- At the Add Managed Handler dialog, enter the following: Request path: Reserved.ReportViewerWebControl.axd
Type: Microsoft.Reporting.WebForms.HttpHandler
Name: Reserved-ReportViewerWebControl-axd
- Click OK.

Reserved-ReportViewerWebControl-axd handler is now added to your Handler Mappings list. Notice that the following line has also been added to your Web.config file under the system.webserver's handler section:

<add name="Reserved-ReportViewerWebControl-axd" path="Reserved.ReportViewerWebControl.axd" verb="*" type="Microsoft.Reporting.WebForms.HttpHandler" resourceType="Unspecified"/>

Run your report again

With all the new features in SQL 2008 R2, here are the major ones getting all the press:

PowerPivot
Parallel Data Warehouse
Application and Multi-Server Management
StreamInsight
256 core support 

There is so much written on the ones above, I wanted to concentrate on talking about other new features in SQL 2008 R2.  So, in no particular order:   

1.       SMB support – SMB stands for Server Message Block and this protocol is now officially supported by SQL Server 2008 R2 and beyond.  This improvement has formalized the support status of placing SQL database files on SMB network file shares.  From Kevin Farlee, the owner of this feature in SQL Server: “This presents a better-together story with the work that Windows has done in Windows7/Server 2008 R2 to make the Windows SMB stack far more performant and resilient than in the past.  It is also a recognition that with the increasing acceptance of iSCSI, customers are viewing Ethernet as a viable way to connect to their storage.  Finally, it gives customers in consolidation environments a very simple to manage method for moving databases between servers without investing in a large SAN infrastructure.” 

2.       Increased Performance – there are very nice performance improvements, especially with the combination of Windows 2008 R2 and SQL 2008 R2.  The actual TPC-E measurements on have been audited and published.

3.       SYSPREP – Finally!  We can now create Sysprep versions of SQL Server environments, starting with SQL Server 2008 R2, but only for the relational engine.  My favorite thing about this piece is that it even works with HyperV images containing SQL Server 2008 R2.

4.       Report Builder 3.0 and Reporting Services – Too many great new features to talk about in a blog and the development team already has a great blog.  But my favorite is the Report Part feature where you can take an existing report and designate report items and data regions to save and reuse in other reports.  This can amount to a huge time savings for developing new reports.  Other customers tell me they like the improved Sharepoint integration and the performance improvements in Sharepoint.  But that is not all, there is Bing map support, spark lines, and shared data sets. 

5.       Master Data Services – for data consistency across heterogeneous applications. BOL link.

6.       SSIS - Bulk Inserts with ADO.NET provider are now possible, which is extremely nice because it used to do it a row at a time.  Now, if you check the box to “Use Bulk Insert when possible” then you can see vastly improved performance when it kicks in.

7.       Setup – integrated Sharepoint mode setup is vastly improved for both Reporting Services and Analysis Services.  See the link for Powerpivot for Sharepoint to get the instructions.

8.       Excel 2010 – new additions for databases:  slicers, data cleansing, AJAX data feeds, Odata feeds and named set improvements.  To create a Named Set in Excel, once you’ve created a PivotTable against an OLAP source go to the Options tab under PivotTable Tools, and select “Fields, Items, & Sets” à “Manage Sets” à “New…” à “Create Set Using MDX…”.  Another of my new favorites within PowerPivot in Excel is the new Data Cleansing ribbon.  This allows the users to do their own clean up, which will be essential when they combine data from disparate sources.  And you can also get an OData feed from a Reporting Services report.

9.       Database Compression - now supports Unicode.  If you have Unicode date types, like nchar and nvarchar, but the data contained within is normally single byte character sets, you will see significant space savings. 

10.   PHP 5 Driver – Version 1.1 of the PHP 5 driver has a list of new capabilities, allowing access to SQL Server 2005 and SQL Server 2008.  

New and changed Editions  (more details and pricing)
·         Data Center Edition – needed for machine with more than 8 physical CPU sockets plus other improvements needed for the top SQL Server projects. 
·         Parallel Data Warehouse Edition – Massively Parallel Processing (MPP) Edition of SQL Server targeted at data warehouses in the 10’s to 100’s of terabytes.  It is an appliance where you order the hardware and software together and it comes preinstalled and preconfigured.  The minimum installation is one rack so no, you cannot install it on your laptop to play with it.
·         Standard Edition – now has the capability to do backup compression. 

INSERT INTO #CaseInSensitive (cisText)
SELECT 'Abc'
UNION ALL
SELECT 'ABc'
UNION ALL
SELECT 'ABC'
UNION ALL
SELECT 'AbC'

If we need all records where column cisText value is ‘ABC’ then here is simple query

SELECT * fROM #CaseInSensitive
WHERE cisText = 'ABC'



Ops. We got all four records as output but we need only one records where cisText = ‘ABC’. But at the time of table creation we have not set any case sensitive collation. Don’t worry; here is a query which can help us.

SELECT * fROM #CaseInSensitive
WHERE cisText COLLATE SQL_Latin1_General_CP1_CS_AS = 'ABC'


During table design we can also make a column case sensitive

CREATE TABLE #CaseSensitive (csId INT identity(1,1), csText varchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS)