Tuesday, October 16, 2018

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

Introduction

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

Illustration

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)

SET @jobID = '$(ESCAPE_SQUOTE(JOBID))'

EXEC [dbo].[notifyjobfailure]

    @jobID

The notifyjobfailure procedure looks like this

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

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)
as
insert jobnotify(jobname)
select @jobid

GO

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]
    @jobID

The procedure code will also be modified as below

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

GO

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

Conclusion

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

Impetus


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

Scenario


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




i.e.
 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

Solution

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
{
    [System.Flags]
    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.

Conclusion

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

Impetus 

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.

Illustration

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


Analysis


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.

Conclusion

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.

References

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

Wednesday, November 8, 2017

SQL Tips: Demystifying FORMAT behavior in Datetime vs Time Values

Introduction


Since SQL 2012 , one of the functions which I used quite regularly on my day to day coding has been the FORMAT function.
The major use case has been in formatting date and time based values to custom date and time formats. Though I rely upon front end to do the formatting on most occasions, sometimes we're required to do it at database layer as some tasks would be data feeds like Excel exports where formatted data has to be exported to Excel sheets.
This article clarifies the difference in the behavior of FORMAT function that you shall notice while working with datetime vs time based values

Scenario

Now lets set the scene for the topic on hand

Consider this small code snippet where I'm trying to format a standard datetime value to the format dd MMM yyyy hh:mm:ss AM/PM.

declare @dt datetime = '20171025 13:14:45'

SELECT FORMAT(@dt,'dd MMM yyyy hh:mm:ss tt')

The result is shown below


All is as expected and we are good

Now lets try using FORMAT function on a time value

declare @tm time = '14:34:56'

SELECT FORMAT(@tm,'hh:mm:ss tt')

And the result is as below


This was something totally unexpected. The expectation was to get the time based value like 02:34:56 PM 

Explanation

I decided to do some analysis on FORMAT function to get to the bottom of this problem.

The official documentation of FORMAT function also clearly states supporting date, datetime, datetime2, time datatypes as seen from here


But the catch is in the mapping table

If you check the documentation, time datatype alone is mapped to .Net datatype of TimeSpan while date, datetime, datetime2 are all mapped to DateTime datatype in .Net

The TimeSpan datatype accept custom specifiers as aid out by the article below


On contrast, the acceptable specifiers for DateTime are the below


Reading through the link you can note the below points regarding Format Specifiers

  • TimeSpan doesnt accept : as a valid separator whereas DateTime accepts : as a valid time separator
  • TimeSpan requires using \ as escape character to escape the : seperators
  • TimeSpan accepts only h or hh as specifier for hour part always returned in 24hr clock whereas DateTime accepts both lower and upper case (h,hh,H,HH) with lowercase giving time in 12h and uppercase in 24hr clock
  • DateTime accepts t or tt to return first or full part of AM/PM designator but TimeSpan doesnt support this as it always returns time in 24hr clock 
Based on the above comparison we can see that for getting time value in hh:mm:ss format we need to use FORMAT function like this

declare @tm time = '02:34:56'

SELECT FORMAT(@tm,'hh\:mm\:ss')

And result is as shown



If you want to get format as 02:34:56 PM  you need to cast it to datetime first before applying FORMAT function as per the stated points above
i.e like


declare @tm1 time = '02:34:56'

SELECT FORMAT(CAST(@tm1 as datetime),'hh:mm:ss tt')


And you shall get the intended result as shown


Conclusion

As seen from the illustration above, FORMAT function handles the datetime and time fields differently due to the difference in mapped .Net datatypes.

Tuesday, October 31, 2017

SQL Tips: Exploring Agent_Datetime Function

Introduction


Recently there was a discussion in one of the forums which helped me to learn about a function which has been there for a long time  in SQLServer and had eluded my knowledge till now.
This article explains on the details of the function along with the common use cases.

Agent_Datetime 

The function of interest is agent_datetime.
It resides in the msdb database of a SQLServer instance and accepts two arguments. First argument would be an eight digit integer which represents date portion of a datetime value. The second argument would be a six digit integer representing the time part of the datetime value in hours,minutes and seconds format.

The function can be invoked as below

Select msdb.dbo.agent_datetime(20171027,231134)

The result would be as below



As you see it created a datetime value 2017-10-27 23:11:34.000 from the two integer values passed.

Background

There is a clear intention behind naming the function as Agent_Datetime and placing it within the msdb database.
The function is not officially documented and is basically used internally for converting the date and time values related to sql jobs from msdb system tables.

Use Cases

1. Get SQL Agent related datetime values from integer based columns in msdb catalog objects

There are few columns like for ex. rundate and runtime in sysjobhistory table which represented a date value stored as integer internally.
So query like below



SELECT j.name,dbo.agent_datetime(jh.run_date,jh.run_time) AS RunDate
FROM dbo.sysjobs j
JOIN dbo.sysjobhistory jh
ON jh.job_id = j.job_id

Will give you the list of jobs with their run dates in proper datetime format from msdb database.

2. Generic use to generate datetime values from integer based date and time fields

Though the function is created in msdb we're free to use it in our user databases to merge integer based date and time values to a proper datetime value.

As an illustration see the below


The above screenshot shows three different illustrations regarding use of agent_datetime function with different types of arguments.
The first example passes 8 digit date and 6 digit time based integer values and merges them into a proper datetime value.
The second examples shows what happens when you pass a 4 digit integer value for the time part instead of the expected 6 digits. The function will interpret it in the format mm:ss and returns merged date with the minute and seconds value. If you have only hour and minute parts make sure you pass it in hhmm00 format (i.e adding 0s for the missing seconds)
The final illustration depicts the case where you've only a date part to pass. In this case, you need to pass a default value of '' for the time part when you invoke the agent_datetime function. The function will return full datetime value with time part being default (00:00:00)


Conclusion

As seen from the illustrations above the msdb.dbo.agent_datetime scalar function can be used to generate proper datetime values from two separate integer based date and time values. It can be used for sql agent based catalog queries in msdb as well as for the queries in other databases. 

The only caveat is that since its undocumented its better to refrain from using this on production code especially for the cases where we want the query to be run periodically. 
For adhoc querying we shall use the function as a quick way to generate datetime values from integer date and time equivalents.