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

SSMS – “An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown”

Background

Eventful day this morning, had to take a walk around the office block.

You know it is a good day when you wake @ 4: 30 AM, pulled in the smartphone and heard what smart bloggers have to say, make the 5:30 AM Bus, made the train and walked the lake just to feel good about work.

Error

An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown

Remediation

It is not the Server, but your client, Sql Server Management Studio ( SSMS), in this case.

No need to reboot, but closed the Chrome Tabs, and when that did not help, exited SSMS.

 

Culprit

May be too much “Query Execution Plan” digest.

Looking for a lot of things through the Graphics pane; graphics is always more taxing on system.

Heart

Heart and I spoke about the next few days and how everything is sane and good.

 

Listening

Have a nice bit of work in, and it is only 2 PM.

Daryl Worley
Second Wind
Link

Don’t need a second wind, but can relate….

 

Transact SQL – Table Variable

 

Background

Transact SQL allows one to create tables on the fly.

Currently the two supported variants are temporary table ( session or global ) or Table variable.

Table Variable Versus Temp Table

  1. Lifetime
    • Table Variable can only be session specific
    • Temp table can be session and global
  2. Statistics
    • On Temp Tables, Statistics can be updated
    • Not so, on table variable

Table Variable

Sample

declare @tblStat TABLE
(
      [id]      smallint not null identity(1,1)
    , [object]  sysname  not null
    , [stat]    sysname  not null
    , [columns] nvarchar(4000) not null

    , [objectID] int null

    --  as object_id([object])
    
    , primary key
        (
            [id]
        )

    , unique 
        (
              [object]
            , [stat]  
        )
        WITH 
        (
            IGNORE_DUP_KEY = ON
        )

    , index [IDX_Columns]
        (
              [columns]
        )


)


Explanation

  1. Columns
    • object_id
      • Defined as null
        • Will be populated later
  2. Primary Key
    • Columns :- ID
  3. Unique Constraint
    • [object], [stat]
      • ignore dup key
  4. Index
    • INDX_Columns
      • [columns]

Unsupported

  1. Constraints
    • Supported
    • Unsupported
      • Named Constraints
  2. Index
    • Supported
      • NonClustered Index
    • Unsupported
      • Clustered Index

Transact SQL – Warning – “Type conversion in expression may affect “CardinalityEstimate” in query plan choice”

 

 

Background

Reviewing code and ran into this warning…

Code


use [tempdb]
go

declare @tblRange TABLE
(
      [id] int not null identity(1,1)

    , [min]  numeric(5,2) null
    , [max]  numeric(5,2) null

    , [range]  AS 
            (
                cast
					(
						case 
				            when [min]=[max] then CONVERT([varchar],CONVERT([float],[min])) 
					        else (
                            CONVERT([varchar],CONVERT([float],[min]))
                                +' - '
                                + CONVERT([varchar],CONVERT([float],[max]))
                        )			 
						end
					as varchar(30)
				)
            ) PERSISTED

)

insert into @tblRange
(
    [min], [max] 
)
select 3,3
union
select 0.5,10

select *
from   @tblRange

Query Plan

 

Warning

Image

Textual

Type conversion in expression (CONVERT(varchar(30),CONVERT(float(53),[min],0),0)) may affect “CardinalityEstimate” in query plan choice, Type conversion in expression (CONVERT(varchar(30),CONVERT(float(53),[max],0),0)) may affect “CardinalityEstimate” in query plan choice

Source Code Control

GitHub

DanielAdeniji/SQLServerTypeConversionMayAffectCardinalityEstimate
Link

Summary

This is a tough error as it appears there is no redemptive path.

 

Addendum

Karly Shockley ( 2017-12-28)

Karly Shockley is able to reproduce.

Code

Here is her working code..


set nocount on;
go

set XACT_ABORT on
go

declare @tblRange2 table
( 
	[min] numeric(5,2) null 
) 

select [min] = convert(varchar, [min]) 
from   @tblRange2 

select [min] = a.[min] 
from   @tblRange2 a 
where  convert (varchar, a.[min]) = 'abc' <span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>

Query Plan

Connect Item

As she is able to reproduce, she suggested that I open up a Connect Item…

Defect #11427260 has been filed with our Product group on your behalf. We will not have any knowledge of its status and will not be able to provide updates but if you would like to have more visibility into the progress of this request, you can always file a Connect bug/request here, https://connect.microsoft.com/SQLServer/Feedback

Transact SQL – Which objects are referenced?

 

Background

In our last post we discussed removing all other objects other than a few designated tables that a problematic queries are using.

 

