Configure Database Mail in SQL Server

How to configure Database Mail in SQL Server ?

Introduction : Database mail is a SQL Server component that uses the 'Simple Mail Transfer Protocol' or SMTP to send email messages. Follow the below given steps to configure database mail:

STEP 1 : Connect to SQL Server Management Studio (Preferably run as Administrator). Go to Management and double click on Database Mail. A wizard as shown below will open. Click Next.

STEP 2 : Select Configuration Task window will appear. If you are installing Database Mail for the 1st time select the set up option radio button : Set up database mail by performing the following tasks. In case you want to modify or manage a previously configured database mail chose other options as applicable to you.

STEP 3 :  Choose Create a new account :

STEP 4 : Provide the following values

Account Name : Any Name
Description : Optional
Email address : Your Email Address
Display Name : Any Name
Reply Email Address : Optional
Server Name : Where\How to get server name is described below :
Port : Usually 25
Tick server requires SSL
SMTP Authentication Method : Choose any, I have chosen basic authentication

Now  Where\How to get server name ? The easiest approach :

Send a mail from your outlook in the format : wrongname@yourcompany.com (Please note that part of email id before @ should be wrong and not the part after @ )

You will get back MAIL DELIVERY FAILURE MESSAGE as shown below from the Server. Find the GENERATING SERVER name in this mail.

Provide this server name in Server Name.

STEP 5 : Complete the wizard. Finish.

And you Database mail is configured now.

Now lets TEST : Run this query to test :


EXEC msdb.dbo.sp_send_dbmail 

 @recipients='Provide email id here whom you want to send mail',  

 @profile_name = 'Account Name you gave in STEP 4',  

 @subject = 'Any Subject Line',  

 @body = 'Type Message Body Text',  

 @file_attachments='Attachment Full File path with file name';

The recipient must receive mail provided if all is good. To check the quue and status of mail like : Sent\Unsent\failed\Retry etc, Run this query :

USE MSDB
GO

Select * from sysmail_allitems

To check error messages :

USE MSDB;

GO

SELECT items.subject,

    items.last_mod_date

    ,l.description FROM dbo.sysmail_faileditems as items

INNER JOIN dbo.sysmail_event_log AS l

    ON items.mailitem_id = l.mailitem_id

WHERE items.recipients LIKE '%%' 

    OR items.copy_recipients LIKE '%%'

    OR items.blind_copy_recipients LIKE '%%'

GO

 In some cases if you are using ssis send mail task or using a ssis job to send mails with attachment; the SQL Server account or SQL Server Agent User may not have access to the Folder from which you are taking the file to be attached. You may get error : SQL Service account not having the correct access to the shared drive :

To solve this :

Grant access to the folder : 

NT SERVICE\MSSSQLSERVER if running SSIS Package or Running a SQL SERVER to send mails :

If using SSIS JOB then add NT Service\SQLSERVERAGENT User :

Follow the screenshots :

STEP 1 : Right click the folder containing the attachment and go to properties Click Edit then Add :

STEP 2 : Browse for NT SERVICE\MSSSQLSERVER or NT Service\SQLSERVERAGENT, change location if required; Check Names and OK: Once users added tick Full control and Modify under Allow : Apply

That's it.

Thanks for reading. Please like and follow & Suggest improvements.

Follow me on twitter @msbigyan

Thanks.

Paras Singh

Senior Consultant : BI & Analytics

SSIS 2016 - Exploring New Features

In this Blog we'll list out 11 new much awaited features in SSIS 2016.

Note : By the time I am writing this blog SQL Server 2016 (RC3) and CTP 3.3 is there in the market.

1. Incremental Package Deployment : 

In SQL Server 2005 and 2008 we used to deploy either through file system or to the msdb database. We used to deploy a package at a time and if we need to delete\update\add a new package, we simply were able to deploy that 1 package. But tin 2012 and 2014 we had to deploy the entire project containing all many packages at one go. The drawback was even if 1 package is changed the entire project had to be deployed again.

Thankfully in 2016 we can deploy entire project in one go as before and we also now have the flexibility to deploy package-by-package.

2. Separate Installation of SQL Server Database Engine and SSDT : 

Compared to SQL SERVER 2005, 2008 we used to install SQL Server Database Engine and BI Tools in one go by just selecting the features; SSDT is installed separately in SQL Server 2012, 2014 and continues in 2016.

3. Custom Logging Level in SSIS Catalog : 

Previously we had 4 built-it logging levels to choose from : Basic, Performance, Verbose and None. Now we can create multiple custom logging levels and save them to use\choose while running packages. Hence it is flexible, reusable, portable and easy to use. While creating custom logging level we have 3 categories of statistics to choose from : Executable Execution Statistics, Component Data Volume Statistics & Component Execution Statistics. 

To show custom logging levels we now also have a new view catalog.cutomized_logging_levels.

A new role ssis_logreader role has also been introduced.

4. SSDT - Visual Studio 2015 accommodates all different versions in single installation :

No more requirements for installing several versions of SSDT. In configuration properties we can now choose TargetServerVersion as SQL Server 2012, SQL Server 2014 or SQL Server 2016.

5. Column Name in error output : 

Now we have full path to error (Error Column - Description) column in output not just column id. Great help in debugging and identify the bad columns.

6. Package Parts : 

We can now create a new control flow package part file (.dtsxp)  and save it for reuse if something is to be reused multiple times. It will reflect in Package Parts folder and also in SSIS Control Flow Tool Box under package parts. Note that it is visible only for that project only.

7. Explicit Support for Excel 2013 and Excel 2016

8. Connection Mangers for HDFS : 

For performing common HDFS operations we have connection managers to connect to hadoop clusters and tasks.

9. SQL Server Import Export Wizard : 

Now we can import\export data to\from Azure Blob storage using Import Export Wizard

10. Project Upgrade : 

When we upgrade from prev version to current version project level connection mangers work as expected, Layout and annotations remain unchanged.

11. Enable Always on support for Integration services catalogs :

Thanks you for reading. Please like and comment
Paras Singh

Follow me on twitter @msbigyan

Power Map

 Power Map

Software Requirements:

• If you have any subscription for Microsoft Office 365, you have access to Power Map for Excel as part of the self-service business intelligence tools.
• Whenever any new Power Map features and performance enhancements are released, you'll get them as part of your subscription plan.
• If you have Office 2013 Professional Plus or a standalone version of Excel 2013, you can download and use the Power Map Preview for Excel 2013 as an add-in.
• You can download Power Map Addin from this link:
• http://www.microsoft.com/en-in/download/details.aspx?id=38395

What is Power Map?

• Microsoft Power Map for Excel 2013 is a three-dimensional (3-D) data visualization tool that lets you look at information in new ways. You can discover insights that you might not see in traditional two-dimensional (2-D) tables and charts.
• With Power Map, you can plot geographic and temporal data on a 3-D globe, show it over time, and create visual tours you can share with other people.

You’ll want to use Power Map to:

• Map data:  Plot more than a million rows of data visually on Bing maps in 3-D format from an Excel table or Data Model in Excel.
• Discover insights: Gain new understandings by viewing your data in geographic space and seeing time-stamped data change over time.
• Share stories:  Capture screenshots and build cinematic, guided video tours you can share broadly, engaging audiences like never before. Or export tours to video and share them that way as well.

You’ll find the Map button on the Insert tab of the Excel ribbon, as shown in this picture:

Let us create a Power map report:

STEPS:

STEP 1: Open Excel 2013 having your data with at least one column as country\State\Province\Zip Code\City name\Address (which Bing Map can identify\locate)

STEP 2: Click on Insert à Map à Launch Power Map\Add Selected Data to Power

STEP 3:

Power Map uses Bing to geocode data based on its geographic properties. After a few seconds, the globe will appear next to the first screen of the Layer Pane.

This window has 4 visible parts:

•  Power Map Ribbon: On Top
• Tours Window: Left Pane
•  Layer Pane : Right side
•  Map Window : Middle

      Tours window will open if you have already created a power map earlier and want to add\edit new\existing tours as shown below :

    

     In layers pane on left side; we can choose geography based on available data:

     Based on our selection Geography is visible as dots on the Map.

    STEP 4:

    

    Click NEXT and start Exploring:

    Select values, Category, Time (If any column have time based data) and create nice visualizations.

    Additionally you can create Tours, Add more layers, Create videos, create flat maps etc…

   

    

    Let’s discuss the available options on ribbon:

           TOUR:

1.       Play Tour : Switch to tour view and watch it play

2.       Add Scene: Add a new scene to the list of existing scenes in your tour.

3.       Create video: Turn the tour you have created into a video so that you can share it.

4.       Capture Screen: Take a static picture of your current map to put it on the clipboard so that you can paste it somewhere else.

MAP:

5.       Themes: Pick a theme to give your tour instant personality or a more professional look. Each scene can have different theme and a theme can have different colors. Some themes even have satellite images for the globe.

6.       Map Levels: Show or hide the labels on the map.

7.       Flat Map: Switch between globe and flat map format.

8.       Find location: Go to any city, address, state, or other geographical location on the map. Just enter the one you want and search by typing the name of the location you would like to find.

LAYER:

9.       Add layer: Add another data layer to the selected scene.

10.   Refresh data: Update all layers of all scenes to show the most current data.

11.   Data Shapes: Change the shape of the columns on the map.

INSERT:

12.   2D Chart: Show the top or bottom 100 locations by value for any data layer on a 2D Chart, and quickly navigate to each location.

13.   Text Box: Add text to a scene that stays on the screen when playing your tour. Resize it or move it to the place you want.

14.   Legend: Show the legends for all data layers in the scene. You can turn individual legends ON or OFF afterwards.

TIME:

15.   Time Line: Show or hide the Time Line control. Use this to play a tour over time. This option is available when a time field is added to a Scene’s time box.

16.   Date & Time: Show or hide the date or time you have added to the time box in the scene. This option is available when a time field is added to a Scene’s time box.

VIEW:

17.   Tour Editor: Show or hide the tour editor. Use the tour editor to view all scenes in your tour and to rename your tour.

18.   Layer Pane: Show or hide the layer pane. Use the Layer pane to manage layers, add data to layers and change settings.

Let me add some visuals of maps and videos which I have created to attract your interest in Power Map :

Note : India Population data used in this Map has been downloaded from Public Catalogue available for download from govt website using power query. Watch out for my blog on Power Query Coming Soon...

There are plenty of other options for you to explore. 

So Get...Set...GO...

Enjoy using Power Map!!!

Thanks,

Paras Singh

04th Feb 2015

07:18 PM IST

Click here to Follow me on Linkedin

Follow me on Twitter @MSBIGYAN

Power View Reporting using SSAS Tabular Model

Agenda :

1. Create a simple SSAS Tabular Project.
2. Deploy the SSAS Tabular Project on Tabular Instance of SQL Server Analysis Services.
3. Create a Power Pivot Gallery on SharePoint 2013.
4. Create a Microsoft Business Intelligence Semantic Model (BISM) for Power View.
5. Start Power View Reporting
6. Exploring Power View

Software Requirements :

1. SQL Server 2012
2. SQL Server Analysis Services - Tabular Instance Installed.
3. SQL Server Data Tools (SSDT)
4. SharePoint 2010\2013
5. AdventureWorksDW2012

STEP BY STEP DESCRIPTION WITH SCREENSHOTS :

STEP 1 : Create a simple SSAS Tabular Project.

SSAS Tabular Model Introduction

• Was introduced in SQL Server 2012 Analysis services.

• In-memory databases which uses analytic engine named VertiPaq.

• VertiPaq is an in-memory column store engine.

• Delivers fast access of Data to client applications and end user tools like Excel, Power View, etc.

• Easy and Faster Development of Tabular model.

• Performance is better with in-memory data.

• DAX is used to create Calculated Columns, KPI’s etc.

• Power View reporting is supported(With SQL Server 2012 SP1 Cumulative upgrade 4 and above i.e. SQL Server 2014 Multidimensional support for Power View is also there)

• Can be deployed on SQL Server 2012 Tabular mode installation.

• Two data access modes are Cached Mode and direct query Mode.

• In direct query mode – Direct Query with in-memory there is direct interaction with underlying data source.

• Choose Tabular Model when you have to do reporting(eg. Power View) over a relational database as underlying data source.

• Uses BI Semantic Model  (BISM)

• BISM provides easy access of data from relational databases, OData feeds , files, excel, Cloud Services, Line-of-Business Applications to all client tools like Excel, Power View and Power Pivot.

• We use SQL Server data Tools – SSDT to create Tabular Model Projects.

• Limitations: No many to many relationships; No named Query.

STEPS :
1.0 Start --> All Programs --> SQL Server Data Tools --> File --> New --> Project.

1.1 Under Installed Templates : Business Intelligence --> Analysis Services --> Analysis Services Tabular Project --> Give Name (AdventureWorksSSASTabularProject) --> Choose Location --> OK.

1.2 An empty project as shown below is created :

1.3 Model --> Import From Data Source :

1.4 Table Import Wizard Opens for Connecting to a Data Source --> Select Microsoft SQL Server from a list of Following options :

1.5 To connect to Microsoft SQL Server Database : Provide Friendly connection name, Server Name, Authentication Method, Database Name(AdventureWorksDW) --> Test Connection --> Next.

1.6 Provide Impersonation Information. This is the credentials used by Analysis Server to connect to the data source when importing and processing data. In this case I have used windows user name and password.

