All about SQLServer: 2013

TSQL Script to Find the Names of Stored Procedures that Use XQUERY

Below scripts can be used to find the names of Stored Procedures that use XQUERY :

SQL Server - Policy based management - System Views

Below query will provide the Created policies , Conditions imposed and Policy evaluation history details :

USE msdb
GO

SQL Server - Policy based management - Enforcing naming convention for Database and its objects

This post is about enforcing naming convention for database and its objects through Policy based management in SQL Server .

Naming convention can be enforced for below list of objects :

Note : But Policy evaluation mode differs for each object .

APPLICATION ROLE

ASYMMETRIC KEY

CERTIFICATE

DATABASE ROLE

DEFAULT

INDEX

RULE

SCHEMA

SQL ASSEMBLY

STORED PROCEDURE

SYMMETRIC KEY

SYNONYM

TABLE

TRIGGER

USER

USER DEFINED FUNCTION

USER DEFINED TYPE

VIEW

XML SCHEMA COLLECTION

In this post , let us see an example for enforcing naming convention while creating Stored procedure .

Excel - Transpose Rows and Columns

In Excel , we can transpose rows into columns and vice versa .

Copy the rows or columns to be transposed , while pasting - > Paste Special ...

Check the option Transpose as shown below :

SQL Server - Capturing Missing Join Predicate for queries using Extended Events

missing_join_predicate , one of the event - " Occurs when an executed query is missing a join predicate. Use this event to identify a query that may perform slowly because of the missing predicate. This event only occurs if both sides of the join return more than one row " .

SELECT * FROM sys.dm_xe_objects WHERE name = 'missing_join_predicate'

--Create Session to track missing_join_predicate event with below mentioned Actions

TSQL Script to Generate Create ColumnStore Index Script

This article is about the TSQL script which can be used to generate create ColumnStore Index script from the database .

SET CONCAT_NULL_YIELDS_NULL OFF;

SQL Server Data Quality Services

This article is about cleansing and formatting data using SQL Server Data Quality Services .

In this post , I am going to show on how to cleanse & format data in the columns of SQL Server table . To do this , first we have to create Knowledge base and then Data quality project .

Creating Knowledge base is nothing but giving hint of source data to DQS and then setting rules on how data should be formatted by creating Domains for each columns .

After creating Knowledge base , create Data quality project making use of knowledge base on our source data and then after deciding on the recommendations provided by DQS , we can export the cleansed (corrected) & formatted data to new table .

Note : click on images to get enlarged view

Handling XML data in SQL Server

XML :

Extensible Markup Language (XML) has been widely adopted as a platform-independent format for data representation.

Before getting deep into XML, a little introduction to the structure of XML.

Sample XML fragment:

<?xml version = "1.0" encoding = "UTF-16"?>
<!—Student Information -->

<Studentinfo>
  <Student>
    <StudentID>1</StudentID>
    <StudentName>Sathya</StudentName>
    <CourseID>1</CourseID>
  </Student>
  <Student>
    <StudentID>2</StudentID>
    <StudentName>Deepak</StudentName>
    <CourseID>2</CourseID>
  </Student>
  <Student>
    <StudentID>3</StudentID>
    <StudentName>sathish</StudentName>
    <CourseID>3</CourseID>
  </Student>
</Studentinfo>

In the above sample XML fragment ,

<Studentinfo> is the root node

<Student> is the element node,so in the above XML fragment,we havee 3 element nodes.

<StudentID> ,

<StudentName> ,

<CourseID> .. are the attribute nodes

1,sathya,1,.. are the attribute values.

<Student> is the start tag & </Student> is the end tag of the element node.

Similarly,

<Studentinfo> is the start tag & </Studentinfo> is the end tag of root node.

To put it simple in database terminologies,consider

root node as the database name,element node as the table name,attribute node as column name & attribute values as column values.

<!—Student Information --> are XML comments denoted by  delimiters

<?xml version = "1.0" encoding = "UTF-16"?> are XML processing instructions marked by <? and ?> delimiters.

A processing instruction is a means to provide additional metadata to a processing application.

FOR XML clause:

For getting relational data in the form of XML, FOR XML clause was introduced.

Using FOR XML clause,we can represent relational data in the form of XML in two ways:
1.) Attribute Centric
2.) Element Centric

/*******************************Sample data ***********************************/

Let us create sample datas for all below XML examples:

SSIS - Merge Transformation

As stated on MSDN BOL :

The Merge transformation combines two sorted datasets into a single dataset .

By including the Merge transformation in a data flow, you can perform the following tasks:

