Capture multiple errors in TRY CATCH by using THROW statement

February 4, 2015 Leave a comment

This post relates to my earlier post [link] where I mentioned on benefit of using THROW clause with same SQL examples.

The THROW clause was introduced in SQL Server 2012 and may be replacing the RAISERROR function in near future.

Normally the SQL statements returns single error, but some SQL statements returns more than one error message when they go wrong due to some reason or exception.

–> On executing the below BACKUP statement in SSMS we can see we get two errors:

BACKUP DATABASE [AdventureWorks2012] 
TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'

The above code throws 2 errors with Error-Message IDs 3201 & 3013, as shown below:

error messages:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘E:\FOLDER_NOT_EXISTS\test.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.


-> But when we want to track these errors by using RAISERROR function it just returns the last (single) error message and its details, and the previous error message details are not returned by this function.

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	DECLARE @msg VARCHAR(1000) = ERROR_MESSAGE()
	RAISERROR(@msg,16,0)
END CATCH

Here, only 1 error message will be returned:

error messages:
Msg 50000, Level 16, State 0, Line 7
BACKUP DATABASE is terminating abnormally.


–> With the new THROW clause you won’t see any issue of omitting the previous errors, as it returns all error details thrown by the SQL Statement itself.

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	THROW;
END CATCH

The above statement throws both the error details as we saw in the first example:
error messages:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘E:\FOLDER_NOT_EXISTS\test.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.


Thus, if you are on SQL Server 2012 and above you must consider using THROW clause instead of the RAISERROR function.

Microsoft’s upcoming USCMO Webcasts | Digital Events

January 16, 2015 Leave a comment

Microsoft announced their new and improved USCMO webcast programs!

The USCMO (U.S. Central Marketing Organization) team manages and optimizes programs through the customer lifecycle in order to drive business results of conversion, pipeline, and managed costs.

Each webcast will stream live with interactive Q&A and will be made available on demand.

–> Please check the webcasts below you might be interested to register:

Webcast Title Webcast Date Registration URL
Protect Your Business Against Online Fraud 1/20/2015 http://aka.ms/protectblog
Social in the Enterprise 1/21/2015 http://aka.ms/enterpriseblog
Windows Server 2003 Migration: Hardware Modernization 1/22/2015 http://aka.ms/WS03blog
It’s a New Year, Be Ready to Adapt 1/22/2015 http://aka.ms/adaptblog
HIPAA Compliant Cloud Solutions with Microsoft BAA 1/23/2015 http://aka.ms/BAAblog
Announcing the Enterprise Cloud Suite 1/26/2015 http://aka.ms/suiteblog
Get a fresh start in 2015 with new Windows devices 1/28/2015 http://aka.ms/windeviceblog
Need fast AND affordable? Why not try SQL Server? 1/29/2015 http://aka.ms/SQLserverblog
Mobile Productivity in the Modern Workplace 2/4/2015 http://aka.ms/mobileblog
Windows Server 2003: Most Common Application Migration Concerns 2/5/2015 http://aka.ms/commonblog
Enabling Customer Insights Using Business Analytics 2/12/2015 http://aka.ms/customerblog
Windows Server 2003: Security Risk and Remediation 2/18/2015 http://aka.ms/remeblog
The Connected Workforce 2/18/2015 http://aka.ms/connectedblog
Fine Tune Your Supply Chain with Better Insight 2/19/2015 http://aka.ms/fineblog

Thanks !!!

2014 in review

December 30, 2014 Leave a comment

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here's an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 330,000 times in 2014. If it were an exhibit at the Louvre Museum, it would take about 14 days for that many people to see it.

Click here to see the complete report.

What is ODS (Operational Data Store) and how it differs from Data Warehouse (DW)

December 17, 2014 Leave a comment

I see lot of people discussing about ODS, and citing their own definitions and ideas about it. Some people also use the name as a synonym for a Data Warehouse or Factory Database. Thus, at times it becomes very difficult to tell or convince people while you are designing or architecting a DW/BI solution.

–> So, I thought to give some time to explain what actually an ODS is.

Simple definition: An Operational Data Store (ODS) is a module in the Data Warehouse that contains the most latest snapshot of Operational Data. It is designed to contain atomic or low-level data with limited history for “Real Time” or “Near Real Time” (NRT) reporting on frequent basis.

