Tag Archives: Microsoft SQL Server

How To Flatten Xml Data in SQL Server

In this article we will see how we can flatten a xml data and make it more readable. Let us understand the scenario first and then dive into the solution. To understand the scenario we will create a xml file. Load the data in the file into a table in SQL Server.

Step 1:  Create a xml file and store it in the C drive. Below is the content of the file

<?xml version="1.0" ?>
<Root>
<Person>
<Name>John</Name>
<Age>30</Age>
<Hobbies>
<Hobby>PhotoGraphy</Hobby>
<Hobby>Driving</Hobby>
</Hobbies>
</Person>
<Person>
<Name>Wayne</Name>
<Age>21</Age>
<Hobbies>
<Hobby>Fishing</Hobby>
<Hobby>Cooking</Hobby>
</Hobbies>
</Person>
</Root>

Step 2 : Save the file as XmlSource.xml on the C drive. You can choose a different location also.

Step 3: Run the following script and create a table which will store the xml data.

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'XmlTable')
DROP TABLE XmlTable
go
CREATE TABLE XmlTable
(
XmLId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY ,
XmlData XML NOT NULL
)

Step 4: Run the below script to insert the contents of the xml file into the table

INSERT INTO XmlTable(XmlData)
SELECT * FROM OPENROWSET(BULK 'C:\XmlSource.xml', SINGLE_BLOB)
AS ImportSource
GO

Step 5: Check if the data has been inserted correctly or not.

xml1

 

If we observe we will see that the contents of the xmlData row are in xml format. So how do we make it something like below
xml2

Here you go. The below query will make it happen

SELECT pref.value('(Name/text())[1]', 'varchar(50)') AS Name ,
pref.value('(Age/text())[1]', 'varchar(50)') AS Age,
pref.value('(Hobbies/Hobby/text())[1]', 'varchar(50)') AS Hobby1,
pref.value('(Hobbies/Hobby/text())[2]', 'varchar(50)') AS Hobby2 
FROM
XmlTable
CROSS APPLY
XmlData.nodes('//Person') AS People ( pref )

The quick way to extract the xml data is to use the CROSS APPLY operator which uses a function
against each row and then adds the data to the result set. We then combine this with a function
that can be executed on the XML data type called NODES

 

 


			

How to Rebuild the MSDB database

I was preparing a migration plan to migrate all SQL Server databases from our existing SAN to new SAN. While doing so I thought of actually doing a small practice on my laptop SQL Server. The user databases moved from the existing location to another location very smoothly. I was very happy. Now was the turn of the system databases. I started with the MSDB database. As recommended in the Microsoft technet article of relocating System DB’s, I was following the steps prescribed. Pretty simple steps. After making the file location change in the master database, I stopped the SQL service, physically moved the files to the new location and then restarted the SQL service. The MSDB database would not come up….In recovery…..

What do I do?…I didn’t even have a backup of the MSDB database. I read few tech net articles that helped me rebuild the MSDB database from scratch. Here is what needs to be done

1) Stop all SQL server services.
msdb12) Start SQL Server from the command prompt using the trace flag T3608. The exact command for this would be : NET START [Name of the SQL service]/T3608. This is what the trace flag does as per Technet.

Prevents SQL Server from automatically starting and recovering any database except the master database. If activities that require tempdb are initiated, then model is recovered and tempdb is created. Other databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work.

msdb23) Now we need to detach the MSDB database from the instance. This is what needs to be done for the same. We need to run a SQLCMD command to do the same.
SQLCMD -E -S<servername> -dmaster -Q”EXEC sp_detach_db msdb”
msdb4After running this rename the existing MSDB files. Stop and restart SQL Service via the configuration manager.
msdb54) We need to locate the following .SQL file in the install folder of the SQL Server installation files.
msdb65) Open the script in SSMS and execute.

SQL Server agent was running successfully. The MSDB database was online again. The first thing I did was take a back up now.

 

 

 

SQL Server Integration Services – Foreach Loop Container

In my previous article I wrote about the For loop Container. In this article I will show how we can implement the Foreach loop container with a demo. The Foreach loop container is the third container amongst the three containers in SSIS.

The Foreach loop container can be used to loop through a set of enumerated objects like files in a folder or rows in a table. We can add different control flow tasks within the container for each enumerator or a DataFlow task to process data related to each item. Let us now implement this container with a small demo.

Implementing Foreach Loop Container

Objective: To copy files from one folder to another folder.

