Excel - How to Sort rows in the Pivoted table

In my previous post , I explained about adding Pivot table and chart .

If you click on the drop-down arrow of Row Label , you will find the sort options as shown below :

Excel - How to add Pivot Table and chart

In Excel , We can find PivotTable  and PivotChart options - > Under Insert tab - > PivotTable

How to add Pivot Table ?

T-SQL - INSERT Statement with Columnslist inside Stored procedure

This article shares the information about what happens when INSERT Statement is used  inside Stored procedure with & without Columns list .

--Create Sample tableCREATE TABLE Test (Id INT , Name VARCHAR(20))

--Scenario 1 - INSERT statement Without Columnslist inside Stored procedure

SSRS - PIE chart

In this article , let us see an example for creating SSRS PIE chart .

Let us try to create Pie chart like in MSDN forum users profile .

Drag & drop , Pie chart on to the report body :

From Toolbox - > Report Items - > Chart - > Shape - > Pie
or Under Design pane - > right click on report body - > Insert - >  Chart - > Shape - > Pie

Excel - How to pull data from SQL Server

This article is about pulling data from SQL Server into Excel sheet .

Open Excel - > Under Data tab - > From Other Sources - > click the drop-down - >  click on From SQL Server .

SSRS - How to remove space (because of no data) between Bars in Column Chart

In SSRS , because of no data there can be space between bars in Column Chart .

Try this MSDN SSRS forum discussion for clear explanation on how to remove space (because of no data) between bars in Column Chart .

SQL Server - Tracking Object deleted informations using Extended Events

This article is about tracking Object deleted informations using Extended Events .

USE AdventureWorks2012

--Create sample table to test object_deleted event
CREATE TABLE TEST_EE_object_deleted (Id INT)

SQL Server - Getting started with Extended events

This article is about SQL Server Extended events . In this article , I have provided MSDN links which will provide complete & accurate information about each topics related to Extended events .

SQL Server Extended Events (Extended Events)

                                  is a general event-handling system for server systems.

Introducing SQL Server Extended Events

Overview on Extended Events :

We can manage  & control Extended events in SQL Server using T-SQL ,
also we have GUI option  - > Under Object Explorer - >  Management - >  Extended Events

A post about me (Sathyanarrayanan S) on TechNet Blogs -> WikiNinjas - Official Blog of TechNet Wiki

SSRS - How to make copy of RDL file

In some cases , we might need copy of RDL file , say .,

For example : you had completed developing your report and only alignment & formatting changes are pending and you don't want your final version of the report to be disturbed because of these changes .

Remember we do have the undo/redo option with CTRL+Z .

But still if you need to make a copy of RDL file , it is simple and easy

SQL Server ORDER BY using Alias name of the column mentioned in the SELECT statement

This article shares information about  sorting resultset of T-SQL query using ORDER BY clause with alias name of the column mentioned in the SELECT statement .

Example :


--Without ORDER BY

--With ORDER BY AliasName
SELECT Id AliasName
FROM  @Tmp
ORDER BY AliasName

The reason that ORDER BY clause works with alias name of the column mentioned in the SELECT statement where as WHERE , GROUP BY & HAVING clause cannot is  ORDER BY comes after SELECT statement in the logical query processing phase .

SQL Server - Different ways to shred XML into table

 SQL Server - Different ways to shred XML into table :

1.) Using OPENXML or nodes()

2.) SSIS - XML Source task

3.) Powershell  

4.) Using VB/C# script (Script task in SSIS)

5.) Using SQL XML Bulkload API

 Reference links :

SQL Server XML Bulk load examples

SQL Server - Import/Shred XML from XML file placed in a folder into table

SSIS - Shred data from XML file in folder into columns of a table

Best Way to Shred XML document and insert the result set into a table in SQL Server

SSRS - How to calculate total of dynamic columns

SSIS Error code 0x80131537 / 2146233033

When I was trying to shred XML into table using SSIS - Data Flow task with XML Source task and OLE DB destination task , I ended up with error code 0x80131537 / 2146233033 because input XML data fails the XSD validation (data type mismatch) .

I searched for the error description on MSDN site - Integration Services Error and Message Reference , but i did not find the error description for this particular error code .
So I started a discussion on MSDN SSIS forumn , refer this thread for explanation on error code 0x80131537 / 2146233033 .