Identify objects in use

 

Outline

In this post we will discuss a couple of options available for identifying the tables a programmable object uses.

 

Options

sp_depends

sp_depends is the classic Stored Procedure for doing so.

Syntax


exec sp_depends @objname = @object

 

Sample


declare @object sysname

set @object = '[dbo].[usp_Agent_Retrieve]'

exec sp_depends @objname = @object

 

Output

sp_depends_20171212_0547PM

 

sys.dm_sql_referenced_entities

sys.dm_sql_referenced_entities is a later iteration of sp_depends.

Syntax


select *

FROM sys.dm_sql_referenced_entities (@object, 'OBJECT') tblRef

 

Sample


declare @object sysname

set @object = '[dbo].[usp_Agent_Retrieve]'

SELECT 
          [referenced_schema_name]
        , [referenced_entity_name]
        , [columnCount] = count(*)
        , [isSelected]
            = max(cast(tblRef.[is_selected] as tinyint))
        , [isUpdated]
            = max(cast(tblRef.[is_updated] as tinyint))
/*
        , [isInsert]
            = max(cast(tblRef.[is_insert_all] as tinyint))
*/

FROM sys.dm_sql_referenced_entities (@object, 'OBJECT') tblRef

group by

          tblRef.[referenced_schema_name]
        , tblRef.[referenced_entity_name]

order by
          tblRef.[referenced_schema_name]
        , tblRef.[referenced_entity_name]

 

 

Output

sysDOTdm_sql_referenced_entities__20171212__0558PM

 

 

Summary

There are some distinct advantages to choosing the more modern path, sys.dm_sql_referenced_entities.

Inclusive are:

  1. Because it is table value function compared to Stored Procedure ( SP ), it is a bit easier to consume
    • We can issue distinct and group by and just get the object names and not have to take entities and columns
  2. It provides detailed information regarding use-cases
    • It tells us which columns are involved in updates, selects, and so forth

 

References

  1. Microsoft
    • Docs / SQL / Relational databases / System dynamic management views
      • sys.dm_sql_referenced_entities
        • sys.dm_sql_referenced_entities (Transact-SQL)
          Link

SQL Server – keeping a few while discarding most Objects

Background

Time to look for help again.

The specific database has too many objects, but I only need to keep about twenty tables or so.

And, remove the other ones.

Script

And, so I wrote a little script.

Outline

There are two parts:

  1. dbo.udtt_Object ( Table Type )
  2. dbo.sp_ScriptDropUnTaggedObjects ( Stored Procedure )

Objects

Table Type

dbo.udtt_Object


if not exists
        (
            select *
            from  sys.types tblSST
            where tblSST.[name] = 'udtt_Object'
        ) 
begin

    CREATE TYPE [dbo].[udtt_Object] AS TABLE
    (

          [id]		bigint	not null identity(1,1) 
        , [schema]  sysname not null 
        , [object]  sysname not null

        , primary key
            (
                  [schema]
                , [object]
            )

    )

end

 

Stored procedure

dbo.sp_ScriptDropUnTaggedObjects


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

    exec('create procedure [dbo].[sp_ScriptDropUnTaggedObjects] as ')

end
go

alter procedure [dbo].[sp_ScriptDropUnTaggedObjects]
(
      @tblTable [dbo].[udtt_Object] READONLY
    , @errorIfObjectsToKeeepListIsEmpty bit = 1
)
as
begin

    set nocount on

    
    declare @tblTableCited TABLE
    (
          [id]	   int not null identity(1,1)
        , [schema] sysname not null
        , [object]  sysname not null
        , [type]    char(2) not null
        , [dropClause] 
                as case
                    when [type] = 'U' then ' Table '
                  end
    )

    declare @id smallint
    declare @table sysname 
    declare @iCount int
    declare @idMax smallint

    declare @idCited bigint
    declare @idCitedMax bigint

    declare @citedSchema	  sysname
    declare @citedObject	  sysname
    declare @citedObjectType  char(2)

    declare @citedObjectDropClause	varchar(30)

    declare @strLog nvarchar(600)
    declare @sql    nvarchar(255)

    declare @iNumberofTablesCited int


    declare @iNumberofAttempts int
    declare @iNumberofAttemptsMax int

    declare @CHAR_TAB char(1)

    set @CHAR_TAB = char(9);

    set @id = 1

    set @iCount = ( select count(*) from  @tblTable)
    set @idMax = ( select max( [id]) from  @tblTable)

    --print '@idMax ' + cast(@idMax as varchar(10))

    set @iNumberofTablesCited = -1
    set @idCited = 1

    set @iNumberofAttempts = 1
    set @iNUmberofAttemptsMax = 50


    set @strLog = 'Number of tables tagged ' 
                    + cast(isNull(@idMax, -1) as varchar(10))

    print @strLog


    if (
            (
                   ( @iCount = 0)
                or ( @idMax is null)
            )
            and 
            (
                (@errorIfObjectsToKeeepListIsEmpty = 1)
            )
       )
       begin

        set @strLog = 'Object to keep list is empty'

        raiserror(@strLog, 16,1)

        return -1

       end

    while (
                    ( @iNumberofTablesCited != 0)
                and ( @iNumberofAttempts <= @iNumberofAttemptsMax) ) begin set @strLog = @CHAR_TAB + 'Attempt ' + cast(@iNumberofAttempts as varchar(3)) print @strLog delete from @tblTableCited insert into @tblTableCited ( [schema] , [object] , [type] ) select tblSS.[name] , tblSO.[name] , tblSO.[type] from sys.objects tblSO inner join sys.schemas tblSS on tblSO.[schema_id] = tblSS.[schema_id] where tblSO.[type] = 'U' and not exists ( select tblT.[object] from @tblTable tblT where tblT.[object] = tblSO.[name] ) and not exists ( select * from sys.foreign_keys tblSFK where tblSO.[object_id] = tblSFK.[referenced_object_id] ) set @idCitedMax = ( select max( [id]) from @tblTableCited) set @iNumberofTablesCited = (select count(*) from @tblTableCited ) set @strLog = @CHAR_TAB + cast(@iNumberofTablesCited as varchar(10)) + ' objects cited' print @strLog if (@iNumberofTablesCited > 0)
        begin

            while (@idCited <= @idCitedMax)

            begin

                select
                          @citedSchema = tblTC.[schema]
                        , @citedObject = tblTC.[object]
                        , @citedObjectType = tblTC.[type]
                        , @citedObjectDropClause = tblTC.[dropClause]

                from    @tblTableCited tblTC
        
                where  tblTC.[id] = @idCited

                if (
                            ( @citedSchema is not null )
                        and ( @citedObject is not null )
                        and (@citedObjectDropClause is not null)
                    )
                begin
                
                    set @strLog =
                                    @CHAR_TAB + @CHAR_TAB + 
                                    + cast(@idCited as varchar(10))
                                    + @citedSchema
                                    + '.'
                                    + @citedObject


                    set @sql = 'DROP '
                                    + @citedObjectDropClause
                                    + quotename(@citedSchema)
                                    + '.'
                                    + QUOTENAME(@citedObject)

                    --print @sql

                    set @strLog = @CHAR_TAB + @CHAR_TAB + @sql
                
                    print @strLog

                    exec(@sql)

                end

                set @idCited = @idCited + 1

            end

        end -- @iNumberofTablesCited

        
        set @iNumberofAttempts = @iNumberofAttempts + 1

    end -- Number of retries

end
go


exec sp_MS_marksystemobject '[dbo].[sp_ScriptDropUnTaggedObjects]'
go

 

Invoke


set XACT_ABORT on
go

set nocount on
go

declare @tblTable as [dbo].[udtt_Object]

declare @commit bit

set @commit = 0

insert into @tblTable
select 'dbo', 'Users'
union
select 'dbo', 'Roles'

begin tran


    exec [dbo].[sp_ScriptDropUnTaggedObjects]
          @tblTable = @tblTable
        , @errorIfObjectsToKeeepListIsEmpty = 1

while (@@trancount > 0)
begin

    if (@commit=1)
    begin
    
        print 'commit tran';
        commit tran;
    
    end
    else
    begin
    
        print 'rollback tran';
        rollback tran;
    
    end
end

GitHub

DanielAdeniji/SQLServerScriptDropUntaggedObjects
Link

 

Log Shipping – Status Tracking

Background

Reviewing Log Shipping on one of our setups and wanted a generic script that will allow quick review of where things are.

Code

Here is where things are:

Primary

SQL


use [master]
go

if schema_id('logShipping') is null
begin

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

end
go