Step 1: We will be using the package we created in my article. Drag and drop a Foreach loop container in the design UI surface -> Drag and drop a File system task inside the container.
IS48Step 2 : Create a variable as shown below with the depicted data type and value
IS49Step 3: create 2 folders named ‘Source’ and ‘destination’ respectively. In the source folder create two files named file1.txt and file2.txt
IS50 IS51Step 4: Right click -> Choose Edit on the Foreach loop container and fill up the following as depicted below. Click on OK.
IS52 IS53Step 5: Right Click on the File System task -> On DestinationConnection -> Choose the usage type as ‘Existing Folder’ -> Choose the path of the destination folder -> click on ok.
IS54Step 6: Set the rest of the fields as below -> Click on OK.
IS55Now we are ready to test out solution. Press F5 and the 2 files should be in the destination folder.
IS56 IS57Bingo!!!! The files has been copied to the destination folder. I hope this article was useful in understanding the Foreach loop and its understanding.

SQL Server Integration Services – Control Flow Objects

In my previous article on SSIS we spoke about Packages,Connections and Data Sources. In this article we will talk about Control Flow Objects and how we can use them.

So what is Control Flow?

Control Flow is the engine which manages the workflow of the tasks created coupled with the control flow containers and constraints. SSIS provides a Design UI which displays a workspace where we can configure different control flow objects.

SSIS provides three types of control flow objects
1) Tasks – Tasks are objects that perform specific work
IS16
2) Containers – Containers help group different tasks
IS17
3) Constraints – Constraints help connecting different tasks and containers. Its also helps to define the order of execution of different tasks.
IS18

A package must contain at least one task that performs a certain operation. When multiple tasks are configured, then a container can be used to group them together.
IS19

 

Container

SSIS provides three types of Containers. Here is a brief description about the three Containers. We will deep dive into the Containers later.

1) Sequence Container : This allows us to group multiple tasks together. The benefits of this feature is that we can control the entire group rather that managing each task separately. It also means we can run different tasks sequentially or parallel. The entire group can collapsed or expanded. The Sequence container is the 3rd container in the toolbox
IS202) For Loop Container : This provides the same functionality as the sequence container but also lets you run tasks multiple times based on the condition provided. The For Loop is the 1st tool in the tool box
IS213) Foreach Loop Container : This allows looping but instead of a condition the looping happens over objects such as files and folders or tables in a database.
IS22

Implementation of Control Flow Task

We will now implement the above by creating and editing a control flow task and executing the package within the SSIS environment. We will be using the same project we created in our previous article for this demo.

Step 1: Open the Project using BIDS -> Select Mypackage.dtsx -> Click on the Toolbox -> Drag and Drop the Execute SQL Task on the Designer Surface. Rename the task as Update Table
IS23 IS24
Step 2: Right Click on the Task – > Click on Edit -> Change the Connection to AdventureWorks -> Click on the SQLStatement and insert the below query – > Click on OK.
IS25 IS26 IS27Query:

UPDATE Production.ScrapReason SET
ModifiedDate = '2014-06-01 00:00:00.000'
WHERE name = 'Drill pattern incorrect'

Step 3: click Start Debugging on the Standard toolbar -> If the execution is correct then the task would be green.
IS28 IS29Step 4: Click on the Progress tab to view the execution details of the task. -> Stop the execution by clciking on the stop debugging button – > Save the project.
IS30 IS31

The objective of this article was an introduction to the concept of Control Flow and Tasks with a small demo. I hope I was able to do that. In my next article I will write about Container with a broader perspective.

 

 

Using TIMEFROMPARTS() – SQL Server 2012

This new function in SQL server 2012 helps in converting time parts to time. The syntax of this function is as follows

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

The range of the parameters for TIMEFROMPARTS is as follows :
Hour -> 0-23.
Minutes -> 0-59.
Seconds -> 0-59.
Fractions -> 0-9999999.
Precision -> 0-7.

Let us understand this with an example.

DECLARE @hour INT, @min INT,@Sec INT,@frac INT;
SET @hour = 13
SET @min = 24
SET @Sec = 22
SET @frac = 45
SELECT TIMEFROMPARTS(@hour,@min,@Sec,@frac,2)

time1

This function requires a valid value for the Hour,Minute, Seconds, Fractions, Precision parameters. If any invalid value is passed then this function will return an error. If a Null value is passed on for the Precision parameter then it generates an error. For other parameters if a Null is passed then the output is also Null.

For example in the below code we pass an invalid value for the Hour parameter

time2

Now lets see what happens when we pass a Null value to the Fraction parameter

time3
 
 
 
 
 
 
   
   
   
      
If we fail to pass any one of the parameters then the following error message is returned.
time4