1.7 Choose how to import the data --> In this case select radio button for Write a query that will specify the data to import --> Next

1.8 Give a Friendly Query Name(ProductSalesQuery) and Specify any SQL query in SQL Statement --> Validate --> Finish. In this case a query on AdventureWorksDW database.

SELECT  DP.[ProductKey]

  ,DP.[EnglishProductName]      

       ,FIS.[OrderQuantity]

       ,FIS.[SalesAmount]

FROM [dbo].[FactInternetSales] FIS INNER JOIN [dbo].[DimProduct] DP 

ON FIS.[ProductKey] = DP.[ProductKey]

1.9 Import operation starts. See Status for Success and Number of rows transferred.

1.10 AdventureWorksSSASTabularProject is created and Grid View looks like this:

1.11 You can also see the Diagram View :

1.12 If you wish to modify the Source Data Query which we have written earlier; You can do it from here : In Grid View --> Click on ProductSalesQuery to see table properties --> Source Data(Table Property) --> Click to edit. As shown below I am keeping the query unchanged but you can do necessary editing in the query here.

If you wish you can make other modifications to this model like changing column names, add new columns, create measures, KPI etc...

STEP 2 : Deploy the SSAS Tabular Project on Tabular Instance of SQL Server Analysis Services.

2.0 In the Solution Explorer Section Click on Model.bim. See the Model.bim File Properties. You can Change the DirectQueryMode to ON. Tabular database in DirectQueryMode fetch query results from the data source, not the vertipaq cache; Although some functionality is limited in this mode. So choose wisely. Check if Workspace Server Property is correctly pointing to your SSAS Tabular instance server name.

2.1 Right click AdventureWorksSSASTabularProject --> Properties(or ALT+ENTER)

2.2 Project property window opens. Click on Deployment under Configuration Properties window. Change Query Mode property to DirectQuery with In-Memory. This property needs to be selected when DirectQueryMode is ON. QueryMode specifies the source from which query results are returned, either the In-Memory cache or DirectQuery data source. Clock OK.

2.3 Right click AdventureWorksSSASTabularProject. Click Deploy.

2.4 Deployment Starts : On Deployment Success. Click Close.

2.5 Open SSMS. Connect to the tabular instance of Analysis services : 

2.6 In databases section you will find two instances. One the AdventureWorksSSASTabularProject database and 2nd the Workspace Database which is database name suffixed with a randomly generated code. It is the name of the database used for storing and editing the temporary in-memory model for the current BIM file.

STEP 3 : Create a Power Pivot Gallery on SharePoint 2013.

Power Pivot Gallery is a special purpose SharePoint server document library that provides rich preview and document management for data model connections, published Microsoft excel workbooks that contain power pivot data models etc. 

Data Models or connections to models can be in SharePoint server document library or Power Pivot Gallery

3.0 Login to your SharePoint portal. Click on Home Tab--> Add lists, libraries, and other apps.

3.1 Click on power pivot gallery :

3.2 Provide a Name. Here I have given AdventureWorks Power Pivot Gallery. Click Create :

3.3 AdventureWorks Power Pivot Gallery is created :

STEP 4 : Create a Microsoft Business Intelligence Semantic Model (BISM) for Power View.

4.0 Files --> New Document --> Report Data Source

4.1 On Data Source Properties Page.

Provide Name : AdventureWorksConnection, 
Select Data Source Type as Microsoft BI Semantic Model for Power View from drop down. Provide connection string : Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorksSSASTabularProject;Data Source=localhost\tabular
Credentials : Windows authentication (integrated) or SharePoint user or other options
Test Connection
Availability : Check Enable this data source. 
Click OK.

4.2 AdventureWorksConnection is created :

Click on the created connection and start Power View reporting.

Alternate Approach:

We can also create BISM for Power View in SharePoint server  Document Library.

Click on Documents TAB --> Files --> New Document --> Report Data Source. 

Follow STEP 4.1 as illustrated in earlier approach:

We will see AdventureWorksConnection among the list of All documents as shown below.

 Click on the 3 dots ... to the right of Newly created AdventureWorksConnection. Click Create Power View Report and start power view reporting.

STEP 5 : Start Power View Reporting :

• Power View is an interactive, easy and quick data exploration, visualization and presentation ad-hoc reporting Tool. 
• Power view in SharePoint is browser- based silverlight application, a feature of SQL Server 2012 SP1 reporting services Add-in for Microsoft SharePoint server 2010 and 2013. 
• Creating, opening, and saving Power View reports(i.e. RDLX files) all takes place in Sharepoint server 2010 or 2013.

