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 <!-- and --> 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 ?



What is Dimensional Fact Model ?



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 .




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

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 .

See Also:




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 .

See Also:




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 :




See Also:


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

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



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


See Also:

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 :

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

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

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