Hey, Kristof ! – Help for people that read …

Do you know how to script database objects from a script?

Post author By Kristof
Post date 2024-07-22

Why yes? With this script it’s easy as ABC. Just fill in the list of items you want scripted and off you go. This way you can script any stored procedure, view, function or anything else that resides in the sys.sql_modules table.

There is a small piece of code that will loop through the definition if it’s longer than 4000 characters long. The PRINT statement will only print the first 4000 characters of a (n)varchar variable. To work around this, we split off 4000 characters, then find the last carriage return and throw away the remainder of the string. This way we don’t get any nasty splits on the screen. Then we print that string and we subtract the length of that string from the original string, and we go again.

DECLARE
    @FQObjectName NVARCHAR(257),
    @Definition   NVARCHAR(MAX),
    @Separator    NVARCHAR(80)  = REPLICATE('-', 80),
    @PrintBlock   NVARCHAR(4000)

DECLARE @tbDefinition TABLE (FQObjectName NVARCHAR(257), Definition NVARCHAR(MAX))

INSERT INTO
    @tbDefinition (FQObjectName, Definition)
SELECT
    CONCAT_WS('.', OBJECT_SCHEMA_NAME(SM.object_id), OBJECT_NAME(SM.object_id)) AS FQObjectName,
    SM.definition
FROM
    sys.sql_modules AS SM
WHERE
    OBJECT_NAME(SM.object_id) IN ('UnqualifiedObjectOne', 'UnqualifiedObjectOne')

DECLARE cDefinitions CURSOR FOR
SELECT  TD.FQObjectName, TD.Definition FROM @tbDefinition AS TD

OPEN cDefinitions

FETCH NEXT FROM cDefinitions
INTO
    @FQObjectName,
    @Definition

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Separator
    PRINT CONCAT('-- ', @FQObjectName)
    PRINT @Separator

    WHILE LEN(@Definition) > 4000
    BEGIN
        -- Fix because PRINT is limited to 4000 chars
        SET @PrintBlock = REVERSE(LEFT(@Definition, 4000))
        SET @PrintBlock = REVERSE(SUBSTRING(@PrintBlock, CHARINDEX(char(13), @PrintBlock), 99999))

        PRINT @PrintBlock
        SET @Definition = RIGHT(@Definition, LEN(@Definition) - LEN(@PrintBlock))
    END

    PRINT @Definition
    PRINT 'GO'
    FETCH NEXT FROM cDefinitions
    INTO
        @FQObjectName,
        @Definition
END

CLOSE cDefinitions
DEALLOCATE cDefinitions
GO

DECLARE

@FQObjectName NVARCHAR(257),

@Definition NVARCHAR(MAX),

@Separator NVARCHAR(80) = REPLICATE('-', 80),

@PrintBlock NVARCHAR(4000)

DECLARE @tbDefinition TABLE (FQObjectName NVARCHAR(257), Definition NVARCHAR(MAX))

INSERT INTO

@tbDefinition (FQObjectName, Definition)

SELECT

CONCAT_WS('.', OBJECT_SCHEMA_NAME(SM.object_id), OBJECT_NAME(SM.object_id)) AS FQObjectName,

SM.definition

FROM

sys.sql_modules AS SM

WHERE

OBJECT_NAME(SM.object_id) IN ('UnqualifiedObjectOne', 'UnqualifiedObjectOne')

DECLARE cDefinitions CURSOR FOR

SELECT TD.FQObjectName, TD.Definition FROM @tbDefinition AS TD

OPEN cDefinitions

FETCH NEXT FROM cDefinitions

INTO

@FQObjectName,

@Definition

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @Separator

PRINT CONCAT('-- ', @FQObjectName)

PRINT @Separator

WHILE LEN(@Definition) > 4000

BEGIN

-- Fix because PRINT is limited to 4000 chars

SET @PrintBlock = REVERSE(LEFT(@Definition, 4000))

SET @PrintBlock = REVERSE(SUBSTRING(@PrintBlock, CHARINDEX(char(13), @PrintBlock), 99999))

PRINT @PrintBlock

SET @Definition = RIGHT(@Definition, LEN(@Definition) - LEN(@PrintBlock))

END

PRINT @Definition

PRINT 'GO'

FETCH NEXT FROM cDefinitions

INTO

@FQObjectName,

@Definition

END

CLOSE cDefinitions

DEALLOCATE cDefinitions

Do you know how to check a phone number?

