Transact SQL – DateAdd and avoid overflow errors

Background

Transact SQL has adequate support for Date arithmetic exposed through dateadd, datediff, etc.

Interesting yesterday I ran into a stumbling block due to a relatively high value for the interval argument.

It graciously handles ints intervals.

But, choked on a bigint.

Error

Error Text

Msg 8115, Level 16, State 2, Line 14
Arithmetic overflow error converting expression to data type int.

Error Image

Workaround

Overview

  1. The workaround proposed by Michael Valentine Jones goes like this
    • Rather than try to add the interval at one time, add succesively
      • Add Days
        • Divide the original increment by 86400 if adding seconds
          • 86400
            • 24 Hours * 60 minutes * 60 seconds
      • Add Seconds
        • Get what remains
          • Get module of the 86400
        • Apply what remained

Code

[dbo].[udfn_dateAddMilliSecondAvoidOverflow]


use master
go

if object_id('dbo.udfn_dateAddMilliSecondAvoidOverflow') is null
begin

	exec
	(
		'
			create function dbo.udfn_dateAddMilliSecondAvoidOverflow()
			returns datetime
			as
			begin
				return 0
			end

		'
	)
end
go

alter function [dbo].[udfn_dateAddMilliSecondAvoidOverflow]
(
	  @increment bigint
	, @base		 datetime

)
returns datetime
as
begin

	return
		(
			dateadd
			(
				  MILLISECOND
				, @increment % (86400 * 1000 )
				, dateadd
					(
						  day
						, @increment / ( 86400 * 1000)
						, @base
					)


			)
		)

end

go

grant execute on [dbo].[udfn_dateAddMilliSecondAvoidOverflow] to [public]
go
	

Sample Data set

 

Credit Roll

Crediting Michael Valentine Jones
Link

 

 

Transact SQL – String Splitting Using XML

Background

Unfortunately splitting a String into rows was lacking in Transact SQL for a while.

MSFT changed that in Version 2016.

Split_String is capable and handy.

Here is the link.

 

Prior to v2016

Prior to Version 2016, there are many tools out in the wild for accomplishing same.

There are CLR Functions.  There are functions that use dbo.Numbers

 

XML

And, there are functions that rely on XML’s inherent ability to split an input into nodes.

 

XML

Standing On

Found a nice working model on sqlhints.com.

BTW, sqlHints.com is Basavaraj Biradar.

And, here is the particular post that we will be using.

It is titled “How to Split comma or any other character delimited string into a Table in Sql Server.”

 