How to troubleshoot error – Msg 195, Level 15, State 10, Line 1 ‘TRY_CONVERT’ is not a recognized built-in function name

While running the following code on a database on my SQL Server 2012 instance I got the following error message

USE BB
go
SELECT TRY_CONVERT(xml, 'MyClub')

TCerr1The reason for this error is that this function TRY_CONVERT is 110 compatible. Which means it will only run on a database with 110 compatibility. Upon checking the compatibility level, I found the database to be on 90 or SQL Server 2005 compatible.
TCerr2Resolution:
I had to change the compatibility level to 110 and then ran the code with success.

TCerr3

TRY_CONVERT function in SQL Server 2012

TRY_CONVERT is one of the new conversion function introduced in SQL SERVER 2012. It returns a value converted to the specified data type if the conversion succeeds. Otherwise,it returns returns NULL value when it fails to convert to a requested data type. TRY_CONVERT function raises an exception if we try to an convert expression to a type which is not explicitly permitted

The syntax for TRY_CONVERT is as follows

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Let us now implement this function and see how it works with different examples

SELECT TRY_CONVERT(xml, 'Manchester United')
SELECT TRY_CONVERT(DATETIME, '02/18/2014 05:30',111)
SELECT TRY_CONVERT(INT, '40')

The output of the above as follows
TryCon1

Now let us see examples where this function might fail or throw an exception

SELECT TRY_CONVERT(xml, 40)

And the output is
TryCon2

SELECT TRY_CONVERT(DATETIME, '22/18/2014 05:30',111)

And the output is
TryCon3The first error is self explanatory. The second code gives an output of NULL because the date is an invalid date.

Now the question is how is TRY_CONVERT different from CONVERT function?

Lets understand with this example

SELECT CONVERT(DATETIME, 'ABC')

TryCon4CONVERT will give an error message stating the conversion failed.

SELECT TRY_CONVERT(DATETIME, 'ABC')
AS 'Result'

TryCon5TRY_CONVERT will give a NULL.

 

PERCENTILE_DISC function in SQL Server 2012

PERCENTILE_DISC is one the new analytical\statistical functions that we have in SQL Server 2012. In this article we will see how we can use this function. To begin with let us see the syntax of this function as stated in BOL.

Syntax of PERCENT_DISC

PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP
( ORDER BY order_by_expression [ ASC | DESC ] )
OVER ( [ <partition_by_clause> ] )

Now let us implement PERCENT_DISC and understand how it works.We will be using the below script to create a database and insert some values in it before we call this function.

USE [Master]
GO
IF EXISTS(SELECT name FROM sys.databases
WHERE name = 'PERCENTRANK')
BEGIN
DROP DATABASE PERCENTRANK;
END
GO
CREATE DATABASE PERCENTRANK;
GO
USE PERCENTRANK
GO
CREATE TABLE PERCENTRANKDEMO
(
[ProductId] int,
[SalesQty] int,
[Year] int
);
insert into PERCENTRANKDEMO
VALUES
(10,1000,2005),(20,1215,2005),(30,3327,2005),
(10,2000,2006),(20,2415,2006),(30,3429,2006),
(10,3050,2007),(20,3216,2007),(30,3737,2007),
(10,3026,2008),(20,1618,2008),(30,3452,2008),
(10,2004,2009),(20,4315,2009),(30,5435,2009),
(10,3040,2010),(20,4015,2010),(30,4343,2010),
(10,3060,2011),(20,4345,2011),(30,3237,2011),
(10,3075,2012),(20,4035,2012),(30,7450,2012),
(10,3055,2013),(20,5012,2013),(30,3321,2013),
(10,3059,2014),(20,6019,2014),(30,3245,2014),
(10,3150,2015),(20,6815,2015),(30,4000,2015);

Noe let’s use the below code to implement this function. With this code we are trying to find the 50th percentile from table we created.

USE PERCENTRANK
GO
select ProductId, SalesQty, Year,
PERCENTILE_DISC(.5)
WITHIN GROUP(ORDER BY SalesQty)
OVER(PARTITION BY ProductID) as [50Percentile]
from PERCENTRANKDEMO
WHERE ProductId = 20
order by ProductId;

Snapshot of the output of the above code
Perc1So what is the inference that we draw from the output. The function PERCENTILE_DISC calculates the Nth percentile based on a discrete distribution of the column values. The result set would have the Nth percentile value which would equal to a specific value in the column. Any NULLS would be ignored.

FIRST_VALUE – New analytical function in SQL Server 2012

In this article we will be taking a look at the new analytical function FIRST_VALUE  in SQL Server 2012. Let us understand them with examples.