Post author By Kristof
Post date 2022-11-28

Of course I do! Here’s a little nifty function you can use or expand for your own needs. This was made for Dutch mobile phone numbers but can be easily tweaked for any other type of phone number.

create or alter function [dbo].[tvf_FormatPhoneNumber]
(
    @PhoneNumber nvarchar(max)
)
returns table
as
return
(
with LoopForDigits (number) as
(
    select
        substring(@PhoneNumber , NMB.number, 1) as [text()]
    from 
        master..spt_values as NMB
    where 
        substring(@PhoneNumber , NMB.number, 1) like '[0-9]'
    and NMB.number between 0 and len(@PhoneNumber )
    and NMB.type = 'P'
    for 
        xml path ('')
),
PhonePattern as
(
    select
        pattern = '%6[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
)
select
    PhoneNumber = concat('31', substring(DIG.number, patindex(PHP.pattern, DIG.number), 9))
from 
    LoopForDigits as DIG
        inner join 
    PhonePattern PHP
        on patindex(PHP.pattern, DIG.number) > 0
)

create or alter function [dbo].[tvf_FormatPhoneNumber]

(

@PhoneNumber nvarchar(max)

)

returns table

return

(

with LoopForDigits (number) as

(

select

substring(@PhoneNumber , NMB.number, 1) as [text()]

from

master..spt_values as NMB

where

substring(@PhoneNumber , NMB.number, 1) like '[0-9]'

and NMB.number between 0 and len(@PhoneNumber )

and NMB.type = 'P'

for

xml path ('')

PhonePattern as

(

select

pattern = '%6[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

)

select

PhoneNumber = concat('31', substring(DIG.number, patindex(PHP.pattern, DIG.number), 9))

from

LoopForDigits as DIG

inner join

PhonePattern PHP

on patindex(PHP.pattern, DIG.number) > 0

)

The way this works is as follows.

First we split the string into a table of single characters. We then filter the ones that aren’t numeric. We use the like function because isnumeric would not filter anything that is used to represent a number like ‘-‘ or ‘+’ in the phone numbers. Finally we concatenate with the xml-trick. Then, we select the part of the resulting string that matches our pattern.

Change the pattern to suit your country’s phone number style – or you can just do the check and return a boolean to indicate it’s actually a valid phone number or not.

How can I use JSON in T-SQL to maintain configurations

Post author By Kristof
Post date 2022-11-01

declare @JSON varchar(max);
set @JSON =
'
[
	{
		"ConfigurationParameterID":1,
		"ParameterCode":"Beehive",
		"ParameterDescription":"Global parameter",
		"ParameterDataType":"D",
		"DefaultValueChar":null,
		"DefaultValueNumeric":null,
		"DefaultValueDate":null,
		"DefaultValueBit":null,
		"CreationDate":"2021-07-14T00:00:00",
		"CreatedBy":"K. Elst",
		"LastUpdateDate":"2021-07-14T00:00:00",
		"LastUpdatedBy":"K. Elst"
	},
	{
		"ConfigurationParameterID":2,
		"ParameterCode":"Ape",
		"ParameterDescription":"It''s a bee",
		"ParameterDataType":"C",
		"DefaultValueChar":null,
		"DefaultValueNumeric":null,
		"DefaultValueDate":null,
		"DefaultValueBit":null,
		"CreationDate":"2021-07-15T00:00:00",
		"CreatedBy":"K. Elst",
		"LastUpdateDate":"2021-07-15T00:00:00",
		"LastUpdatedBy":"K. Elst"
	}
]';

select
    PRM.ConfigurationParameterID,
    PRM.ParameterCode,
    PRM.ParameterDescription,
    PRM.ParameterDataType,
    PRM.DefaultValueChar,
    PRM.DefaultValueNumeric,
    PRM.DefaultValueDate,
    PRM.DefaultValueBit,
    PRM.CreationDate,
    PRM.CreatedBy,
    PRM.LastUpdateDate,
    PRM.LastUpdatedBy
from openjson(@JSON)
with
(
    ConfigurationParameterID int,
    ParameterCode nvarchar(50),
    ParameterDescription nvarchar(255),
    ParameterDataType nchar(1),
    DefaultValueChar nvarchar(max),
    DefaultValueNumeric float,
    DefaultValueDate datetime,
    DefaultValueBit bit,
    CreationDate datetime,
    CreatedBy nvarchar(255),
    LastUpdateDate datetime,
    LastUpdatedBy nvarchar(255)
)
as PRM

declare @JSON varchar(max);

set @JSON =

[

{

"ConfigurationParameterID":1,

"ParameterCode":"Beehive",

"ParameterDescription":"Global parameter",

"ParameterDataType":"D",

"DefaultValueChar":null,

"DefaultValueNumeric":null,

"DefaultValueDate":null,

"DefaultValueBit":null,

"CreationDate":"2021-07-14T00:00:00",

"CreatedBy":"K. Elst",

"LastUpdateDate":"2021-07-14T00:00:00",

"LastUpdatedBy":"K. Elst"

{

"ConfigurationParameterID":2,

"ParameterCode":"Ape",

"ParameterDescription":"It''s a bee",

"ParameterDataType":"C",

"DefaultValueChar":null,

"DefaultValueNumeric":null,

"DefaultValueDate":null,

"DefaultValueBit":null,

"CreationDate":"2021-07-15T00:00:00",

"CreatedBy":"K. Elst",

"LastUpdateDate":"2021-07-15T00:00:00",

"LastUpdatedBy":"K. Elst"

}

]';

select

PRM.ConfigurationParameterID,

PRM.ParameterCode,

PRM.ParameterDescription,

PRM.ParameterDataType,

PRM.DefaultValueChar,

PRM.DefaultValueNumeric,

PRM.DefaultValueDate,

PRM.DefaultValueBit,

PRM.CreationDate,

PRM.CreatedBy,

PRM.LastUpdateDate,

PRM.LastUpdatedBy

from openjson(@JSON)

with

(

ConfigurationParameterID int,

ParameterCode nvarchar(50),

ParameterDescription nvarchar(255),

ParameterDataType nchar(1),

DefaultValueChar nvarchar(max),

DefaultValueNumeric float,

DefaultValueDate datetime,

DefaultValueBit bit,

CreationDate datetime,

CreatedBy nvarchar(255),

LastUpdateDate datetime,

LastUpdatedBy nvarchar(255)

)

as PRM

Do you know how I can easily perform an 11-test (Elfproef)

Post author By Kristof
Post date 2022-09-06

The elfproef is a Dutch validation check that was used for bank account numbers before the introduction of IBAN and is still used for BSN (National identifier) and payment references in bank transaction. Here’s a sweet inline table valued function that will yield instant results. 1 million iterations of this take up almost no cpu time so it’s very fast.

The first part checks if the supplied value is numeric. If it is , we work with the supplied value, otherwise we work with the value 1 which will never yield a valid BSN. We cannot work with if/then constructs because we are working with an inline table function.

I used a JSON array for configuration because it makes the code easier to read. If you want to know how an elfproef works, here’s an explanation in Dutch: https://nl.wikipedia.org/wiki/Elfproef

Example usage: select BSN from Personen as PRS WHERE dbo.tvf_CheckElfProef(PRS.BSN) = 0

create or alter function dbo.tvf_CheckElfproef
(
    @Bsn varchar(9)
)
returns table
as
return
(
with Bsn as
(
    select
        Nr = cast(iif(isnumeric(@Bsn) = 1, @BsnNr, 1) as bigint)
),
Factoren as
(
    select
        Positie
      , Factor
    from openjson('
        [
            {"Positie": 1,"Factor": -1},
            {"Positie": 2,"Factor": 2},
            {"Positie": 3,"Factor": 3},
            {"Positie": 4,"Factor": 4},
            {"Positie": 5,"Factor": 5},
            {"Positie": 6,"Factor": 6},
            {"Positie": 7,"Factor": 7},
            {"Positie": 8,"Factor": 8},
            {"Positie": 9,"Factor": 9}
        ]
        ') with (Positie smallint, Factor smallint)
),
Totalen as
(
    select
        Totaal = sum((nr % power(10, Positie) / power(10, Positie - 1)) * Factor)
    from Factoren
    cross join Bsn
)
select
    ResultaatElfProef = Totaal % 11
from Totalen
)

create or alter function dbo.tvf_CheckElfproef

(

@Bsn varchar(9)

)

returns table

return

(

with Bsn as

(

select

Nr = cast(iif(isnumeric(@Bsn) = 1, @BsnNr, 1) as bigint)

Factoren as

(

select

Positie

, Factor

from openjson('

[

{"Positie": 1,"Factor": -1},

{"Positie": 2,"Factor": 2},

{"Positie": 3,"Factor": 3},

{"Positie": 4,"Factor": 4},

{"Positie": 5,"Factor": 5},

{"Positie": 6,"Factor": 6},

{"Positie": 7,"Factor": 7},

{"Positie": 8,"Factor": 8},

{"Positie": 9,"Factor": 9}

]

') with (Positie smallint, Factor smallint)

Totalen as

(

select

Totaal = sum((nr % power(10, Positie) / power(10, Positie - 1)) * Factor)

from Factoren

cross join Bsn

)

select

ResultaatElfProef = Totaal % 11

from Totalen

)

Do you know how to fix error 502 or 504 when saving a game in Baldur’s Gate 3?

Post author By Kristof
Post date 2022-06-13

Actually, I do. You have too many save games and you need to delete a few. But, there’s no delete option I hear you say? Simply select a save game and hit “delete” on the keyboard. It will ask you if you are sure (of course you are) and then you can save again. Woohoo! That’s some hard earned XP saved right there.

I need a script that will search all objects in a database for text or a combination of keywords

Post author By Kristof
Post date 2022-03-16

Oh, and I need a way to present it to my boss in an easy-to-use-excel-format.

Well, this code will search all your databases for the text combinations you provide and return the views that contain the keywords. You can easily modify this to include stored procedures or functions and you can extend the search combinations and what it says as a response to certain keywords being found.

declare @SearchText       nvarchar(max) = 'MuFunction(@SomeParam)'
      , @IncludeSystemDbs as tinyint    = 0 --1 to include

declare @tbSearchResults table (
    fqo                     nvarchar(384)
  , object_type_description nvarchar(max)
)
declare @SearchSql nvarchar(max) = formatmessage(
'
use ?

select 
    concat_ws(''.'', quotename(''?''), quotename(object_schema_name(ASM.object_id)), quotename(object_name(ASM.object_id))),
    OBJ.type_desc
from 
    sys.all_sql_modules as ASM
        inner join 
    sys.objects OBJ on ASM.object_id = OBJ.object_id
where 
    ASM.definition like ''%%%s%%''
and exists (
                select 
                    1/0
                from
                    sys.databases as DTB
                where
                (
                    DTB.name = ''?''
                and (
                        is_distributor = 0
                    or  ''?'' not in (''master'',''tempdb'',''model'', ''msdb'')
                    or %d = 1
                    )
                )
            )
', @SearchText, @IncludeSystemDbs)

insert into @tbSearchResults
exec sys.sp_MSforeachdb @command1 = @SearchSql

select
    SRS.object_type_description
  , SRS.fqo
from @tbSearchResults as SRS
order by 1
       , 2

declare @SearchText nvarchar(max) = 'MuFunction(@SomeParam)'

, @IncludeSystemDbs as tinyint = 0 --1 to include

declare @tbSearchResults table (

fqo nvarchar(384)

, object_type_description nvarchar(max)

)

declare @SearchSql nvarchar(max) = formatmessage(

use ?

select

concat_ws(''.'', quotename(''?''), quotename(object_schema_name(ASM.object_id)), quotename(object_name(ASM.object_id))),

OBJ.type_desc

from

sys.all_sql_modules as ASM

inner join

sys.objects OBJ on ASM.object_id = OBJ.object_id

where

ASM.definition like ''%%%s%%''

and exists (

select

1/0

from

sys.databases as DTB

where

(

DTB.name = ''?''

and (

is_distributor = 0

or ''?'' not in (''master'',''tempdb'',''model'', ''msdb'')

or %d = 1

)

', @SearchText, @IncludeSystemDbs)

insert into @tbSearchResults

exec sys.sp_MSforeachdb @command1 = @SearchSql

select

SRS.object_type_description

, SRS.fqo

from @tbSearchResults as SRS

order by 1

, 2

Do you know how to fix the NFC and wireless charging not working on my Google Pixel4?

Post author By Kristof
Post date 2022-03-02

Recently, the wireless charging on my Google Pixel4 stopped working. I blamed the wireless charging pad but it wasn’t until I travelled back to Europe and wanted to use my wireless payments that I noticed the NFC was also not working.

It now turns out it’s due to the back of the phone being slightly detached from the rest of the phone. It’s hardly noticeable, especially since I always keep it in a silicone case but removing the phone from the case revealed the right side (when the phone screen is away from you) to have a slight gap that I can run my nail through. Pressing down on the back immediately fixed the problem and at least the wireless charging is working once again. I hope adjusting the case will hold the back down hard enough. Other reports have users opening the phone, bending the pins and then trying to glue the back to the phone … for now, things are working again by just pushing it back down. Otherwise I will try duct tape.

I need a dashboard for my SQL Agent JOBS that run SSIS Packages

Post author By Kristof
Post date 2021-09-22

I hear you. Here is a script in T-SQL that will fetch all the jobs (including the ones that are currently running) and displays a dashboard for each step. It will also display the last message SSIS generated (last error message in case of failure, or last message in case of a successful run).

WITH
	LastSessionId
AS
	(
		SELECT
			session_id	= MAX(session_id), job_id
		FROM
			msdb.dbo.sysjobactivity
		GROUP BY
			job_id
	),
	LastSessionInformation
AS
	(
		SELECT
			sja.session_id,
			sja.job_id,
			sja.job_history_id,
			sja.start_execution_date,
			sja.last_executed_step_date,
			sja.stop_execution_date,
			sja.next_scheduled_run_date
		FROM
			msdb.dbo.sysjobactivity AS sja
			INNER JOIN
			LastSessionId AS lsi
			ON sja.job_id = lsi.job_id
			AND sja.session_id = lsi.session_id
	),
	SsisExecution
AS
	(
		SELECT
			exe.folder_name,
			exe.project_name,
			exe.package_name,
			exe.process_id,
			opr.status,
			opr.operation_id,
			exe.start_time,
			exe.execution_id
		FROM
			SSISDB.catalog.operations AS opr
			INNER JOIN
			SSISDB.catalog.executions AS exe
			ON opr.process_id = exe.process_id
			AND opr.status IN
					(2, 4, 5, 6, 7) -- 2 running, failed, pending, ended unexpectedly, succeeded
		WHERE
			opr.object_type = 20	-- package
	),
	LatestMessage
AS
	(
		SELECT
			last_message_id = MAX(msg.event_message_id), msg.message_type, msg.operation_id
		FROM
			SSISDB.catalog.event_messages AS msg
		GROUP BY
			msg.operation_id, msg.message_type
	)
SELECT
	job.job_id,
	sjh.instance_id,
	job = job.name,
	lsi.start_execution_date,
	last_executed_date = COALESCE(lsi.stop_execution_date, lsi.last_executed_step_date),
	lsi.next_scheduled_run_date,
	sjh.step_id,
	sjh.step_name,
	sjh.run_status,
	IIF(lsi.start_execution_date IS NOT NULL, COALESCE(sjh.message, '>>> RUNNING'), NULL),
	package = exe.folder_name + '\' + exe.project_name + '\' + exe.package_name,
	msg.message,
	msg.message_source_name,
	msg.execution_path,
	msg.package_path,
	details = 'SELECT * FROM SSISDB.catalog.event_messages WHERE operation_id = '
			+ CAST(exe.operation_id AS VARCHAR(50)) + ' ORDER BY message_time DESC'
FROM
	msdb.dbo.sysjobs AS job
	INNER JOIN
	LastSessionInformation AS lsi
	ON lsi.job_id = job.job_id
	LEFT JOIN
	msdb.dbo.sysjobhistory AS sjh
	ON sjh.job_id = job.job_id
	AND (
			(	lsi.job_history_id >= sjh.instance_id
			AND sjh.run_date >= CAST(FORMAT(lsi.start_execution_date, 'yyyyMMdd') AS INT)
			AND sjh.run_time >= CAST(FORMAT(lsi.start_execution_date, 'hhmmss') AS INT)
			)
		OR	(	lsi.job_history_id IS NULL
			AND	sjh.run_date >= CAST(FORMAT(lsi.start_execution_date, 'yyyyMMdd') AS INT)
			AND	sjh.run_time >= CAST(FORMAT(lsi.start_execution_date, 'hhmmss') AS INT)
			)
		)
	LEFT JOIN
	msdb.dbo.sysjobsteps AS sjs
	ON sjs.job_id = job.job_id
	AND sjs.step_id = sjh.step_id
	OUTER APPLY
	(
		SELECT
			ExecutionId = SUBSTRING(
									sjh.message,
									CHARINDEX('Execution ID: ', sjh.message) + 14,
									CHARINDEX('.', sjh.message, CHARINDEX('Execution ID: ', sjh.message) + 14)
									- CHARINDEX('Execution ID: ', sjh.message) - 14
								)
		WHERE
			sjs.subsystem = 'ssis'
		AND CHARINDEX('Execution ID: ', sjh.message) > 0
		AND CHARINDEX('Failed', sjh.message) = 0
		UNION ALL
		SELECT
			ExecutionId = SUBSTRING(
									sjh.message,
									CHARINDEX('Execution ID: ', sjh.message) + 14,
									CHARINDEX(',', sjh.message, CHARINDEX('Execution ID: ', sjh.message) + 14)
									- CHARINDEX('Execution ID: ', sjh.message) - 14
								)
		WHERE
			sjs.subsystem = 'ssis'
		AND CHARINDEX('Execution ID: ', sjh.message) > 0
		AND CHARINDEX('Failed', sjh.message) > 0
	) AS eii
	LEFT JOIN
	SsisExecution AS exe
	ON sjs.subsystem = 'SSIS'
	AND exe.execution_id = CAST(eii.ExecutionId AS INT)
	LEFT JOIN
	LatestMessage AS lem
	ON lem.operation_id = exe.operation_id
	AND lem.message_type = 120
	LEFT JOIN
	LatestMessage AS lmg
	ON lmg.operation_id = exe.operation_id
	AND lmg.message_type = 40
	LEFT JOIN
	SSISDB.catalog.event_messages AS msg
	ON msg.event_message_id = COALESCE(lem.last_message_id, lmg.last_message_id)
WHERE
	job.name LIKE 'hds%'
ORDER BY
	lsi.start_execution_date DESC, job.name, instance_id DESC

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

WITH

LastSessionId

(

SELECT

session_id = MAX(session_id), job_id

FROM

msdb.dbo.sysjobactivity

GROUP BY

job_id

LastSessionInformation

(

SELECT

sja.session_id,

sja.job_id,

sja.job_history_id,

sja.start_execution_date,

sja.last_executed_step_date,

sja.stop_execution_date,

sja.next_scheduled_run_date

FROM

msdb.dbo.sysjobactivity AS sja

INNER JOIN

LastSessionId AS lsi

ON sja.job_id = lsi.job_id

AND sja.session_id = lsi.session_id

SsisExecution

(

SELECT

exe.folder_name,

exe.project_name,

exe.package_name,

exe.process_id,

opr.status,

opr.operation_id,

exe.start_time,

exe.execution_id

FROM

SSISDB.catalog.operations AS opr

INNER JOIN

SSISDB.catalog.executions AS exe

ON opr.process_id = exe.process_id

AND opr.status IN

(2, 4, 5, 6, 7) -- 2 running, failed, pending, ended unexpectedly, succeeded

WHERE

opr.object_type = 20 -- package

LatestMessage

(

SELECT

last_message_id = MAX(msg.event_message_id), msg.message_type, msg.operation_id

FROM

SSISDB.catalog.event_messages AS msg

GROUP BY

msg.operation_id, msg.message_type

)

SELECT

job.job_id,

sjh.instance_id,

job = job.name,

lsi.start_execution_date,

last_executed_date = COALESCE(lsi.stop_execution_date, lsi.last_executed_step_date),

lsi.next_scheduled_run_date,

sjh.step_id,

sjh.step_name,

sjh.run_status,

IIF(lsi.start_execution_date IS NOT NULL, COALESCE(sjh.message, '>>> RUNNING'), NULL),

package = exe.folder_name + '\' + exe.project_name + '\' + exe.package_name,

msg.message,

msg.message_source_name,

msg.execution_path,

msg.package_path,

details = 'SELECT * FROM SSISDB.catalog.event_messages WHERE operation_id = '

+ CAST(exe.operation_id AS VARCHAR(50)) + ' ORDER BY message_time DESC'

FROM

msdb.dbo.sysjobs AS job

INNER JOIN

LastSessionInformation AS lsi

ON lsi.job_id = job.job_id

LEFT JOIN

msdb.dbo.sysjobhistory AS sjh

ON sjh.job_id = job.job_id

AND (

( lsi.job_history_id >= sjh.instance_id

AND sjh.run_date >= CAST(FORMAT(lsi.start_execution_date, 'yyyyMMdd') AS INT)

AND sjh.run_time >= CAST(FORMAT(lsi.start_execution_date, 'hhmmss') AS INT)

)

OR ( lsi.job_history_id IS NULL

AND sjh.run_date >= CAST(FORMAT(lsi.start_execution_date, 'yyyyMMdd') AS INT)

AND sjh.run_time >= CAST(FORMAT(lsi.start_execution_date, 'hhmmss') AS INT)

)

LEFT JOIN

msdb.dbo.sysjobsteps AS sjs

ON sjs.job_id = job.job_id

AND sjs.step_id = sjh.step_id

OUTER APPLY

(

SELECT

ExecutionId = SUBSTRING(

sjh.message,

CHARINDEX('Execution ID: ', sjh.message) + 14,

CHARINDEX('.', sjh.message, CHARINDEX('Execution ID: ', sjh.message) + 14)

- CHARINDEX('Execution ID: ', sjh.message) - 14

)

WHERE

sjs.subsystem = 'ssis'

AND CHARINDEX('Execution ID: ', sjh.message) > 0

AND CHARINDEX('Failed', sjh.message) = 0

UNION ALL

SELECT

ExecutionId = SUBSTRING(

sjh.message,

CHARINDEX('Execution ID: ', sjh.message) + 14,

CHARINDEX(',', sjh.message, CHARINDEX('Execution ID: ', sjh.message) + 14)

- CHARINDEX('Execution ID: ', sjh.message) - 14

)

WHERE

sjs.subsystem = 'ssis'

AND CHARINDEX('Execution ID: ', sjh.message) > 0

AND CHARINDEX('Failed', sjh.message) > 0

) AS eii

LEFT JOIN

SsisExecution AS exe

ON sjs.subsystem = 'SSIS'

AND exe.execution_id = CAST(eii.ExecutionId AS INT)

LEFT JOIN

LatestMessage AS lem

ON lem.operation_id = exe.operation_id

AND lem.message_type = 120

LEFT JOIN

LatestMessage AS lmg

ON lmg.operation_id = exe.operation_id

AND lmg.message_type = 40

LEFT JOIN

SSISDB.catalog.event_messages AS msg

ON msg.event_message_id = COALESCE(lem.last_message_id, lmg.last_message_id)

WHERE

job.name LIKE 'hds%'

ORDER BY

lsi.start_execution_date DESC, job.name, instance_id DESC

How do I solve “illegal characters in path” in my Octopus deploy?

Post author By Kristof
Post date 2021-09-08

You won’t believe this, but the reason why I got this was a weird trailing backslash. I failed to find where it came from and after HOURS of searching, I found out it was because I had entered a variable in the process step and accidentally type ENTER before the variable. So it was essentially a CRLF followed by the variable name. For some reason, that caused the illegal character. Guess it was trying to insert the CRLF in the path … Well, check your variable listings in your steps to see if you have any leading or trailing enters …

I need a way to view the results of my SSIS packages (as a recordset)

Post author By Kristof
Post date 2021-09-03

This code was written while we were migrating a bunch of SSIS packages to a new SQL Server version. It will show you the latest results for each package run. If a package ended in an error state, it will show the latest error that was generated.

You can toggle the code to show only packages in error state, and you can toggle the running packages on/off. Running packages can be included because otherwise, running packages do not show up in this list.

DECLARE
	@ShowOnlyErrors BIT = 0,	-- Show errors or also completed packages
	@ShowRunning	BIT = 1		-- Show packages that are currently running to track progress

;
WITH
	LatestExecution
AS
	(
		SELECT
			exe.folder_name,
			exe.project_name,
			exe.package_name,
			exe.process_id,
			opr.status,
			opr.operation_id,
			opr.start_time,
			opr.end_time,

			sort_order = ROW_NUMBER() OVER (PARTITION BY
												exe.folder_name, exe.project_name, exe.package_name
											ORDER BY
												exe.start_time DESC,
												opr.operation_id desc
										)
		FROM
			SSISDB.CATALOG.OPERATIONS AS opr
			INNER JOIN
			SSISDB.CATALOG.executions AS exe
			ON opr.process_id = exe.process_id
			AND	opr.object_id = exe.object_id
			AND opr.STATUS IN
					(2, 4, 5, 6, 7) -- 2 running, failed, pending, ended unexpectedly, succeeded
		WHERE
			opr.object_type = 20 -- package
		AND opr.operation_type = 200	-- Package start
		AND	(	opr.status <> 2
			OR	@ShowRunning = 1)
	),
	LatestMessage
AS
	(
		SELECT
			msg.operation_id,
			msg.message,
			msg.execution_path,
			msg.package_path,
			msg.message_time,
			msg.message_type,
			lex.start_time,
			lex.end_time,
			lex.process_id,
			lex.status,
			lex.folder_name,
			lex.project_name,
			lex.package_name,
			sort_order = ROW_NUMBER() OVER (PARTITION BY
												msg.operation_id
											ORDER BY
												msg.message_type DESC, msg.message_time DESC
										)
		FROM
			SSISDB.catalog.event_messages AS msg
			INNER JOIN
			LatestExecution AS lex
			ON lex.operation_id = msg.operation_id
			AND lex.status IN
					(2, 4, 5, 6, 7) -- 2 running, failed, pending, ended unexpectedly, succeeded
			AND lex.sort_order = 1
		WHERE
			msg.message_type = 120
		OR
			(
				msg.message_type IN (30, 40)
		AND		msg.package_path = '\package'
		AND		msg.event_name = 'OnPostExecute'
		AND		@ShowOnlyErrors = 0
			)
	)
SELECT
	DISTINCT
	lms.folder_name,
	lms.project_name,
	lms.package_name,
	status = IIF(lms.status = 2, 'RUNNING', IIF(lms.message_type = 120, 'ERROR', 'SUCCESS')),
	lms.message,
	lms.operation_id,
	lms.execution_path,
	lms.package_path,
	lms.start_time,
	lms.message_time,
	runtime = CAST(DATEDIFF(DAY, lms.message_time , lms.start_time) AS VARCHAR(5)) + ' ' + FORMAT(CAST(lms.message_time AS DATETIME) - CAST(lms.start_time AS DATETIME), 'HH:mm:ss')
FROM
	LatestMessage AS lms
WHERE
	lms.sort_order = 1
ORDER BY
	lms.start_time DESC, lms.message_time;

DECLARE

@ShowOnlyErrors BIT = 0, -- Show errors or also completed packages

@ShowRunning BIT = 1 -- Show packages that are currently running to track progress

;

WITH

LatestExecution

(

SELECT

exe.folder_name,

exe.project_name,

exe.package_name,

exe.process_id,

opr.status,

opr.operation_id,

opr.start_time,

opr.end_time,

sort_order = ROW_NUMBER() OVER (PARTITION BY

exe.folder_name, exe.project_name, exe.package_name

ORDER BY

exe.start_time DESC,

opr.operation_id desc

)

FROM

SSISDB.CATALOG.OPERATIONS AS opr

INNER JOIN

SSISDB.CATALOG.executions AS exe

ON opr.process_id = exe.process_id

AND opr.object_id = exe.object_id

AND opr.STATUS IN

(2, 4, 5, 6, 7) -- 2 running, failed, pending, ended unexpectedly, succeeded

WHERE

opr.object_type = 20 -- package

AND opr.operation_type = 200 -- Package start

AND ( opr.status <> 2

OR @ShowRunning = 1)

LatestMessage

(

SELECT

msg.operation_id,

msg.message,

msg.execution_path,

msg.package_path,

msg.message_time,

msg.message_type,

lex.start_time,

lex.end_time,

lex.process_id,

lex.status,

lex.folder_name,

lex.project_name,

lex.package_name,

sort_order = ROW_NUMBER() OVER (PARTITION BY

msg.operation_id

ORDER BY

msg.message_type DESC, msg.message_time DESC

)

FROM

SSISDB.catalog.event_messages AS msg

INNER JOIN

LatestExecution AS lex

ON lex.operation_id = msg.operation_id

AND lex.status IN

(2, 4, 5, 6, 7) -- 2 running, failed, pending, ended unexpectedly, succeeded

AND lex.sort_order = 1

WHERE

msg.message_type = 120

(

msg.message_type IN (30, 40)

AND msg.package_path = '\package'

AND msg.event_name = 'OnPostExecute'

AND @ShowOnlyErrors = 0

)

SELECT

DISTINCT

lms.folder_name,

lms.project_name,

lms.package_name,

status = IIF(lms.status = 2, 'RUNNING', IIF(lms.message_type = 120, 'ERROR', 'SUCCESS')),

lms.message,

lms.operation_id,

lms.execution_path,

lms.package_path,

lms.start_time,

lms.message_time,

runtime = CAST(DATEDIFF(DAY, lms.message_time , lms.start_time) AS VARCHAR(5)) + ' ' + FORMAT(CAST(lms.message_time AS DATETIME) - CAST(lms.start_time AS DATETIME), 'HH:mm:ss')

FROM

LatestMessage AS lms

WHERE

lms.sort_order = 1

ORDER BY

lms.start_time DESC, lms.message_time;