Objective

  1. Look for separator
    • Replace separator with endNode and beginNode
      • Close out endNode
      • Start new node with beginNode
  2. Begin & End fragment
    • Begin Fragment with beginNode
    • End Fragment with endNode
  3. Start and end node fragment
    • XML needs root node
      • And, so we start string with rootNode
      • And, end with rootNode
  4. Use XQuery to parse node
    • Parse node using XQuery
    • Nodes ( “//node”)

Code

[stringSplit].[itvf_stringSplitUsingXML]

 


use [master]
go


if schema_id('stringSplit') is null
begin

	exec('create schema [stringSplit] authorization [dbo] ')

end
go

if object_id('[stringSplit].[itvf_stringSplitUsingXML]') is null
begin

	exec
	(
		'create function [stringSplit].[itvf_stringSplitUsingXML]
			()
			RETURNS @RESULT TABLE
			(
				Value VARCHAR(MAX)
			)
		as
		begin

			return

		end

		'
	) 

end
go


ALTER FUNCTION [stringSplit].[itvf_stringSplitUsingXML]
(
      @string  VARCHAR(MAX)
	, @separator CHAR(1) = ','
)
RETURNS @tblResult TABLE
(

	  [rowNumber] smallint not null identity(1,1)
	, [value]	  VARCHAR(600)
	, [xml]		  xml
	, [element]	  xml

)
AS
BEGIN

	/*

		a) sqlhints.com
		   http://sqlhints.com/tag/split-comma-separated-values-in-sql/
	
	*/    

	 DECLARE @xml XML
	 DECLARE @xmlAsString nvarchar(max)

	 declare @rootBegin varchar(10)
	 declare @rootEnd varchar(10)

	 declare @node      varchar(10)
	 declare @nodeBegin varchar(10)
	 declare @nodeEnd varchar(10)

	 declare @xmlNodeAsString as varchar(600)

	 set @rootBegin = '<root>'
	 set @rootEnd = '</root>'

	 set @node = 'node'
	 set @nodeBegin = '<node>'
	 set @nodeEnd = '</node>'

	 /*

		Look for separator, when found
			end earlier node by replacing separator
				 with [nodeEnd] 
				 and starting new node with [nodeBegin]

	 */
	 set @xmlNodeAsString = REPLACE
								(
									  @string
									, @separator
									, @nodeEnd + @nodeBegin
								)

	/*
		Bracket fragment with begin and end node
	*/
 	 SET @xmlNodeAsString =  @nodeBegin 		
							+ @xmlNodeAsString
							+ @nodeEnd

	/*
		Root node
		 separated out contents
		 End Root Node
	*/
 	 SET @xmlAsString = @rootBegin
							+ @xmlNodeAsString
							+ @rootEnd

	/*
		Convert to XML
	*/
	set @xml = @xmlAsString
 

	INSERT INTO @tblRESULT
	(
		  [value]
		, [xml]
		, [element]

	)
	SELECT 

		  [value]
		  = 
			(
				t.i.value
				(
						'(.)[1]'
					, 'varchar(max)'
				)
			)

		, [xml]
			= @xml

		 , [element]
		  = 
			(
				t.i.query
				('.')
			)
	  
	from @xml.nodes('//node') AS t(i)

	RETURN

END
go


 

Invoke

 


	declare @data varchar(600)

	set @data = 'sammie,bobbie,jackie'

	select *

	from   [master].[stringSplit].[itvf_stringSplitUsingXML]
			(
				  @data
				, default
			)

 

Output

Informatica – PowerCenter Express – Datatype – XML

Background

As I dig more into Informatica and MySQL, found out that we were not properly copying XML column from SQL Server into MySQL.

 

Table Structure

Here is our table structure.

MSSQL

Admin-CourseManagerUserLog-MSSQL

MySQL

Admin-CourseManagerUserLog-MYSQL

 

Informatica

Original Table Structure

Physical Data Object – Admin.CourseManagerUserLog

PhysicalDataObject

Explanation:

Later on, was cognizant that our XML Column, details, precision or size is 0

 

Mappings

Mappings

Explanation:

Later on, noticed that for the details column, the arrow is not full.

 

Mappings - Later

 

Run Mappings

Run the mapping.

And, things ran well.  Was not until later that I found that the details column on the destination, MySQL, was empty.

 

Create View on the Source

Here is a workaround.

In the view, add new columns to track the unsupported XML Column.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if object_id('[Admin].[CourseManagerUserLog_XMLColumnConverted]') is null
begin

	exec('create view [Admin].[CourseManagerUserLog_XMLColumnConverted] as select 1/0 as [shell] ')

end
go

alter view [Admin].[CourseManagerUserLog_XMLColumnConverted]
as

SELECT
		    [id]
		  , [username]
		  , [resourceID]
		  , [status]
		  , [message]
		  , [details]
		  , [detailsAsVarcharMax]
				= cast(	[details] as varchar(max))	
		  , [detailsAsVarchar]
				= cast(	[details] as varchar(8000))	
		  , [userID]
		  , [refID]
		  , [record_created]
		  , [appointmentID]

from   [Admin].[CourseManagerUserLog]
GO

GRANT SELECT ON [Admin].[CourseManagerUserLog_XMLColumnConverted] TO [public]

go

 

Informatica

Resource

Resource

Explanation

  1. details, XML Column, comes in xml.  But, size is 0
  2. detailsAsVarchar, varchar column, size is 8000 ( max size in MS SQL Server )
  3. detailsAsVarcharMax [varchar(max) ], size is 0, as well

 

Mappings

After AutoLink

After AutoLink ….

Mappings-After-Autolink

Those columns with matching names are Auto Linked.

 

After Column – detailsAsVarchar is linked to details

And, then we manually linked the columns whose names are not matched.

Here in:

  1. detailsAsVarchar (MSSQL) right-arrow details (MySQL)

Mappings - Later - Details - 0448PM