Tag Archives: SQL Server 2012

SQL Server – Integration Service – How To Extract Images From a SQL Server Table To a Folder Using SSIS

In this article we will go through how we can extract images from a table in SQL Server and copy it on a folder. To achieve this we need to use SQL Server Integration Service. Let’s go over the process step by step.

Step 1: Create a new SSIS project in BIDS
IE1

Step 2: Drag and drop the Data Flow Task from the SSIS Toolbox to the design surface

IE2

Step 3: Go to the Data Flow tab. Drag and Drop the following on the design surface.

ie3

Step 4: Right click on the connection manager area and create a new OLEDB connection. We will create a connection for the AdventureWorks2012 database and the Production.Photo table.

 

IE4 IE5

 

Step 5. Use the following command to select the data from the table

declare @Ipath varchar(100)= 'C:\document\'
SELECT [ThumbNailPhoto],
@Ipath+[ThumbnailPhotoFileName] AS Imagepath
FROM [AdventureWorks2012].[Production].[ProductPhoto]

 

IE6

 

Step 6. Double click on the export column transformation editor and select the following values

IE7

 

Step 7. Build the package and execute. You should find all the images in the selected folder

IE9

 

Advertisements

SQL Server 2012 – How to change the collation of SQL Server.

In this article we will see how we can change the collation of a SQL Server post installation. To achieve this we need to rebuild the master database. While rebuilding the master database, the process gives an option to change the collation. Let us understand the process step by step and change the collation of an already installed SQL Server.

Step 1: Take a backup of all your system databases and user databases.

Step 2: Script out all Logins.

In this example we will change the collation fromSQL_Latin1_General_CP1_CI_AI’ to ‘SQL_Latin1_General_CP1_CI_AS’
collate1

 

Step 3: Stop the SQL Server Service.

collate3

 

Step 4: Via Command prompt locate to the Binn directory of the SQL Server

collate2

Step 5: Run the below command

sqlservr -m -T4022 -T3659  -s”INST2012_1″  -q”SQL_Latin1_General_CP1_CI_AS”

Parameters:
[-m] Single User Mode
[-T] Trace flag turned on at startup
[-s] SQL Server Instance Name
[-q] New Collation

collate5

Step 6: Restart SQL Server and check the collation

collate6

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

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.

 

CHOOSE – SQL Server 2012

In this article we will understand this new logical function named CHOOSE in SQL Server 2012.

So here is how the syntax of CHOOSE looks like

 CHOOSE ( index, val_1, val_2 [, val_n ] )

Now let us use this in the below code and understand the output.

declare @MyFavouriteClub as int = 5
SELECT choose(@MyFavouriteClub, 'Arsenal', 'Chelsea',
'Liverpool', 'Everton', 'Manchester United',
'Sunderland', 'Fulham')

Now lets see the output of the above
choose1So what the function does is pulls out the 5th value from the data set and dispalys as a result set. CHOOSE works like an index into an array, where the array contains values or arguments. The index value\argument determines which of the containing values will be returned. If no match is found then a NULL is returned.

PERCENT_RANK function in SQL Server 2012

PERCENT_RANK is a new function introduced in SQL Server 2012. The function calculates the relative rank of a row within a subset of rows or in simpler terms  it shows the percentage of values that are less than or equal to the current value.

The syntax for the function is as follows

 PERCENT_RANK( )
 OVER ( [ partition_by_clause ] order_by_clause )

Let us quickly implement PERCENT_RANK and understand how it works.

Use the following code to create a database with values in it.

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

Now lets use the PERCENT_RANK function on the above database created and view the outcome. We will use the following code below.

SELECT ProductId,SalesQty,Year,
PERCENT_RANK() OVER ( ORDER BY [SalesQty] )
AS PercentRank
FROM PERCENTRANKDEMO
WHERE productid = 30;

The outcome would be as below. The column PercentRank shows the percent of values that are less or equal to the current value of the SalesQty column. The first row in any set has a PERCENT_RANK of 0. That’s how it has been designed by default. In the result set 0.1 denotes 10 percent while 1 denotes 100 percent.
PR1

LAG and LEAD functions in SQL Server 2012

LAG() and LEAD() are two of the new analytical functions that has been introduced in SQL Server 2012. These functions helps in accessing a value from a  previous row (lag) and subsequent row (lead) in the same result set without joining the result set to itself . Let us quickly understand this with an example.

Let us create a database for the same and insert some values. Use the following code to achieve the same on a SQL Server 2012 instance

USE [Master]
GO
IF EXISTS(SELECT name FROM sys.databases
WHERE name = 'LagAndLeadDemo')
BEGIN
 DROP DATABASE LagAndLeadDemo;
END
GO
CREATE DATABASE LagAndLeadDemo;
GO
USE LagAndLeadDemo
GO
CREATE TABLE LagLead
(
[ProductId] int,
[SalesQty] int,
[Year] int
);
insert into LagLead
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,3093,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);

Now let us implement LAG with following query

select [ProductId], [SalesQty], Year,
LAG([SalesQty]) OVER
(ORDER BY Year) as SalesQtyLastYear
from LagLead
where [ProductId] = 10
order by Year;

ll2Now if we observe the data of the column ‘SalesQtylastYear’ then we will find that it reflects the value that belongs to the previous row of the SalesQty column.

Now let us implement Lead with the following query

select [ProductId], [SalesQty],
Year,
LAG([SalesQty]) OVER
(ORDER BY Year) as SalesQtyLastYear,
Lead([SalesQty]) OVER
(ORDER BY Year) as SalesQtyNextYear
from LagLead
where [ProductId] = 10
order by Year;

ll3What we observe now is that the data of the column ‘SalesQtylastYear’ reflects the next value that belongs to the SalesQty column.

So in a nutshell this is what LAG and LEAD does. Fetches the previous row and next row values respectively. Let us now calculate the difference between last years sales quantity and current year’s sales quantity

select [ProductId], [SalesQty], Year,
LAG([SalesQty]) OVER (ORDER BY Year)
as LastYearRevenue,
[SalesQty] - LAG([SalesQty]) OVER (ORDER BY Year)
as SalesQtyChange
from LagLead
where [ProductId] = 20
order by Year;

ll4

%d bloggers like this: