Wednesday, April 10, 2019

T-SQL Tips: Generating Nested XML Structures Efficiently Using FLOWR Expressions


I have been thinking about publishing an article on this for quite a while now. Of late have been too busy (or may be lazy is a better word!) to write on this. Finally thought of breaking the 
Previously I had blogged about how FOR XML PATH can be used to generate nested XML structures in the below two articles

There is an alternate method that can be used to generate these nested XML structures using FLOWR expressions. This will be much more efficient than using nested subqueries for large data volumes.


Using the same example as in the previous article, we can see how FLOWR expression can be applied to get same result

CREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[CustName] [varchar](100) NULL,
[OrderDate] [date] NULL,
[ReferredBy] [varchar](100) NULL,
[AgentGrp] [varchar](30) NULL,
[OrderID] ASC
INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (1, N'ABC Corp', CAST(0xB8350B00 AS Date), N'Agent 1', N'110')
INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (12353, N'R & K Associates', CAST(0x7A370B00 AS Date), N'Agent 5', N'105')
INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (13345, N'Zyng Enterprises', CAST(0x5D370B00 AS Date), N'Agent 3', N'110')
INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (15789, N'Maxim Bailey', CAST(0x7A370B00 AS Date), N'Agent 1', N'120')
INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (22345, N'Kyzer', CAST(0xA5370B00 AS Date), N'Agent 2', N'120')
INSERT [dbo].[Orders] ([OrderID], [CustName], [OrderDate], [ReferredBy], [AgentGrp]) VALUES (29398, N'ABC Corp', CAST(0x54370B00 AS Date), N'Agent 4', N'105')

The result will be as below

As seen from result above the FLOWR expression helps us in building the XML in the structure we want.
The first part of the query using FOR XML will built a simple XML structure with all attributes involved. Then we apply query function on top of the simple XML we built and use FLOWR expressions inside to get XML to the format we desire

Another illustration is given below

declare @catalog table
catalogid int identity(1001,1),
catalogdescription nvarchar(100),
catalogcreationdate datetime

insert @catalog (catalogdescription,catalogcreationdate)
(N'mens wear',getdate()-120),
(N'womens wear',getdate()-35),
(N'sports wear',getdate()-90),

declare @products table
ProductID  int identity(10001,1),
ProductDesc nvarchar(100),
CatalogID int

insert @products (ProductDesc,CatalogID)
values ('Crop Tops',1002),
('Sweat Shirts',1002),
('Bodyfit Jeans',1001),
('Golden Perfurme',1005),
('Pendant with Earstuds',1004),

declare @productattributeTypes table
AttributeTypeID int identity(10001,1),
AttributeTypeDesc  nvarchar(100)

insert @productattributeTypes (AttributeTypeDesc)
values ('Shoe Size'),('Belt Size'),('Base Material'),('Color'),('Pattern'),('Size')

declare @productattributevalues table
AttributeID int identity(10001,1),
ProductID  int ,
AttributeTypeID int,
AttributeValue nvarchar(100)

insert @productattributevalues (AttributeTypeID,ProductID,AttributeValue)
values (10003,10001,'Cotton'),
(10005,10003,'Slim fit'),
(10006,10004,'100 ml'),
(10003,10006,'Black Metal'),
(10003,10007,'White metal with stones'),

Here's the query

with the result as below


From the two illustrations above, its evident that FLOWR expression based method can be used effectively to generate nested XML structures


The full code for generating the XML structures can be found below

Monday, October 29, 2018

SQL Tips: String Or Binary Data Truncated Error Message Enhancement


I'm sure lots of us who have been developing in SQLServer over ages would agree to the fact that the error

String or binary data would be truncated.
The statement has been terminated.
is one of the most frustrating errors you would have ever come across in T-SQL. Especially in case of long stored procedures with lengthy INSERT...SELECT statements it was always a daunting to task to find the column which acted as the root cause for the above error. And most times the error happens at a later stage due to absence of any breaking data at the time of implementation.

A good majority of senior developers have always complained against this ambiguity and multiple connect items were logged for this issue which got pretty good support as well.


When feedback platform was moved to  Microsoft had opened a request in that for the connect requests and this had also got good number of votes

Accordingly MS started background work to fix this and finally the fix was released in SQL 2019 version to enhance the error message to include more information. Based on this, the error message has been modified to the below

String or binary data would be truncated in table 'XXXXXXXXXX', column 'YYYYYYY'. Truncated value: 'ZZZZZZ'.
The statement has been terminated.

This was really a good news for all of us, but still there was a small concern that we have wait a while to see this in action as most of the currently implemented instances were on SQL 2016 and SQL 2017.
But it seems MS read our mind on this and now I'm really happy to see the announcement that this enhancement has been backported to SQL 2017 CU 12 and on SQL 2016 SP2 CU.
To enable this currently a trace flag has also being introduced (trace flag 460) which can be enabled at session level or at server level itself. Once set it replaces the older error message with the new one above for truncation exceptions raised. The future SQL 2019 releases should have this message as the default and wont require setting the trace flag explicitly for this.


Lets see an illustration of the above error message on a sample table

The code will look like below

--setting the trace flag for the session
DBCC TRACEON  (460);  

--sample table
declare @t table(
v varchar(10)

-- insert values
insert @t 
('this is a long value to check for truncation error') -- this raises the error

Now lets check the result

As expected we will get the  new error message which gives clear indication of the table, column and value which caused the truncation exception. This makes it much easier for someone to debug and fix the issue.

Now if you turn off the trace flag and try, you can see that it reverts to the old error message 


As seen from the above illustration, this new enhancement on truncation error is really a life saver for someone developing or debugging Transact SQL code and is sure to save many hours of development effort on truncation issues which is one of the high frequent issues we come across in ETL, datawarehousing projects.

The official announcement regarding the release can be found in the below link

Let me end this article by conveying big thanks to Pedro Lopes (@SQLPedro) – Senior Program Manager and the entire MS team for the help and support provided in addressing this issue and coming up with the release.

Tuesday, October 16, 2018

SQL Agent Tips: Using JOBID Token in T-SQL Job Steps To Fetch The Job GUID


One of the pretty cool features available in SQL Server Agent is the ability to use tokens for returning values for many system parameters at runtime (for example Server Name, Job Name, Database Name etc).
Recently there was an issue reported in the forums regarding the usage of JOBID token for getting the details of job within job step. This prompted me to analyse more on the feature.
This article explains how you can use the JOBID token to return the job identifier information inside the job step


There are quite a few scenarios where we need to use job related information in scripts inside SQL Agent job steps. There are tokens available for this purpose which can be used to return data from job metadata.
One of common use case is when we need to capture the job id for logging purpose inside a step within the same job.
The code can be given as below

DECLARE @jobID varchar(100)


EXEC [dbo].[notifyjobfailure]


The notifyjobfailure procedure looks like this

CREATE proc  [dbo].[notifyjobfailure]
@jobid varchar(100)
insert jobnotify(jobname)
select @jobid

Where jobnotify is a simple table  for capturing the job_id along with the current system time.

Include this within step of a job and try executing it. We will find that the job step will fail like below

As seen from the image above, we get a conversion error trying to use the token inside job step.

To understand why this is happening I tweaked the datatype of the column within the table and the parameter to be of type varchar.

So modified code will look like this

ALTER proc  [dbo].[notifyjobfailure]
@jobid varchar(100)
insert jobnotify(jobname)
select @jobid


Try executing the job and we can see that it executes fine now.
Check the table and we can see the below data populated

Checking the data we can see that the token value gets passed in hexadecimal format rather than as a GUID.
Taking this into consideration, we can tweak the code again to add a conversion step to ensure value gets passed as a valid unique identifier (GUID). Accordingly, the code will look like this

DECLARE @jobID uniqueidentifier

SET @jobID = CONVERT(uniqueidentifier,$(ESCAPE_SQUOTE(JOBID)))

EXEC [dbo].[notifyjobfailure]

The procedure code will also be modified as below

CREATE proc  [dbo].[notifyjobfailure]
@jobid uniqueidentifieras
insert jobnotify(jobname)
select @jobid


Now go ahead and execute the job and you will find that it executes successfully


Based on the illustration above we can understand the below points

1. The value for JOBID token gets passed as a hexadecimal value
2. While trying to save the value to a table column of type uniqueidentifier always make sure we do an explicit conversion. Otherwise the code will break as seen from the illustration

Tuesday, August 14, 2018

SSIS Tips: Enforcing TLS 1.2 For SSIS 2012 Connections


The main inspiration behind this article comes from a recent issue faced in one of my projects for configuring TLS 1.2 based connectivity to a HTTP endpoint and steps taken in resolving the same


In one of the projects, there was a SQL Agent job which started suddenly failing. There was no changes done on any of the core functionality so it was evident that the failure had something to do with some changes done at the destination end.The failure was attributed to a task which was utilizing a HTTP connection manager to connect to a URL for downloading response in  XML format.

The failure message looked like below in the SQL Agent history

 Error: The underlying connection was closed: An unexpected error occurred on a send.
  Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
    An existing connection was forcibly closed by the remote host

On analysis and checking with the admin team, we came to know that they enabled TLS 1.2 on the server endpoint. This was causing the connection to fail as the SQLServer we had was on 2012 version and it was still using TLS 1.0 based connection.

The challenge was to see how this can be sorted out. This article discusses a quick solution which can be applied in scenarios like above to ensure successful connection


The solution involves forcing the connection to use TLS 1.2 this can be done by using the below single line of code inside a script task in your SSIS package.

This should be the first task inside the package and will have single line code as shown below

    System.Net.ServicePointManager.SecurityProtocol = (System.Net.SecurityProtocolType)3072;

Here's how code looks inside the Script Task

3072 corresponds to TLS 1.2 protocol in the SecurityProtocolType enumeration within System.Net namespace

namespace System.Net
    public enum SecurityProtocolType
       Ssl3 = 48,
       Tls = 192,
       Tls11 = 768,
       Tls12 = 3072,
And you need to have .Net framework 4.5 installed to get this work correctly.

Once this is done it enforces TLS 1.2 for the connection following and connections would be successful.


As shown above this method can be used in SSIS 2012 to ensure TLS 1.2 protocol is enforced for connecting to a client app which is using enhanced encryption

Monday, February 19, 2018

SSRS 2017 Tips: Solving Default Font Issue in SSDT 15.5 Preview


The impetus for writing this article came from a recent question which was asked in one of the forums.
This was regarding the creation of SSRS report using VS 2017 shell based SQLServer Data Tools. There was a post which indicated that preview tab of the SSDT 2017 causes some tablix data to disappear.
This article discusses on the root cause behind the issue and a workaround you shall use to avoid the issue.


Consider the below sample report for this example which shows some addresses

Now lets try using this data inside a report.

The source query is below

select SUBSTRING(LTRIM(RTRIM(DestAddr4)),1,2) as Post,DestAddr1,DestAddr4,TransportTelNo from dbo.SvrDestinations
WHERE DestAddr4 is not null
and LEN(LTRIM(RTRIM(DestAddr4))) > 2
ORDER BY DestAddr4

Using this in a table will look like this

Now go to the preview tab to check the report preview and we can see this


As you see from the screenshot, the data is missing in all the textboxes except the last one. This had us bewildered for a while and we started the analyzing the behavior to understand the root cause for this issue.
We tried exporting this to Excel and CSV and the screenshots are as below

The two screenshots showed that data was in fact available inside the tables which made it clear the issue was with the HTML viewer in the VS shell ( SSDT 15.5 Version)
Our next attempt was to use a matrix container instead of table container and see if the issue persists,
As per this, created a matrix container and added the same columns inside and the screenshot is as below

The original table and matrix is shown above as in the designer. The main table's visibility option is set to hide and matrix is set to visible.  The behavior would be as below

As you see, we were able to display the data properly inside matrix. One thing to note here is that all columns are included inside grouping columns in the matrix with no values in detail columns (you can see the mandatory detail column blank in the last screenshot. Its column visibility would be set to hide for it to not generate the blank column)
From the above trials, the major things to note are

  • The data is not visible while displayed inside a table
  • The data is visible inside a matrix only when its inside a group.
Taking these things to consideration the next step was to check the report code behind
As you're probably aware, the code behind can be viewed by right clicking the report file (rdl) from solution and choosing view code. The code behind would be in XML format.
The code for the report looks like this

Fortunately I had another instance of SSRS in my machine which was of version 2012. 
I compared code behind of a similar report to the above and found that in the current version we have an additional section on top which includes the font family information (highlighted in blue above).

I changed the font to Arial which was what my previous version of SSRS was using inside and tried running the report.
The report now looked like this

So as it turns out, the issue was with the default font family which was used inside SSDT.


As shown by the illustration above, SSDT 15.5 has an issue with preview mode not displaying full data correctly for tables and in matrix detailed rows. 
The issue seems to be caused due to the default font (Segoe UI) being applied by the HTML preview screen. Changing the font seems to fix the issue. So whenever you face similar issue, try tweaking the font by opening the code behind to ensure its not a font based issue before you look for the other causes.
Hopefully this article could save you couple of hours of effort in fixing the issue caused due to font setting in the future.


The sample RDL along with table creation script can be downloaded from the below link