Merge data from two data sources, such as tables and files.
Create complex datasets by nesting Merge transformations.
Remerge rows after correcting errors in the data.

In this article , let us see an example for Merge Transformation .

SSIS - WMI Event Watcher Task

As Stated here , we can use the WMI Event Watcher task for the following purposes:

Wait for notification that files have been added to a folder and then initiate the processing of the file.
Run a package that deletes files when the available memory on a server drops lower than a specified percentage.
Watch for installation of an application, and then run a package that uses the application.

Getting Started with SQL Server Analysis Services

What is a Data warehouse ?

http://en.wikipedia.org/wiki/Data_warehouse

What is Dimensional Fact Model ?

http://en.wikipedia.org/wiki/Dimensional_Fact_Model

Star schema :

http://en.wikipedia.org/wiki/Star_schema

SQL Server - FOR XML clause can be used within CTE

In SQL Server - FOR XML clause can be used within CTE . But it is stated in MSDN BOL as

The following clauses cannot be used in the CTE_query_definition:

ORDER BY (except when a TOP clause is specified)
INTO
OPTION clause with query hints
FOR XML
FOR BROWSE

To make it more clear , I raised a question on MSDN T-SQL forum - http://social.technet.microsoft.com/Forums/en-US/1b5d3c1a-0083-4f47-b6b0-35f86cdc2291/for-xml-clause-with-cte?forum=transactsql

Here is the Connect case [Feedback] for change in MSDN BOL documentation .

SSRS - Sorting in Tables , Matrices and Charts

This article is about SSRS reports - Sorting in Tables , Matrices and Charts .
Refer my post on TechNet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/20587.ssrs-sorting.aspx

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 table
CREATE 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

Interview with a Wiki Ninja: Sathyanarrayanan S ,MCC and TechNet Guru Medal Winner by Maheshkumar S Tiwari

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 clause

As stated here ,

ORDER BY clause -

Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.

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

SQL Server - ORDER BY with CASE statement - Example

T-SQL - Using Order By clause to return resultset with exact matches first and then partial matches

SQL Server 2012 - OFFSET and FETCH (Enhancement in ORDER BY clause)

Reference link :

http://technet.microsoft.com/en-us/library/ms188385.aspx

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

Refer my post on Technet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/20031.ssrs-how-to-calculate-total-of-dynamic-columns.aspx

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 .

SQL Server - ORDER BY with CASE statement - Example

In this article , let us see an example for ORDER BY with CASE statement

Consider below sample data :

DECLARE @Tmp TABLE (Id VARCHAR(5),Name VARCHAR(5))
INSERT @Tmp SELECT 1,'C'
INSERT @Tmp SELECT 2,'B'
INSERT @Tmp SELECT 3,'A'

If we are required to sort based on column and if the name of the column is passed as input , then we can make use of ORDER BY with CASE statement

--Sort Input
DECLARE @Sortby VARCHAR(20) = 'Name'

SELECT * FROM @Tmp
ORDER BY CASE WHEN @Sortby = 'Name' THEN Name 
              ELSE Id END 

--Sort Input
SET @Sortby = 'Id'

SELECT * FROM @Tmp
ORDER BY CASE WHEN @Sortby = 'Name' THEN Name 
              ELSE Id END

Reference links :

SSRS - Divide by Zero error - Myth of IIF

In SSRS , IIF evaluates both True and False part irrespective of condition imposed .

Because of that , expressions using IIF will lead us to error in some cases .

Two common scenarios where IIF causes problem :

i. ) Divide by zero .
ii ) Argument 'Month' is not a valid value .

i . ) Divide by zero :

Refer below two MSDN blogs for explanations on how IIF causes divide by zero error and workaround for the same.

http://blogs.msdn.com/b/sqlforum/archive/2011/02/28/faq-why-does-the-attempted-to-divide-by-zero-error-still-happen.aspx

http://blogs.msdn.com/b/bwelcker/archive/2006/09/26/end-of-amnesia-_2800_avoiding-divide-by-zero-errors_2900_.aspx

SQL Server - Concatenating string / integer with NULL

In SQL Server , SET statement - CONCAT_NULL_YIELDS_NULL -
Controls whether concatenation results are treated as null or empty string values.

Concatenation of string with NULL (Example) :

DECLARE @a VARCHAR(5) = 100
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT @a + NULL; 
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT @a + NULL;

SQL Server - How to find environment difference on SSMS Query window

This article is about finding environment difference on SSMS query window .

Suppose If we have access to all environments (Production,QA and Development), we should be really careful in executing DML scripts on respective environments.

On query window at the bottom, we can find the server name, user name, SPID and database details as shown in below image :