Detailed definifion:
– An ODS is basically a database that is used for being an interim area for a data warehouse (DW), it sits between the legacy systems environment and the DW.
– It works with a Data Warehouse (DW) but unlike a DW, an ODS does not contain Static data. Instead, an ODS contains data which is dynamically and constantly updated through the various course of the Business Actions and Operations.
– It is specifically designed so that it can Quickly perform simpler queries on smaller sets of data.
– This is in contrast to the structure of DW wherein it needs to perform complex queries on large sets of data.
– As the Data ages in ODS it passes out of the DW environment as it is.

–> Where does ODS fits in a DW/BI Architecture?

ODS_DW

–> Classes of ODS (Types):

Bill Inmon defines 5 classes of ODS shown in image below:

Class-1 ODS would simply involve Direct Replication of Operational Data (without Transformations), being very Quick.
– Whereas Class-5 ODS would involve high Integration and Aggregation of data (highly Transformed), being a very time-consuming process.

ODS2

Self Service BI by using Power BI – Power Pivot (Part 2)

October 29, 2014 Leave a comment

After a long pause I’m back again to discuss on Power BI.

In my previous Power BI Series first part [link] I discussed about the first component of Power BI, i.e. Power Query and how to use it to discover and gather data.

Power Pivot lets you:
1. Create your own Data Model from various Data Sources, Modeled and Structured to fit your business needs.
2. Refresh from its Original sources as often as you want.
3. Format and filter your Data, create Calculated fields, define Key Performance Indicators (KPIs) to use in PivotTables and create User-Defined hierarchies to use throughout a workbook.

And here in second part I will discuss about few of these features.

–> The benefit of creating Data Model in Power Pivot is that Power Pivot Models run in-memory so that users can analyze 100’s of millions of rows of data with lightning fast performance.

All you need is Microsoft Excel 2013 to create your Data Model. Check this [link] to troubleshoot if you don’t see POWERPIVOT option in Excel ribbon.

–> Creating Data Model:

To create a Data Model you need a Data Source, so we will use SQL Server as a Data Source and I’ve setup AdventureWorksDW2012 Database for our hands-on. Click [here] to download AdventureWorksDW2012 DB from CodePlex.

1. Open Excel, and go to POWERPIVOT tab and click on Manage, this will open a new PowerPivot Manager window.
PowerPivot01

2. Now on this new window, click on From Database icon and select From SQL Server from the dropdown, this will open a Table Import Wizard Popup window.

3. Provide SQL Server Instance name that you want to connect to. Select AdventureWorksDW2012 Database from the Database name dropdown, and click Next.
PowerPivot02

4. Click Next again and select the required Tables (10 selected), click Finish.
PowerPivot03

5. Make sure you get Success message finally, click Close.
PowerPivot04

6. In the PowerPivot Manager window you will see many tabs listing records. Click on Diagram View to see all the tabs as tables and relations between them. This is your Power Pivot – Data Model:
PowerPivot05


 

–> Now as your Data Model is ready, you can create Pivot Reports in Excel, let’s see how:

1. Go to the PowerPivot Manager window and click on PivotTable icon and then select PivotTable from the dropdown.
PowerPivot06

2. The control moves to the Excel sheet, select Existing Worksheet on the Popup.

3. Now select following columns form the PivotTable Fields list:
– DimGeography.EnglighCountryRegionName
– FactInternetSales.SalesAmount
This would give you Total sales across Regions in the Worksheet

4. Let’s add some Slicers to this:
4.a. Click on PIVOTTABLE TOOLS – ANALYZE, here click on Insert Slicer. On ALL tab, select DimDate.FiscalYear column. This will add Year slicer to the report.
4.b. Now again click on the PivotTable Report, you will see the PIVOTTABLE TOOLS on the ribbon bar again. Select Insert Slicer again and select DimProductCategory.EnglighProductCategoryName column.

You can align, move, resize the report, slicers and beautify the report as you want, as shown below:
PowerPivot07

This way you can add Graphs, Charts and create very impressive Reports UI as per your requirements.

This Power Pivot – Data Model can also be used to create Power View Reports, which we will cover in next part of this series.

Thanks!!!

Follow

Get every new post delivered to your Inbox.

Join 425 other followers