This function returns the first value from an ordered set of values. The syntax for the same is

FIRST_VALUE ( [scalar_expression ] )
OVER ( [ partition_by_clause ]
order_by_clause [ rows_range_clause ] )

Let us create a sample table and understand this on SQL Server 2012. The following script does the same. Lets observe the output of the script below

CREATE TABLE [dbo].[MyFirstLastVaL](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NOT NULL,
[MarksInMaths] [int] NOT NULL )
GO
insert into MyFirstLastVaL values('Alan',99)
insert into MyFirstLastVaL values('Brent',29)
insert into MyFirstLastVaL values('Charlie',25)
insert into MyFirstLastVaL values('David',37)
insert into MyFirstLastVaL values('Ello',15)
insert into MyFirstLastVaL values('Frank',59)
insert into MyFirstLastVaL values('Grant',16)
insert into MyFirstLastVaL values('Honey',18)
insert into MyFirstLastVaL values('Irene',86)
insert into MyFirstLastVaL values('Jack',76)
insert into MyFirstLastVaL values('Kalou',66)
insert into MyFirstLastVaL values('Lisa',68)
insert into MyFirstLastVaL values('Mike',98)
insert into MyFirstLastVaL values('Lisa',28)
insert into MyFirstLastVaL values('Mike',45)
insert into MyFirstLastVaL values('Alan',19)
insert into MyFirstLastVaL values('Brent',92)
insert into MyFirstLastVaL values('Charlie',35)
insert into MyFirstLastVaL values('Irene',36)
insert into MyFirstLastVaL values('Jack',67)
insert into MyFirstLastVaL values('Kalou',96)

SELECT id,name,MarksInMaths,
FIRST_VALUE(marksinmaths) OVER (ORDER BY marksinmaths) MyFirstVal
FROM MyFirstLastVaL

fvl1Now let us use the function with partition by clause and observe the output

SELECT id,name,MarksInMaths,FIRST_VALUE(MarksInMaths)
OVER (PARTITION BY Name ORDER BY MarksInMaths) MyFirstVal
FROM MyFirstLastVaL

fvl2In the first case the query simply returns the first value of the entire data set whereas in the second case the output is based on the marksinmaths column, so the FIRST_VALUE is different but the same for each partition.

EOMONTH() – End of the month – SQL Server 2012

So how do we calculate the last date of the month which is 4 months from now?

SQL Server 2012 presents EOMONTH() function. Let us understand this function with an example.

DECLARE @MyDate datetime
SET @MyDate = GETDATE()
SELECT EOMONTH (@MyDate,4)
SELECT EOMONTH (@MyDate,-4)
as LastDayOfTheMonth

eom2
So what do we find from the 2 outputs. Well it gives me the last date of the 4th month from the current month i.e. 4 months from now would be April and the last date of April is 30th. The second output gives the last date of 4 months previous i.e August. This function would be very handy in calculations where the last date of the month is a critical factor. Previously to achieve this we would have to do a bit of manipulation.

Pagination – Using OFFSET and FETCH in SQL Server 2012

OFFSET and FETCH are two new clauses introduced in SQL Server 2012 that allows us to extract a portion of rows from the result set. When we need to display a large result set to the user, the best way of going about it is to split them .i.e use pagination. In SQL Server 2012, we can achieve pagination by using the ‘OFFSET’ and ‘FETCH’ commands. Let us understand this with an example:

USE AdventureWorks2012
GO
SELECT pp.ProductId,PP.Name,pp.ProductNumber,
pp.DaysToManufacture,pp.ListPrice
FROM Production.Product PP
ORDER BY pp.productid

OandF1
The query returns 504 rows as depicted above. So what do i do if i need to fetch only a portion of the above rows?

Enter OFFSET and FETCH. How? let us understand this with an example

USE AdventureWorks2012
GO
SELECT PP.ProductID,PP.Name,pp.ProductNumber,
pp.DaysToManufacture,pp.ListPrice
FROM Production.Product PP
ORDER BY pp.ProductID
OFFSET 100 ROWS
FETCH NEXT 10 ROWS ONLY

OandF2Here, the OFFSET tells the query to ignore the first 100 rows and then return only the following 10 rows. This is very easy to use and a quick way to return just a portion of records.
Few Limitations of the OFFSET and FETCH clause as stated in msdn:
a) ORDER BY is mandatory to use OFFSET and FETCH clause.
b) OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
c) TOP cannot be combined with OFFSET and FETCH in the same query expression.
d) The OFFSET/FETCH row count expression can be any arithmetic, constant, or parameter expression that will return an integer value. The row count expression does not support scalar sub-queries.

