A recent Windows 2012 OS roll up patch causes the execution of any SSIS package to fail under the following conditions:
1. The package is using Microsoft.Jet.OLEDB.4.0
2. The Package is executed from a SQL job.
3. The OS is Windows 2012 on which the SQL is installed.
Snapshot of the error message:
MS has acknowledged the issue. Below link with details and workaround.
https://support.microsoft.com/en-us/help/4041692/windows-server-2012-update-kb4041692.
One of the challenges for DBAs and Developers was to compare execution plans effectively. In the latest SSMS released by MS we have an option of comparing execution plans in a better readable format. This short article will explain that.
Step 1: Download the latest SSMS from MS website or If you have installed SQL server 2016 then this feature will be available.
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
Step 2: Install the downloaded package
Step 3: Execute a query and save the execution plan somewhere.
Step 4: Execute another query and wait till the plan is displayed.
Step 5: Right click on the displayed plan and choose the following option highlighted.
This will ask for the saved execution plan. Choose the saved plan.
Step 6: Click on open
The actions opens 2 windows which shows the difference between the 2 execution plans as depicted below. This is a very handy feature when we need to compare the execution plans for a SP or query pre and post change to gauge the effect in terms of performance.
I hope this article was useful. Have a good day ahead.
Bodhisatya
Recently MS released the following patches for SQL Server. The product team has identified that the patches can be potential CDC functionality breaker. The link below for the note from the product team. Worth a read before applying them on production boxes specially if CDC is active.
I hope this article was useful. Have a good day ahead.
Bodhisatya
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
Step 2: Drag and drop the Data Flow Task from the SSIS Toolbox to the design surface
Step 3: Go to the Data Flow tab. Drag and Drop the following on the design surface.
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.
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]
Step 6. Double click on the export column transformation editor and select the following values
Step 7. Build the package and execute. You should find all the images in the selected folder
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 from ‘SQL_Latin1_General_CP1_CI_AI’ to ‘SQL_Latin1_General_CP1_CI_AS’
Step 3: Stop the SQL Server Service.
Step 4: Via Command prompt locate to the Binn directory of the SQL Server
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
Step 6: Restart SQL Server and check the collation
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)
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
Now lets see what happens when we pass a Null value to the Fraction parameter
If we fail to pass any one of the parameters then the following error message is returned.
Let us understand this with an example.
The SQL code for returning day, month and year parameters as a date would probably look something like this
DECLARE @Day INT = 07, @Month int = 03,@Year INT = 2014 SELECT CONVERT(datetime,CONVERT(varchar(10),@Year) + '-' + CONVERT(varchar(10),@Month) + '-' + CONVERT(varchar(10),@Day),103) AS TheDate
Now lets implement DATEFROMPARTSHere is the code for the same
DECLARE @Day INT = 07, @Month int = 03, @Year INT = 2014 SELECT DATEFROMPARTS (@Year, @Month, @Day) AS TheDate
In my earlier post on Extended events we discussed how we can use the SSMS GUI in SQL Server 2012 to create new extended events. In this article we will take a deeper dive into the SQL Server 2012 SSMS GUI and explore other extended events features.
Step 1 -> Right click on the session we created. Click on properties
Step 2 -> Click on Events -> Click on Configure
Step 3 -> When we click on configure, the below window will appear
Step 4 -> Now click on the first event ‘query_post_execution_showplan‘ and we will find a list of Actions on the right hand pane being displayed. These are the bunch of global fields from which we can choose and add to the events and the relevant data will be captured by the event trace. For this event we will choose ‘cpu_id’ and ‘database_name’.
Repeat the same for the next event
Step 5 -> Now Click on the ‘Events Fields‘ tab and select the below as depicted. Click OK.
Now lets start the session and examine the Live data. For generating activity on the SQL Server we will run the below queries
USE AdventureWorks2012 go SELECT * FROM Production.Product pp WHERE pp.ProductID > 40 go SELECT * FROM Production.BillOfMaterials Pb WHERE pb.BillOfMaterialsID > 100 go
Now lets watch the Live data
In this section we will add filters to the session we created. Filters allow you to fetch events based on the criteria provided.
Step 1 -> Right click on the session -> Click on Properties -> Click on Events -> Choose an event -> Click on the ‘Filter(Predicate)’ tab
Step 2 -> From the Field tab choose a filter from the drop down. In our case we will choose duration and set a value in the Value field. Click on OK.
Step 3 -> Start the session and fire some queries and observe the Live data in action.
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')
The 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.
Resolution:
I had to change the compatibility level to 110 and then ran the code with success.
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 ] )
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
SELECT TRY_CONVERT(xml, 40)
And the output is
SELECT TRY_CONVERT(DATETIME, '22/18/2014 05:30',111)
And the output is
The first error is self explanatory. The second code gives an output of NULL because the date is an invalid date.
Lets understand with this example
SELECT CONVERT(DATETIME, 'ABC')
CONVERT will give an error message stating the conversion failed.
SELECT TRY_CONVERT(DATETIME, 'ABC') AS 'Result'
TRY_CONVERT will give a NULL.
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
So 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.
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.
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
So 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.
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 )
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.
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);
select [ProductId], [SalesQty], Year, LAG([SalesQty]) OVER (ORDER BY Year) as SalesQtyLastYear from LagLead where [ProductId] = 10 order by Year;
Now 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.
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;
What 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;
In SQL Server 2012 you can now create an user-defined server role and configure server level permissions for it. In previous versions this was not possible. If we had to delegate someone with administrative tasks we had no choice but to assign more rights and access than required. With SQL Server 2012, user-defined server roles can be created and configured with specific permissions for specific set of DBA’s.
Let us understand with an example how we can create an user defined server role.
Step 1: Right click on Server roles and select ‘New Server Role‘
Step 2-> As the dialog box opens, type in a server role name -> set the owner to a preferred login. In our case we would choose sa.
Step 3 -> Choose an option\s from Securables window. In our case we chose Servers. Under servers you will find the name of the server. Select the option. Below in the permissions window select the following as shown in the snapshot.
Step 4 -> Click on OK. You will find the new server role under the server roles in SSMS
Step 5 -> Now let us add a login to this new role. Right click on the ServerRole1 -> Click on Properties -> On the members tab click on Add
Step 6 -> Add a login that you want a to give membership to this role. Click on OK.
So now you have successfully given a particular login few administrative rights that is required rather than granting it a privilege like sysadmin.However, one limitation of the user-defined server roles is that they cannot be granted permission on database level securables. Below is the script for the entire action we did.
USE [master] GO CREATE SERVER ROLE [ServerRole1] AUTHORIZATION [sa] GO use [master] GO GRANT ALTER SERVER STATE TO [ServerRole1] GO use [master] GO GRANT ALTER TRACE TO [ServerRole1] GO use [master] GO GRANT CONNECT SQL TO [ServerRole1] GO ALTER SERVER ROLE [ServerRole1] ADD MEMBER [testdb] GO
Applied SQL... 