SSMS - How to connect to particular database when connecting to SQL Server database engine

After connecting into SQL Server Database engine and opening Query window , you will notice master database in the Available Databases list by default .

We can choose the required database from the Available Databases list.

To connect to particular database when connecting to SQL Server database engine :

SSRS - SUM based on condition

In this article , let us see an example on how to SUM based on condition in SSRS tablix report .

Consider below query is my report dataset :

DECLARE @tmp TABLE (Code VARCHAR(10),Level1 INT,Level2 INT)
INSERT @tmp SELECT 'CHN',5,10
INSERT @tmp SELECT 'KOL',5,15
INSERT @tmp SELECT 'IND',10,25
INSERT @tmp SELECT 'NY',5,10
INSERT @tmp SELECT 'TX',5,15
INSERT @tmp SELECT 'US',10,25
SELECT * FROM @tmp

Consider, I have tablix report as shown in below image :

In the above shown report, If we are required to SUM values in the Columns Level1 and Level2, only for Code = "IND" and Code = "US", then we need to write expression as shown below :

Won Microsoft TechNet Guru Awards

Won Gold medal - Microsoft TechNet Guru Awards - Under Category -
SQL Server Reporting Services, Power View Technical Guru - July 2013

Gold medal article - SSRS: Multi Font Color / Multi Font Size within a Single Field / Textbox

Announced as Featured article on TechNet Wiki

Awarded as Microsoft Community Contributor

What is a Microsoft Community Contributor?

It is a badge offered by Microsoft which is designed to showcase those who make notable contributions to Microsoft online communities such as TechNet, MSDN and Microsoft Community.

Microsoft Community Contributor (FAQ's) - https://www.microsoftcommunitycontributor.com/faq.aspx

Current recipients list of the Microsoft Community Contributor badge - https://www.microsoftcommunitycontributor.com/current-recipients.aspx

SQL Server Technet Wiki Articles

SQL Server Developer Resources
T-SQL Useful Links
SQL Server Query Language - Transact-SQL
SQL Server Reporting Services - List of TechNet Wiki Articles
SQL Server Integration Services - List of TechNet Wiki Articles
SQL Server Analysis Services - List of TechNet Wiki Articles
TechNet Guru Contributions

SQL Server Performance tuning with Indexing

Refer my post on Technet Wiki - SQL Server Performance tuning with Indexing

SQL Server - ISNUMERIC

ISNUMERIC - Determines whether an expression is a valid numeric type .
Returns 1 if it is a valid numeric type else 0 .

--Valid Scenarios (ISNUMERIC returns 1)

--valid numbers
SELECT ISNUMERIC(12345) 

--valid numbers inside quotes
SELECT ISNUMERIC('12345') 

--valid numbers with comma separated
SELECT ISNUMERIC('1,23,456') 

--decimal type
SELECT ISNUMERIC(12345.) 

--decimal type
SELECT ISNUMERIC(123.45)

--decimal type 
SELECT ISNUMERIC(.12345) 

--decimal type
SELECT ISNUMERIC('.')

SQL Server - Script to find particular column from all tables/views except system databases

Below script can be used to find particular column from all tables/views in all databases except system databases

IF OBJECT_ID('tempdb..##SearchColumn') IS NOT NULL
BEGIN
DROP TABLE ##SearchColumn
END
CREATE TABLE ##SearchColumn (ObjectName NVARCHAR(200))
EXECUTE sp_MSforeachdb '
IF ''?'' NOT IN (''master'',''model'',''Msdb'',''tempdb'') --system databases
BEGIN
    USE [?]
      INSERT ##SearchColumn
      SELECT Object_name(object_id) 
      FROM Sys.Columns 
      WHERE name LIKE ''%BusinessEntityID%'' --Pass ColumnName
END'
SELECT * FROM ##SearchColumn

SQL Server - Display dynamically horizontal rows vertically

Refer my post on Technet gallery - http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-display-602afad6

Refer my post on Technet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/19698.sql-server-display-horizontal-rows-vertically.aspx

SSRS - Multi-valued Parameter as Stored procedure Input

In this post, I have shared about how to handle when SSRS dataset gets delimited string as input from multi valued parameter.

Refer my post on Technet Wiki

Refer my post on Technet Wiki ( for single character multi-valued parameter )

SSIS - Import Excel to table - Cannot convert between unicode and non-unicode string data types

Refer my post on Technet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/19612.ssis-import-excel-to-table-cannot-convert-between-uniocde-and-non-unicode-string-data-types.aspx

SSRS - Compare two versions of the same report

Refer my post on Technet gallery - SSRS - Compare two versions of the same report
http://gallery.technet.microsoft.com/scriptcenter/SSRS-Compare-two-versions-44be98e5

Refer my post on Technet Wiki - SSRS - RDL Compare -
http://social.technet.microsoft.com/wiki/contents/articles/19611.ssrs-rdl-compare.aspx

T-SQL Script to generate Index Creation Scripts for all tables in a database

Refer my post on Technet gallery :

SQL Server - Generate Index Script
http://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Generate-Index-fa790441

Refer my post on Technet Wiki :

T-SQL Script to generate Index Creation Scripts for all tables in a database
http://social.technet.microsoft.com/wiki/contents/articles/19598.t-sql-script-to-generate-index-creation-scripts-for-all-tables-in-a-database.aspx

T-SQL Script to get information about Index settings

Refer my post on Technet gallery :

T-SQL Script to get detailed information about Index settings for all tables
http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-get-03d0fee7

Refer my post on Technet Wiki :

T-SQL Script to get detailed information about Index settings
http://social.technet.microsoft.com/wiki/contents/articles/19593.t-sql-script-to-get-detailed-information-about-index-settings.aspx

SSRS (Matrix) - How to Repeat Headers on Each Page and Keep Headers Fixed while Scrolling

Refer my post on Technet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/19505.ssrs-matrix-how-to-repeat-headers-on-each-page-and-keep-headers-fixed-while-scrolling.aspx

SSRS - No data message for report items

This article is about handling display of report items like chart , table or matrix when there is no data
returned from the dataset .

For example :

Consider below query is my dataset :

DECLARE @SalesbyMonth TABLE (Months VARCHAR(20),Sales INT,MonthNo INT)
INSERT @SalesbyMonth SELECT 'January',1000,1
INSERT @SalesbyMonth SELECT 'February',100,2
INSERT @SalesbyMonth SELECT 'March',100,3
INSERT @SalesbyMonth SELECT 'April',200,4
INSERT @SalesbyMonth SELECT 'May',500,5
INSERT @SalesbyMonth SELECT 'June',800,6
INSERT @SalesbyMonth SELECT 'July',500,7
INSERT @SalesbyMonth SELECT 'August',100,8
INSERT @SalesbyMonth SELECT 'September',900,9
INSERT @SalesbyMonth SELECT 'October',600,110
INSERT @SalesbyMonth SELECT 'November',100,11
INSERT @SalesbyMonth SELECT 'December',100,12
SELECT * FROM @SalesbyMonth
WHERE MonthNo = @MonthNo

I have table & column chart on the report body , when I preview the report and enter MonthNo =12 and click on View report

SSRS - How to display table after clicking on chart

If we have a requirement to display table only after clicking on chart then follow below steps :

Consider below query is my dataset :

DECLARE @SalesbyMonth TABLE (Months VARCHAR(20),Sales INT,MonthNo INT)
INSERT @SalesbyMonth SELECT 'January',1000,1
INSERT @SalesbyMonth SELECT 'February',100,2
INSERT @SalesbyMonth SELECT 'March',100,3
INSERT @SalesbyMonth SELECT 'April',200,4
INSERT @SalesbyMonth SELECT 'May',500,5
INSERT @SalesbyMonth SELECT 'June',800,6
INSERT @SalesbyMonth SELECT 'July',500,7
INSERT @SalesbyMonth SELECT 'August',100,8
INSERT @SalesbyMonth SELECT 'September',900,9
INSERT @SalesbyMonth SELECT 'October',600,110
INSERT @SalesbyMonth SELECT 'November',100,11
INSERT @SalesbyMonth SELECT 'December',100,12
SELECT * FROM @SalesbyMonth

I have Column chart and table on the report body as shown below :

To display all labels on the X-axis (Horizontal axis)

To sort labels on X - axis (Horizontal axis)

Parameter settings is as shown in below images :

SSRS - How to set Column Visibility property for many columns based on Parameter value

Refer my post on Technet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/19333.ssrs-how-to-set-column-visibility-property-for-many-columns-based-on-parameter-value.aspx

SSIS - Extract filename from file path using TOKEN and TOKENCOUNT

TOKEN and TOKENCOUNT are new string functions introduced from SSIS 2012 .

As mentioned in MSDN :

TOKEN - Returns a token (substring) from a string based on the specified delimiters that separate tokens in the string and the number of the token that denotes which token to be returned.

Syntax :

TOKEN(character_expression, delimiter_string, occurrence)

TOKENCOUNT - Returns the number of tokens in a string that contains tokens

separated by the specified delimiters.

Syntax :

TOKENCOUNT(character_expression, delimiter_string)

Example : (To extract filename from file path using TOKEN and TOKENCOUNT )

Below expression returns 3 , because delimiter_string "\\" splits the character_expression
"G:\\GAMES\\fifa.exe" into three parts .

TOKENCOUNT("G:\\GAMES\\fifa.exe", "\\")

Below expression returns fifa.exe , because delimiter_string "\\" splits the character_expression "G:\\GAMES\\fifa.exe" into three parts, Occurrence is specified as 3, so the third token in the string is returned.

TOKEN("G:\\GAMES\\fifa.exe", "\\",3)

To extract filename from file path using TOKEN and TOKENCOUNT

TOKEN("G:\\GAMES\\fifa.exe", "\\", TOKENCOUNT("G:\\GAMES\\fifa.exe", "\\"))

For more info & examples :

Token - http://technet.microsoft.com/en-us/library/hh213216.aspx
TokenCount - http://technet.microsoft.com/en-us/library/hh213135.aspx

How to check the syntax of dynamic SQL before execution

Refer my post on Technet Gallery - http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-check-the-935002e7

T-SQL Script to compare two tables definition / metadata in different databases

Refer my post on Technet Gallery - http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-compare-aaae99a7

SSRS - To group same row data with one column having varying data

Refer my post on Technet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/19044.ssrs-to-group-same-row-data-with-one-column-having-varying-data.aspx

SSRS - How to add variables

To add Variables in SSRS reports :

Goto Report - > Report Properties ... -> Variables

or right-click outside the body of the report - > Report Properties ... -> Variables

We can assign any value or write expression for the value of the variable and can use that
variable throughout the report

SSRS - Example for Conditional formatting

In this post - http://www.allaboutmssql.com/2013/08/ssrs-example-for-tablix-with-sparkline.html
Under section TablewithIndicators , Indicators color are displayed based on Indicator column value .
In similar way we can do conditional formatting on the report .

SSRS - How to add Custom Code and example for using Custom Code

To add Custom code in SSRS reports :

Goto Report - > Report Properties ... -> Code

or right-click outside the body of the report - > Report Properties ... -> Code

Consider this is my Dataset for this example :

SSRS - Example for Tablix with Sparkline / Bar Chart / Indicator

This article is about an example for creating table with
i) Sparkline
ii) Bar Chart
iii) Indicator

Create Data source and then Datasets

Dataset name : TablixWithGraph

Dataset Query :

DECLARE @FY TABLE (Products VARCHAR(20),Q1 INT,Q2 INT,Q3 INT)
INSERT @FY SELECT 'SQLServer2008',1000,200,100
INSERT @FY SELECT 'SQLServer2012',1000,1000,1500
INSERT @FY SELECT 'SQLServer2014',1000,3000,5000

SELECT * FROM (
SELECT Products,Q1,Q2,Q3,Q1 AS Q11,Q2 AS Q22,Q3 AS Q33 FROM @FY) tmp
UNPIVOT
(QData FOR Qs IN (Q11,Q22,Q33)
) AS Unpvt

Dataset name : TablixWithIndicators

Dataset Query :

SSRS - Example for Lookup , LookUpSet and MultiLookup functions

In this article, let us see examples for Lookup, LookUpSet and MultiLookup functions .

As mentioned in MSDN:

Lookup -
Returns the first matching value for the specified name from a dataset that contains name/value pairs.

LookUpSet -
Returns the set of matching values for the specified name from a dataset that contains name/value pairs.

MultiLookup -
Returns the set of first-match values for the specified set of names from a dataset that contains name/value pairs.

Examples:

Create Data source and then Datasets

Dataset name : LookUp_Parameter

Dataset Query :

DECLARE  @Country_Master TABLE(Id INT,Country_Code VARCHAR(10),Country_Name VARCHAR(10))
INSERT INTO @Country_Master SELECT 1,'IND','INDIA'
INSERT INTO @Country_Master SELECT 2,'US','USA'
INSERT INTO @Country_Master SELECT 3,'CN','CHINA'
SELECT * FROM @Country_Master

How to add Pivot Table ?

Overview on Extended Events :

See Also:

See Also:

Example :

See Also:

SQL Server - Different ways to shred XML into table :

See Also:

See Also:

Reference links :

See Also:

i . ) Divide by zero :

See Also:

See Also:

See Also:

See Also:

See Also:

See Also:

Refer my post on Technet gallery :

Refer my post on Technet Wiki :

See Also:

Refer my post on Technet gallery :

Refer my post on Technet Wiki :

See Also:

See Also:

About Me

Followers