TRY_PARSE – Conversion function in SQL Server 2012

In this article I will discuss about the TRY_PARSE function in SQL Server 2012. Conversion functions helps us avoid errors when dealing with different data types. Let us understand TRY_PARSE with an example.

SELECT TRY_PARSE ('12-18-2013' AS datetime) AS Alpha
SELECT TRY_PARSE ('2013' AS decimal) AS Beta
SELECT TRY_PARSE ('2013.00' AS decimal) AS Gamma
SELECT TRY_PARSE ('2013.0000' AS float) AS Theta
SELECT TRY_PARSE ('Arsenal' AS float) AS Delta

The TRY_PARSE() function can convert any string value to a Numeric or Date/Time format. If the passed string value cannot be converted to Numeric or Date/Time format, it will result to a NULL.

Lets have a look at the output of the above
TP2In the above case it would not convert ‘Arsenal’ to a date time or Numeric value and hence it results in NULL output. TRY_PARSE function is not a native SQL SERVER function, instead it is a CLR dependent function.

IIF – The InLine conditional Statement in SQL Server 2012

IIF is a new inline conditional statement in SQL Server. We can pass an expression that can be evaluated to either true or false to the function and it returns one value for true and another one for false.

Let us understand this with an example:

DECLARE @MyTeam nvarchar(40) = 'Manchester United'
DECLARE @YourTeam nvarchar(40) = 'Arsenal'
SELECT IIF (@MyTeam = @YourTeam, 'True Devils',
'False Gooners') AS YouAre

The output of the above as below. In this we have passed two different values.
IIF1Now let us pass two same values and see how this works:

DECLARE @MyTeam nvarchar(40) = 'Manchester United'
DECLARE @YourTeam nvarchar(40) = 'Manchester United'
SELECT IIF (@MyTeam = @YourTeam, 'True Devils',
'False Gooners') AS YouAre

IIF2
IIF is very useful where a straightforward comparison has to be made without writing case statements.

How to use ‘WITH RESULT SETS’ in SQL Server 2012

In SQL Server 2012, the execute statement has been enhanced with an option called ‘WITH RESULT SETS‘. So let us understand what this does.

This option enables us to change the column names and data types of the returning result set from the stored procedure. Let us understand this with an example.

Create a new stored procedure

USE AdventureWorks2012
go
CREATE PROC Usp_products
AS
SELECT ProductID,Name,ProductNumber FROM Production.Product
ORDER BY ProductID

Let us now execute the above procedure and see the outcome
withresultset1Now let us use this new option and change the name of the columns and their data types while the procedure returns the output at run time.

EXEC Usp_products
WITH RESULT SETS
(
(
SerialNumber varchar(15),
Name1 varchar(30),
ProductNumber1 varchar(30)
)
)

withresultset2So this is how we can use this option to tweak the output set to different data types and column names from an stored procedure.

Resources in a SQL Server Failover Cluster

In this article I will discuss about the resources that are part of a SQL Server failover cluster.

A SQL Server failover cluster instance consists of 2 kinds of resources.

1)Local Resources: Local resources are those components that are installed on each possible owner node. They remain on that node and do not move over during a failover.
2)The shared resources: Those resources that fail over between the owner nodes.

Let us now go through the resources that are part of a resource group in a SQL Server failover cluster.
a) SQL Server Database Engine\Agent service : The database engine and SQL Server agent resources are always part of any SQL Server failover cluster’s resource group where the database engine has been installed. If SQL server analysis service is installed, then it will be a part of the resource group. From an availability and performance perspective it is better if both are installed on separate resource groups.

b) IP Address and Network name: The IP address and network name component ensures an interface that clients can use to connect to the SQL Server instance  irrespective of the fact that which node is currently serving the cluster. During a failover event,  the network name and IP addresses is registered to redirect to the new node that is serving the SQL Server failover cluster instance. To the clients that are connecting to the SQL Server, this is a absolutely transparent process.

c) Shared disks:A SQL Server shared disk contains all of the system and user data including databases, logs for the SQL Server failover cluster instance. When a failover occurs, the disks are mounted on the new node. When SQL Server instance is started on the new node, it goes through recovery as part of the SQL Server startup and maintains access to the same database files that existed when it was running on the previous node.The shared disks are the single point of failure for a failover cluster instance. Windows Server and SQL Server failover clustering provides redundancy for machines, operating systems, and SQL Server binaries, but requires reliable storage to assure availability of the shared storage.

%d bloggers like this: