Search

Advertisements

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))
GO


--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
GO


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


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 :


DECLARE @Tmp TABLE (Id INT)
INSERT @Tmp SELECT 3
INSERT @Tmp SELECT 2
INSERT @Tmp SELECT 1

--Without ORDER BY
SELECT * FROM @Tmp

--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
http://www.allaboutmssql.com/2013/08/sql-server-xml-bulk-load-examples.html

SQL Server - Import/Shred XML from XML file placed in a folder into table
http://gallery.technet.microsoft.com/scriptcenter/SQL-Server-ImportShred-XML-7f3baa40#content

SSIS - Shred data from XML file in folder into columns of a table
http://www.allaboutmssql.com/2013/08/ssis-shred-data-from-xml-file-in-folder.html

Best Way to Shred XML document and insert the result set into a table in SQL Server
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/52769628-58e3-4d3f-869b-519f61d86da5/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 .

Advertisements