There are 2 versions of Power View :

1. Power View for Excel 2013

2. Power View for SharePoint Server 2010 or 2013(This is being explained in this blog)

We can create power view reports from a Data Model. This data model can be :

1. Power Pivot Model

2. SSAS Tabular Model

3. SSAS Multidimensional Model(SQL Server 2012 SP 1 Cumulative Upgrade 4 and above)

4. Other Data Models

Data Models or connections to models can be in SharePoint server document library or Power Pivot Gallery.

STEPS for creating and exploring Power View reporting in SharePoint:

5.0 : Click on the connection created in Power Pivot Gallery or  SharePoint server document library as illustrated in STEP 4. This page opens :

5.1 This contains 5 important sections :

1. Power View Ribbon : Home, Styles, and Chart\Tables Tols like Design and Layout etc.

2. Views Section : Contains all different slides\views of a report

3. Field List Section : Lists tables and columns, calculated columns, measures, KPIs etc

4. Main Power View Report Visualization Section : Here we see the data and charts

5. Filters Section : Can filter the data here.

5.2 To start reporting Select the check box against the columns which you want to view in your report or Drag and drop that column in the Main Power View Report Visualization Section.

• For this example I selected\Draged and dropped EnglishProductName and SalesAmount. 
• As I do this I see a Tabular report with 2 columns being created with EnglishProductName and their corresponding sales amount.
• In FIELDS section I can See EnglishProductName and sum(sales amount).
• Tabular reports also have a TILE BY option which we may or may not use in our visualization.
• In Power view Ribbon Section we now also have Design section under Table Tools
• Through design section we now have the option of creating Table or Matrix reports or Column, Bar, Line, Scatter, Charts, Maps, Cards etc.
• Depending on Table, Matrix, Chart, Map, Card, Scatter Chart etc. options changes which you need to explore.

Till now the Power view report and the 5 different sections looks like this :

5.3 Follow the steps below or create another visualization  of your choice and need :

• Add a Title to your report.
• Click elsewhere in the report visualization section other than the Table report created earlier.
• Now select EnglishProductName and OrderQuantity
• A table report with two columns EnglishProductName and OrderQuantity is created by the side of previously created Table.
• To add a different flavour to our report lets make this newly created table report as a column chart.
• For this just click on the Column Chart under Design.
• The Table converts to a column chart with a sub-title OrderQuantity by EnglishProductName. 
• EnglishProductName on X-Axis and OrderQuantity (in thousands) appears on Y-Axis.
• The height of Column bars indicate the OrderQuantity in thousands for each EnglishProductNames on X-Axis.
• In the section under field list we in Values section we have sum(OrderQuantity); EnglishProductName in AXIS section.
• We also have the options of TILE BY, LEGEND, VERTICAL MULTIPLES, HORIZONTAL MULTIPLES.
• In power view ribbon section we now have Design and Layout under Chart Tools which we can use to change design and layouts

Till now the Power view report and the 5 different sections looks like this :

5.4 We now have created a simple power view report. Now Click on save icon or save as and provide a name in file name (Adventure Works Reports.rdlx). Click save.

5.5 Connection and Report looks like this in the Power Pivot Gallery :

STEP 6 : Exploring Power View

6.1 Edit a Report

6.2 Add a new view or a duplicate view

6.3 Changing data in the source table and check if data refreshes in Power view report :

• In the new view created above create a Total Order Quantity Report as shown below. We can see that the Total Order Quantity before making any changes in the source data is 60398.

• Open SSMS. Login to your database engine. Change OrderQuantity of any product in the Table dbo.FactInternetSales(AdventureWorksDW database) from 1 to 200. As shown below :

• Go to Power View Report just created and Click on Refresh (Top Left Corner). Observe that Order Quantity changes from 60398 to 60597

This automatic refresh from Power View Report itself happens because while creating Tabular Model(Refer back steps 2.2 and 2.6) we had set DirectQueryMode as ON and Query Mode property to DirectQuery with In-Memory.

6.4 Export to Power Point :

6.5 Filters : Create filters here

6.6 Sort by a particular Column in asc or desc order

6.7 Data Mining within the Table and Chart created in our Report

There are many other options to be explored in power view. Microsoft releases updates for Power view every month. So Power view as a product is evolving. Watch-out for updates and my further blogs on  Topics related to Power BI, Power View, Power Query and Power Map.

Note : Further edits are possible in this blog.

Thank you for reading. Happy Learning :)

@MSBIGYAN

Paras Singh on Linkedin