if object_id('[logShipping].[if_StatusPrimary]') is null
begin

    exec('CREATE FUNCTION [logShipping].[if_StatusPrimary] 
            ()
            RETURNS TABLE   
            AS
            RETURN 
            (
                -- Add the SELECT statement with parameter references here
                SELECT [shell] = 0
            )
        ')
end
go


ALTER FUNCTION [logShipping].[if_StatusPrimary] 
()
RETURNS TABLE   
AS
RETURN 
(

    select 
             [sqlInstancePrimary]
              = tblLSMP.[primary_server]
    
            , [databasePrimary]
                = tblLSMP.[primary_database]

            /*
                Process - Backup - Begin
            */
            , [backupDate]
                = tblLSMP.[last_backup_date]

            , [backupDateUTC]
                = tblLSMP.[last_backup_date_utc]

            , [timeSinceLastbackupInMinutes]
                =
                    datediff
                    (
                          minute
                        , tblLSMP.[last_backup_date]
                        , getdate()
                    )


            , [lastbackupFile]
                = tblLSMP.[last_backup_file]

            , [lastbackupFileTS]
                = msdb.[dbo].[agent_datetime]
                    (
                        substring
                        (

                            substring
                            (
                              tblLSMP.[last_backup_file]
                            , charindex( '_2', tblLSMP.[last_backup_file]) + 1
                            , charindex( '.', tblLSMP.[last_backup_file])
                               - charindex( '_2', tblLSMP.[last_backup_file])
                               - 1
                            )
                            , 1
                            , 8
                        )

                        , substring
                        (

                            substring
                            (
                              tblLSMP.[last_backup_file]
                            , charindex( '_2', tblLSMP.[last_backup_file]) + 1
                            , charindex( '.', tblLSMP.[last_backup_file])
                               - charindex( '_2', tblLSMP.[last_backup_file])
                               - 1
                            )
                            , 9
                            , 6
                        )

                    )



            , [lastbackupFileTSLocale]
                = dateadd
                    (
                          minute
                        , datediff
                            (
                                  minute
                                , tblLSMP.[last_backup_date]
                                , tblLSMP.[last_backup_date_utc]
                            ) * -1
                        , msdb.[dbo].[agent_datetime]
                        (
                            substring
                            (

                                substring
                                (
                                  tblLSMP.[last_backup_file]
                                , charindex( '_2', tblLSMP.[last_backup_file]) + 1
                                , charindex( '.', tblLSMP.[last_backup_file])
                                   - charindex( '_2', tblLSMP.[last_backup_file])
                                   - 1
                                )
                                , 1
                                , 8
                            )

                            , substring
                            (

                                substring
                                (
                                  tblLSMP.[last_backup_file]
                                , charindex( '_2', tblLSMP.[last_backup_file]) + 1
                                , charindex( '.', tblLSMP.[last_backup_file])
                                   - charindex( '_2', tblLSMP.[last_backup_file])
                                   - 1
                                )
                                , 9
                                , 6
                            )

                        )
        
                )

            /*
                Process - Backup - End
            */



    from   [msdb]..[log_shipping_monitor_primary] tblLSMP

)
go

Invoke


select *
from   [master].[logShipping].[if_StatusPrimary]()

Output

Secondary

SQL


use [master]
go

if schema_id('logShipping') is null
begin

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

end
go

if object_id('[logShipping].[IF_LogShippingMetric]') is not null
begin

    drop function [logShipping].[IF_LogShippingMetric]

end
go


if object_id('[logShipping].[if_StatusSecondary]') is null
begin

    exec('CREATE FUNCTION [logShipping].[if_StatusSecondary] 
            ()
            RETURNS TABLE   
            AS
            RETURN 
            (
                -- Add the SELECT statement with parameter references here
                SELECT [shell] = 0
            )
        ')
end
go


ALTER FUNCTION [logShipping].[if_StatusSecondary] 
()
RETURNS TABLE   
AS
RETURN 
(

    select 
             [sqlInstancePrimary]
              = tblLSMS.[primary_server]
        
            , [sqlInstanceSecondary]
                = tblLSMS.[secondary_server]
        
            , [databasePrimary]
                = tblLSMS.[primary_database]

            /*
                Process - Copy - Begin
            */
            , [copyDate]
                = tblLSMS.[last_copied_date]

            , [copyDateUTC]
                = tblLSMS.[last_copied_date_utc]

            , [timeSinceLastCopiedInMinutes]
                =
                    datediff
                    (
                          minute
                        , tblLSMS.last_copied_date
                        , getdate()
                    )


            , [lastCopiedFile]
                = tblLSMS.[last_copied_file]

            , [lastCopiedFileTS]
                = msdb.[dbo].[agent_datetime]
                    (
                        substring
                        (

                            substring
                            (
                              tblLSMS.[last_copied_file]
                            , charindex( '_2', tblLSMS.[last_copied_file]) + 1
                            , charindex( '.', tblLSMS.[last_copied_file])
                               - charindex( '_2', tblLSMS.[last_copied_file])
                               - 1
                            )
                            , 1
                            , 8
                        )

                        , substring
                        (

                            substring
                            (
                              tblLSMS.[last_copied_file]
                            , charindex( '_2', tblLSMS.[last_copied_file]) + 1
                            , charindex( '.', tblLSMS.[last_copied_file])
                               - charindex( '_2', tblLSMS.[last_copied_file])
                               - 1
                            )
                            , 9
                            , 6
                        )

                    )

           , [lastCopiedFileTSLocale]
                = dateadd
                    (
                          minute
                        , datediff
                            (
                                  minute
                                , getdate()
                                , getutcdate()
                            ) * -1
                        , msdb.[dbo].[agent_datetime]
                        (
                            substring
                            (

                                substring
                                (
                                  tblLSMS.[last_copied_file]
                                , charindex( '_2', tblLSMS.[last_copied_file]) + 1
                                , charindex( '.', tblLSMS.[last_copied_file])
                                   - charindex( '_2', tblLSMS.[last_copied_file])
                                   - 1
                                )
                                , 1
                                , 8
                            )

                            , substring
                            (

                                substring
                                (
                                  tblLSMS.[last_copied_file]
                                , charindex( '_2', tblLSMS.[last_copied_file]) + 1
                                , charindex( '.', tblLSMS.[last_copied_file])
                                   - charindex( '_2', tblLSMS.[last_copied_file])
                                   - 1
                                )
                                , 9
                                , 6
                            )

                        )
        
                )

            /*
                Process - Copy - End
            */


            /*
                Process - Restoration - Begin
            */

            , [restoreDate]
                = tblLSMS.last_restored_date 

            , [timeSinceLastRestoreInMinutes]
                =
                    datediff
                    (
                          minute
                        , tblLSMS.last_restored_date
                        , getdate()
                    )

            , [lastRestoredFile]
                = tblLSMS.[last_restored_file]

            , [lastRestoredFileTS]
                = msdb.[dbo].[agent_datetime]
                    (
                        substring
                        (

                            substring
                            (
                              tblLSMS.[last_restored_file]
                            , charindex( '_2', tblLSMS.[last_restored_file]) + 1
                            , charindex( '.', tblLSMS.[last_restored_file])
                               - charindex( '_2', tblLSMS.[last_restored_file])
                               - 1
                            )
                            , 1
                            , 8
                        )

                        , substring
                        (

                            substring
                            (
                              tblLSMS.[last_restored_file]
                            , charindex( '_2', tblLSMS.[last_restored_file]) + 1
                            , charindex( '.', tblLSMS.[last_restored_file])
                               - charindex( '_2', tblLSMS.[last_restored_file])
                               - 1
                            )
                            , 9
                            , 6
                        )

                    )

            , [lastRestoredFileTSLocale]
                = dateadd
                    (
                          minute
                        , datediff
                            (
                                  minute
                                , getdate()
                                , getutcdate()
                            ) * -1
                        , msdb.[dbo].[agent_datetime]
                        (
                            substring
                            (

                                substring
                                (
                                  tblLSMS.[last_restored_file]
                                , charindex( '_2', tblLSMS.[last_restored_file]) + 1
                                , charindex( '.', tblLSMS.[last_restored_file])
                                   - charindex( '_2', tblLSMS.[last_restored_file])
                                   - 1
                                )
                                , 1
                                , 8
                            )

                            , substring
                            (

                                substring
                                (
                                  tblLSMS.[last_restored_file]
                                , charindex( '_2', tblLSMS.[last_restored_file]) + 1
                                , charindex( '.', tblLSMS.[last_restored_file])
                                   - charindex( '_2', tblLSMS.[last_restored_file])
                                   - 1
                                )
                                , 9
                                , 6
                            )

                        )
        
                )

            /*
                Process - Restoration - End
            */

    from   [msdb]..log_shipping_monitor_secondary tblLSMS

)
go

Invoke


select *
from   [master].[logShipping].[if_StatusSecondary]()

Output

 

Version Control

GitHub

  1. DanielAdeniji/SQLServerLogShippingStatus
    Link

 

References

  1. Docs / SQL / Relational databases / System tables
    • log_shipping_monitor_primary
      • log_shipping_monitor_primary (Transact-SQL)
        Link
    • log_shipping_monitor_secondary (Transact-SQL)
      • log_shipping_monitor_secondary (Transact-SQL)
        Link