A Better Kitchen Sink Design Pattern?

Aaron Bertrand’s Kitchen Sink Design Pattern article recently referenced by Brent Ozar deals with the issue of having a single query where some of the predicates may or may not be provided. Below is an alternative. However, there are a couple of important caveats listed at the bottom of this article. Brent’s example from #5:

WHERE ([Column] = @Column or @Column IS NULL)

A better way?

WHERE [Column] LIKE CASE WHEN @Column IS NULL THEN '%' ELSE @Column END

Using the AdventureWorks2012 database:

declare @lastName nvarchar(50)
set @lastName = 'Smith'

select p.[PersonType], p.[LastName] 
from [AdventureWorks2012].Person.Person as p
where p.[PersonType] = 'IN'
and (p.LastName = @lastName or @lastName is null)

select p.[PersonType], p.[LastName] 
from [AdventureWorks2012].Person.Person as p
where p.[PersonType] = 'IN'
and p.LastName like case when @lastName is null then '%' else @lastName end


Notice the logical reads and query plan costs:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Person'. Scan count 1, logical reads 3820, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Person'. Scan count 1, logical reads 328, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.



Let’s try adding that index and run it again:

CREATE NONCLUSTERED INDEX [IX_missing_index]
ON [AdventureWorks2012].[Person].[Person] ([PersonType],[LastName])


SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Person'. Scan count 1, logical reads 118, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Person'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.



Since the above only works with char columns (type conversion issue in CASE statement), you can use one of the below patterns using the min/max range of the data type as they produced the same plan.

declare @i int;
set @i = 2

select p.FirstName, p.LastName from person.Person as p
where p.LastName in ('Diaz','Hernandez')
and p.[EmailPromotion] >= case when @i is not null then @i else -2147483648 end 
and p.[EmailPromotion] <= case when @i is not null then @i else 2147483648 end 

select p.FirstName, p.LastName from person.Person as p
where p.LastName in ('Diaz','Hernandez')
and p.[EmailPromotion] between 
   case when @i is not null then @i else -2147483648 end and
   case when @i is not null then @i else 2147483648 end


Now for the caveat: Operators will never evaluate to true with NULL values. If your column definition allows nulls, don’t use this. And if you value plan reuse, I defer to Aaron’s link above.


Afterthought: Perhaps one day a predicate can be optional and dropped by the query parser before going to the optimizer. Ex: p.[EmailPromotion] predicate would be dropped before a plan is generated:

where p.LastName in ('Diaz','Hernandez')
and if (@i is not null) p.[EmailPromotion] = @i

Handling Errors and Return Codes in a PowerShell Job Step

PowerShell job steps offer a lot of advantages, but when things don’t work as expected, it can frustrating to understand why. Things like when a non-zero exit code reports the step as successful. Some important points I found with PowerShell steps (especially the first item):

  • Only STDOUT or STDERR will be added to the job step log, not both. If there is any STDERR, no STDOUT will be returned
  • Write-Host does not work, must use Write-Output (STDOUT) or Write-Error (STDERR)
  • $ErrorActionPreference setting and -ErrorAction parameter are key to whether the script terminates or adds the error to the log
  • If you try to exit with [Environment]::Exit(1) nothing is returned
  • Use throw to terminate the script and add the error to the log – just not as clean as $ErrorActionPreference = ‘Stop’ combined with Write-Error
  • STDERROR does not use newlines, and appears to trim blank lines, so add a period then newline to help format output
  • SilentlyContinue will not be logged, so you can use it to write your own messages with Write-Error
  • Continue will be logged and is unformatted
  • You will need to enable saving step output to see script results

I created a framework for capturing the output, return code, and run time. It also manages logging to files and limiting how long the process is allowed to run.

The below would be the entire PowerShell job step. It simply sleeps for 10 seconds and returns a non-zero exit code. To use in your code, replace everything after -Command with your command, and be sure it returns an accurate exit code.

$JobName = 'test'
$psarg = @();
$psarg += "-Noninteractive -Command Start-Sleep 10; exit 1"

$ldir = "C:\temp\LOGS\$JobName\"
$waitTimeSeconds = 120
$killWaitTimeSeconds = 30

$logMaxDays = 180
$lfiles = $ldir + '*.log'
mkdir -path $ldir -erroraction silentlycontinue
$dt = get-date -Format yyyymmdd_HHmm
$fout = $ldir + $JobName.split(".")[0] + "." + $dt + ".stdout.log"
$ferr = $ldir + $JobName.split(".")[0] + "." + $dt + ".stderr.log"

# you must have ErrorActionPreference as Continue for the write-error output to appear
$ErrorActionPreference = 'Continue' 

$process = start-process -WorkingDirectory "P:\scripts\vcenter" -RSO $fout -RSE $ferr powershell.exe -ArgumentList $psarg -passthru


<#############   MAKE NO CHANGES BELOW HERE   #################>

<#
	Wait-Process -TimeOut parameter is not Int32 !!!  Max value is 32767   !!!
	Need to loop if the timeout value exceeds this limitation
	
	If the modulus is zero then the remainder needs to be set to iteration since the last wait is not run in the loop
#>
$maxWaitTimeIteration = 32767
$waitTimeIterations = $waitTimeSeconds / $maxWaitTimeIteration
$waitTimeRemainder = $waitTimeSeconds % $maxWaitTimeIteration
if ($waitTimeRemainder -eq 0) { $waitTimeRemainder = $maxWaitTimeIteration; } # reset the remainder when modulus is zero
while ($waitTimeIterations -gt 1) {
	# we do not care about these waits as are only run when the $waitTimeSeconds > $maxWaitTimeIteration
	$process | Wait-Process -Timeout $maxWaitTimeIteration -ErrorAction SilentlyContinue 
	$waitTimeIterations = $waitTimeIterations - 1
}
$process | Wait-Process -Timeout $waitTimeRemainder -ErrorVariable wpError -ErrorAction SilentlyContinue 

if ($wpError) {
	"`n`n----------`n *** Attempting to terminate Process ID {0} because it did not complete within allotted time of {1} seconds ***`n----------`n" -f $process.Id, $waitTimeSeconds | write-error 
	
	if (-not $noKillDebug) { Stop-Process -Id $process.Id -ErrorAction SilentlyContinue }
	Wait-Process -Id $process.Id -timeout $killWaitTimeSeconds -ErrorAction SilentlyContinue
	
	if ($process.ExitTime -eq $null) {
		$timeoutMsg = ".`n`nProcess did not terminate after {0} seconds and appears to be hung.`n`n******  PLEASE KILL PROCESS ID [ {1} ]  ********`n" -f $killWaitTimeSeconds, $process.Id
		$resultMsg = "`n`nRESULT:  SCRIPT IS HUNG AND PROCESS NEEDS TO BE TERMINATED"
	} else {
		$timeoutMsg = ".`n`nProcess {0} has been terminated.  Please check script logs.`n" -f $process.Id
		$resultMsg = "`n`nRESULT:  SCRIPT WAS TERMINATED DUE TO TIMEOUT"
	}
	
} # if ($wpError) 


$procStats = @'
.

CPU Time (seconds)  : [ {0} ]
CPU Time (ticks)    : [ {1} ]
PeakWorkingSet64    : [ {2} ]
StartTime           : [ {3} ]
ExitTime            : [ {4} ]
Exit Code           : [ {5} ]

'@ -f $process.CPU, $process.TotalProcessorTime.Ticks, $process.PeakWorkingSet64, $process.StartTime, $process.ExitTime, $process.ExitCode



if ($process.ExitCode -eq 0) {
	
	$procStats | write-output
	
	$DatetoDelete = (Get-Date).AddDays([math]::Abs($logMaxDays) * -1)
	"`nLooking for log files older than {0} in: {1}" -f $DatetoDelete, $ldir | write-output
	
	$oldLogFiles = Get-ChildItem $lfiles | Where-Object { $_.LastWriteTime -lt $DatetoDelete }
	if ($oldLogFiles.Count -ge 1) {
		'`nDeleting {0} log files' -f $oldLogFiles.Count | write-output
		$oldLogFiles | Remove-Item
	} else {
		write-output "`nNo log files to delete."
	}
	"`nRESULT:  Script completed successfully." | write-output
	
} else {
	# Either script failed or it hung/timed-out	
	
	$ErrorActionPreference = 'Stop'  # a write-error will exit the script immediately (and cleanly)
	if ($timeoutMsg) {
		$procStats + $timeoutMsg + $resultMsg | Write-Error
		# throw $throwMsg
	} else {
		$procStats + "`n`nRESULT:  SCRIPT RETURNED A FAILURE CODE.  Please check the script log files.`n" | write-error
		#throw 'SCRIPT RETURNED A FAILURE CODE'
	}
	
} # if ($process.ExitCode -eq 0)


The sample job below will report the following with various exit codes, timeouts, etc.


SCRIPT COMPLETED SUCCESSFULLY

Message
.

CPU Time (seconds)  : [ 0.359375 ]
CPU Time (ticks)    : [ 3593750 ]
PeakWorkingSet64    : [ 1167360 ]
StartTime           : [ 3/25/2021 10:50:13 AM ]
ExitTime            : [ 3/25/2021 10:50:19 AM ]
Exit Code           : [ 0 ]


Looking for log files older than 9/26/2020 10:50:19 AM in: C:\temp\LOGS\test\

No log files to delete.

RESULT:  Script completed successfully.

SCRIPT EXITED WITH ERROR CODE

Message
A job step received an error at line 0 in a PowerShell script. The corresponding line is ''. Correct the script and reschedule the job. The error information returned by PowerShell is: '.

CPU Time (seconds)  : [ 0.328125 ]
CPU Time (ticks)    : [ 3281250 ]
PeakWorkingSet64    : [ 1187840 ]
StartTime           : [ 3/25/2021 10:50:06 AM ]
ExitTime            : [ 3/25/2021 10:50:12 AM ]
Exit Code           : [ 1 ]


RESULT:  SCRIPT RETURNED A FAILURE CODE.  Please check the script log files.

TIMEOUT / TERMINATED

Message
The job script encountered the following errors. These errors did not stop the script:
A job step received an error at line 0 in a PowerShell script. The corresponding line is ''. Correct the script and reschedule the job. The error information returned by PowerShell is: '

----------
*** Attempting to terminate Process ID 30316 because it did not complete within allotted time of 11 seconds ***
----------

'
A job step received an error at line 0 in a PowerShell script. The corresponding line is ''. Correct the script and reschedule the job. The error information returned by PowerShell is: '.

CPU Time (seconds)  : [ 0.359375 ]
CPU Time (ticks)    : [ 3593750 ]
PeakWorkingSet64    : [ 54132736 ]
StartTime           : [ 3/25/2021 10:49:37 AM ]
ExitTime            : [ 3/25/2021 10:49:48 AM ]
Exit Code           : [ -1 ]
.

Process 30316 has been terminated.  Please check script logs.


RESULT:  SCRIPT WAS TERMINATED DUE TO TIMEOUT
'

TIMEOUT / HUNG PROCESS

Message
The job script encountered the following errors. These errors did not stop the script:
A job step received an error at line 0 in a PowerShell script. The corresponding line is ''. Correct the script and reschedule the job. The error information returned by PowerShell is: '

----------
*** Attempting to terminate Process ID 36212 because it did not complete within allotted time of 11 seconds ***
----------

'
A job step received an error at line 0 in a PowerShell script. The corresponding line is ''. Correct the script and reschedule the job. The error information returned by PowerShell is: '.

CPU Time (seconds)  : [ 0.375 ]
CPU Time (ticks)    : [ 3750000 ]
PeakWorkingSet64    : [ 54276096 ]
StartTime           : [ 3/25/2021 10:49:50 AM ]
ExitTime            : [  ]
Exit Code           : [  ]
.

Process did not terminate after 4 seconds and appears to be hung.

******  PLEASE KILL PROCESS ID [ 36212 ]  ********


RESULT:  SCRIPT IS HUNG AND PROCESS NEEDS TO BE TERMINATED
'

The sample job further below will have 4 tasks, each with a different outcome for testing the framework. Execute the script below, run the job, and then view the job output:

/*
	Sample job testing the four possible outcomes with a PowerShell job step

	1) Success
	2) Script finishes but with non-zero exit code
	3) Script exceeds runtime and is terminated
	4) Script exceeds runtime and attempt to kill the job fail

	2021-03-25	ronthepolymath.wordpress.com

*/


USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'z PowerShell Error HandlingTest', 
		@enabled=0, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Script Timeout - Process Terminated', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'<#
	PowerShell Agent Job Step Error Handling

	IMPORTANT THINGS TO KNOW:

		* Write-Host does not work, must use Write-Output (STDOUT) or Write-Error (STDERR)
		* Only STDOUT _or_ STDERR will be added to the job step log, not both.  If there is any STDERR, no STDOUT will be returned
		* $ErrorActionPreference setting and -ErrorAction parameter are key to whether the script terminates or adds the error to the log
		* If you try to exit with [Environment]::Exit(1) nothing is returned
		* Use throw to terminate the script and add the error to the log
		* STDERROR does not use newlines, and appears to trim blank lines, so add a period then newline to help format output
		* SilentlyContinue will not be logged, so you can use it to write your own messages with Write-Error
		* Continue will be logged and is unformatted

	2021-03-25	ronthepolymath.wordpress.com

#>

$JobName = ''test''
$psarg = @();

if (1 -eq 0) {
	# script completes in time and is successful
	$psarg += "-Noninteractive -Command Start-Sleep 5; exit 0"
	$waitTimeSeconds = 30 #60 * 60 * 24
	$maxWaitTimeIteration = 32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 300
}

if (1 -eq 0) {
	# script completes in time but has exit code
	$psarg += "-Noninteractive -Command Start-Sleep 5; exit 1"
	$waitTimeSeconds = 30 #60 * 60 * 24
	$maxWaitTimeIteration = 32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 300
}

if (1 -eq 1) {
	# script runs out of time and is terminated
	$psarg += "-Noninteractive -Command Start-Sleep 25; exit 1"
	$waitTimeSeconds = 11 # 30 #60 * 60 * 24
	$maxWaitTimeIteration = 3 # 32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 300
}

if (1 -eq 0) {
	# script runs out of time but fails to terminate the process
	$psarg += "-Noninteractive -Command Start-Sleep 55; exit 0"
	$waitTimeSeconds = 11 #60 * 60 * 24
	$maxWaitTimeIteration = 3 #32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 4 # 300
	$noKillDebug = $true
}

$ldir = "C:\temp\LOGS\$JobName\"
$logMaxDays = 180
$lfiles = $ldir + ''*.log''
mkdir -path $ldir -erroraction silentlycontinue
$dt = get-date -Format yyyymmdd_HHmm
$fout = $ldir + $JobName.split(".")[0] + "." + $dt + ".stdout.log"
$ferr = $ldir + $JobName.split(".")[0] + "." + $dt + ".stderr.log"

$ErrorActionPreference = ''Continue'' # you must have this as Continue for the write-error output to appear

$process = start-process -WorkingDirectory "C:\temp" -RSO $fout -RSE $ferr powershell.exe -ArgumentList $psarg -passthru  # worked

<#############   MAKE NO CHANGES BELOW HERE   #################>

<#
	Wait-Process -TimeOut parameter is not Int32 !!!  Max value is 32767   !!!
	Need to loop if the timeout value exceeds this limitation
	
	If the modulus is zero then the remainder needs to be set to iteration since the last wait is not run in the loop
#>
$waitTimeIterations = $waitTimeSeconds / $maxWaitTimeIteration
$waitTimeRemainder = $waitTimeSeconds % $maxWaitTimeIteration
if ($waitTimeRemainder -eq 0) { $waitTimeRemainder = $maxWaitTimeIteration; } # reset the remainder when modulus is zero
while ($waitTimeIterations -gt 1) {
	# we do not care about these waits as are only run when the $waitTimeSeconds > $maxWaitTimeIteration
	$process | Wait-Process -Timeout $maxWaitTimeIteration -ErrorAction SilentlyContinue # do not include the ErrorVariable here
	$waitTimeIterations = $waitTimeIterations - 1
}
$process | Wait-Process -Timeout $waitTimeRemainder -ErrorVariable wpError -ErrorAction SilentlyContinue 

if ($wpError) {
	"`n`n----------`n *** Attempting to terminate Process ID {0} because it did not complete within allotted time of {1} seconds ***`n----------`n" -f $process.Id, $waitTimeSeconds | write-error 
	
	if (-not $noKillDebug) { Stop-Process -Id $process.Id -ErrorAction SilentlyContinue }
	Wait-Process -Id $process.Id -timeout $killWaitTimeSeconds -ErrorAction SilentlyContinue
	
	if ($process.ExitTime -eq $null) {
		$timeoutMsg = ".`n`nProcess did not terminate after {0} seconds and appears to be hung.`n`n******  PLEASE KILL PROCESS ID [ {1} ]  ********`n" -f $killWaitTimeSeconds, $process.Id
		$resultMsg = "`n`nRESULT:  SCRIPT IS HUNG AND PROCESS NEEDS TO BE TERMINATED"
	} else {
		$timeoutMsg = ".`n`nProcess {0} has been terminated.  Please check script logs.`n" -f $process.Id
		$resultMsg = "`n`nRESULT:  SCRIPT WAS TERMINATED DUE TO TIMEOUT"
	}
	
} # if ($wpError) 


$procStats = @''
.

CPU Time (seconds)  : [ {0} ]
CPU Time (ticks)    : [ {1} ]
PeakWorkingSet64    : [ {2} ]
StartTime           : [ {3} ]
ExitTime            : [ {4} ]
Exit Code           : [ {5} ]

''@ -f $process.CPU, $process.TotalProcessorTime.Ticks, $process.PeakWorkingSet64, $process.StartTime, $process.ExitTime, $process.ExitCode



if ($process.ExitCode -eq 0) {
	
	$procStats | write-output
	
	$DatetoDelete = (Get-Date).AddDays([math]::Abs($logMaxDays) * -1)
	"`nLooking for log files older than {0} in: {1}" -f $DatetoDelete, $ldir | write-output
	
	$oldLogFiles = Get-ChildItem $lfiles | Where-Object { $_.LastWriteTime -lt $DatetoDelete }
	if ($oldLogFiles.Count -ge 1) {
		''`nDeleting {0} log files'' -f $oldLogFiles.Count | write-output
		$oldLogFiles | Remove-Item
	} else {
		write-output "`nNo log files to delete."
	}
	"`nRESULT:  Script completed successfully." | write-output
	
} else {
	# Either script failed or it hung/timed-out	
	
	$ErrorActionPreference = ''Stop''  # a write-error will exit the script immediately (and cleanly)
	if ($timeoutMsg) {
		$procStats + $timeoutMsg + $resultMsg | Write-Error
		# throw $throwMsg
	} else {
		$procStats + "`n`nRESULT:  SCRIPT RETURNED A FAILURE CODE.  Please check the script log files.`n" | write-error
		#throw ''SCRIPT RETURNED A FAILURE CODE''
	}
	
} # if ($process.ExitCode -eq 0)


', 
		@database_name=N'master', 
		@flags=40
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Script Timeout - Process Hung', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'<#
	PowerShell Agent Job Step Error Handling

	IMPORTANT THINGS TO KNOW:

		* Write-Host does not work, must use Write-Output (STDOUT) or Write-Error (STDERR)
		* Only STDOUT _or_ STDERR will be added to the job step log, not both.  If there is any STDERR, no STDOUT will be returned
		* $ErrorActionPreference setting and -ErrorAction parameter are key to whether the script terminates or adds the error to the log
		* If you try to exit with [Environment]::Exit(1) nothing is returned
		* Use throw to terminate the script and add the error to the log
		* STDERROR does not use newlines, and appears to trim blank lines, so add a period then newline to help format output
		* SilentlyContinue will not be logged, so you can use it to write your own messages with Write-Error
		* Continue will be logged and is unformatted

	2021-03-25	ronthepolymath.wordpress.com

#>

$JobName = ''test''
$psarg = @();

if (1 -eq 0) {
	# script completes in time and is successful
	$psarg += "-Noninteractive -Command Start-Sleep 5; exit 0"
	$waitTimeSeconds = 30 #60 * 60 * 24
	$maxWaitTimeIteration = 32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 300
}

if (1 -eq 0) {
	# script completes in time but has exit code
	$psarg += "-Noninteractive -Command Start-Sleep 5; exit 1"
	$waitTimeSeconds = 30 #60 * 60 * 24
	$maxWaitTimeIteration = 32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 300
}

if (1 -eq 0) {
	# script runs out of time and is terminated
	$psarg += "-Noninteractive -Command Start-Sleep 25; exit 1"
	$waitTimeSeconds = 11 # 30 #60 * 60 * 24
	$maxWaitTimeIteration = 3 # 32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 300
}

if (1 -eq 1) {
	# script runs out of time but fails to terminate the process
	$psarg += "-Noninteractive -Command Start-Sleep 55; exit 0"
	$waitTimeSeconds = 11 #60 * 60 * 24
	$maxWaitTimeIteration = 3 #32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 4 # 300
	$noKillDebug = $true
}

$ldir = "C:\temp\LOGS\$JobName\"
$logMaxDays = 180
$lfiles = $ldir + ''*.log''
mkdir -path $ldir -erroraction silentlycontinue
$dt = get-date -Format yyyymmdd_HHmm
$fout = $ldir + $JobName.split(".")[0] + "." + $dt + ".stdout.log"
$ferr = $ldir + $JobName.split(".")[0] + "." + $dt + ".stderr.log"

$ErrorActionPreference = ''Continue'' # you must have this as Continue for the write-error output to appear

$process = start-process -WorkingDirectory "C:\temp" -RSO $fout -RSE $ferr powershell.exe -ArgumentList $psarg -passthru  # worked


<#############   MAKE NO CHANGES BELOW HERE   #################>

<#
	Wait-Process -TimeOut parameter is not Int32 !!!  Max value is 32767   !!!
	Need to loop if the timeout value exceeds this limitation
	
	If the modulus is zero then the remainder needs to be set to iteration since the last wait is not run in the loop
#>
$waitTimeIterations = $waitTimeSeconds / $maxWaitTimeIteration
$waitTimeRemainder = $waitTimeSeconds % $maxWaitTimeIteration
if ($waitTimeRemainder -eq 0) { $waitTimeRemainder = $maxWaitTimeIteration; } # reset the remainder when modulus is zero
while ($waitTimeIterations -gt 1) {
	# we do not care about these waits as are only run when the $waitTimeSeconds > $maxWaitTimeIteration
	$process | Wait-Process -Timeout $maxWaitTimeIteration -ErrorAction SilentlyContinue # do not include the ErrorVariable here
	$waitTimeIterations = $waitTimeIterations - 1
}
$process | Wait-Process -Timeout $waitTimeRemainder -ErrorVariable wpError -ErrorAction SilentlyContinue 

if ($wpError) {
	"`n`n----------`n *** Attempting to terminate Process ID {0} because it did not complete within allotted time of {1} seconds ***`n----------`n" -f $process.Id, $waitTimeSeconds | write-error 
	
	if (-not $noKillDebug) { Stop-Process -Id $process.Id -ErrorAction SilentlyContinue }
	Wait-Process -Id $process.Id -timeout $killWaitTimeSeconds -ErrorAction SilentlyContinue
	
	if ($process.ExitTime -eq $null) {
		$timeoutMsg = ".`n`nProcess did not terminate after {0} seconds and appears to be hung.`n`n******  PLEASE KILL PROCESS ID [ {1} ]  ********`n" -f $killWaitTimeSeconds, $process.Id
		$resultMsg = "`n`nRESULT:  SCRIPT IS HUNG AND PROCESS NEEDS TO BE TERMINATED"
	} else {
		$timeoutMsg = ".`n`nProcess {0} has been terminated.  Please check script logs.`n" -f $process.Id
		$resultMsg = "`n`nRESULT:  SCRIPT WAS TERMINATED DUE TO TIMEOUT"
	}
	
} # if ($wpError) 


$procStats = @''
.

CPU Time (seconds)  : [ {0} ]
CPU Time (ticks)    : [ {1} ]
PeakWorkingSet64    : [ {2} ]
StartTime           : [ {3} ]
ExitTime            : [ {4} ]
Exit Code           : [ {5} ]

''@ -f $process.CPU, $process.TotalProcessorTime.Ticks, $process.PeakWorkingSet64, $process.StartTime, $process.ExitTime, $process.ExitCode



if ($process.ExitCode -eq 0) {
	
	$procStats | write-output
	
	$DatetoDelete = (Get-Date).AddDays([math]::Abs($logMaxDays) * -1)
	"`nLooking for log files older than {0} in: {1}" -f $DatetoDelete, $ldir | write-output
	
	$oldLogFiles = Get-ChildItem $lfiles | Where-Object { $_.LastWriteTime -lt $DatetoDelete }
	if ($oldLogFiles.Count -ge 1) {
		''`nDeleting {0} log files'' -f $oldLogFiles.Count | write-output
		$oldLogFiles | Remove-Item
	} else {
		write-output "`nNo log files to delete."
	}
	"`nRESULT:  Script completed successfully." | write-output
	
} else {
	# Either script failed or it hung/timed-out	
	
	$ErrorActionPreference = ''Stop''  # a write-error will exit the script immediately (and cleanly)
	if ($timeoutMsg) {
		$procStats + $timeoutMsg + $resultMsg | Write-Error
		# throw $throwMsg
	} else {
		$procStats + "`n`nRESULT:  SCRIPT RETURNED A FAILURE CODE.  Please check the script log files.`n" | write-error
		#throw ''SCRIPT RETURNED A FAILURE CODE''
	}
	
} # if ($process.ExitCode -eq 0)


', 
		@database_name=N'master', 
		@flags=40
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Script Completes - Exit 1', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'<#
	PowerShell Agent Job Step Error Handling

	IMPORTANT THINGS TO KNOW:

		* Write-Host does not work, must use Write-Output (STDOUT) or Write-Error (STDERR)
		* Only STDOUT _or_ STDERR will be added to the job step log, not both.  If there is any STDERR, no STDOUT will be returned
		* $ErrorActionPreference setting and -ErrorAction parameter are key to whether the script terminates or adds the error to the log
		* If you try to exit with [Environment]::Exit(1) nothing is returned
		* Use throw to terminate the script and add the error to the log
		* STDERROR does not use newlines, and appears to trim blank lines, so add a period then newline to help format output
		* SilentlyContinue will not be logged, so you can use it to write your own messages with Write-Error
		* Continue will be logged and is unformatted

	2021-03-25	ronthepolymath.wordpress.com

#>

$JobName = ''test''
$psarg = @();

if (1 -eq 0) {
	# script completes in time and is successful
	$psarg += "-Noninteractive -Command Start-Sleep 5; exit 0"
	$waitTimeSeconds = 30 #60 * 60 * 24
	$maxWaitTimeIteration = 32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 300
}

if (1 -eq 1) {
	# script completes in time but has exit code
	$psarg += "-Noninteractive -Command Start-Sleep 5; exit 1"
	$waitTimeSeconds = 30 #60 * 60 * 24
	$maxWaitTimeIteration = 32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 300
}

if (1 -eq 0) {
	# script runs out of time and is terminated
	$psarg += "-Noninteractive -Command Start-Sleep 25; exit 1"
	$waitTimeSeconds = 11 # 30 #60 * 60 * 24
	$maxWaitTimeIteration = 3 # 32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 300
}

if (1 -eq 0) {
	# script runs out of time but fails to terminate the process
	$psarg += "-Noninteractive -Command Start-Sleep 55; exit 0"
	$waitTimeSeconds = 11 #60 * 60 * 24
	$maxWaitTimeIteration = 3 #32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 4 # 300
	$noKillDebug = $true
}

$ldir = "C:\temp\LOGS\$JobName\"
$logMaxDays = 180
$lfiles = $ldir + ''*.log''
mkdir -path $ldir -erroraction silentlycontinue
$dt = get-date -Format yyyymmdd_HHmm
$fout = $ldir + $JobName.split(".")[0] + "." + $dt + ".stdout.log"
$ferr = $ldir + $JobName.split(".")[0] + "." + $dt + ".stderr.log"

$ErrorActionPreference = ''Continue'' # you must have this as Continue for the write-error output to appear

$process = start-process -WorkingDirectory "C:\temp" -RSO $fout -RSE $ferr powershell.exe -ArgumentList $psarg -passthru  # worked


<#############   MAKE NO CHANGES BELOW HERE   #################>

<#
	Wait-Process -TimeOut parameter is not Int32 !!!  Max value is 32767   !!!
	Need to loop if the timeout value exceeds this limitation
	
	If the modulus is zero then the remainder needs to be set to iteration since the last wait is not run in the loop
#>
$waitTimeIterations = $waitTimeSeconds / $maxWaitTimeIteration
$waitTimeRemainder = $waitTimeSeconds % $maxWaitTimeIteration
if ($waitTimeRemainder -eq 0) { $waitTimeRemainder = $maxWaitTimeIteration; } # reset the remainder when modulus is zero
while ($waitTimeIterations -gt 1) {
	# we do not care about these waits as are only run when the $waitTimeSeconds > $maxWaitTimeIteration
	$process | Wait-Process -Timeout $maxWaitTimeIteration -ErrorAction SilentlyContinue # do not include the ErrorVariable here
	$waitTimeIterations = $waitTimeIterations - 1
}
$process | Wait-Process -Timeout $waitTimeRemainder -ErrorVariable wpError -ErrorAction SilentlyContinue 

if ($wpError) {
	"`n`n*****`nAttempting to terminate Process ID {0} because it did not complete within allotted time of {1} seconds...`n*****`n" -f $process.Id, $waitTimeSeconds | write-error 
	
	if (-not $noKillDebug) { Stop-Process -Id $process.Id -ErrorAction SilentlyContinue }
	Wait-Process -Id $process.Id -timeout $killWaitTimeSeconds -ErrorAction SilentlyContinue 
	
	if ($process.ExitTime -eq $null) {
		$timeoutMsg = ".`n`nProcess did not terminate after {0} seconds and appears to be hung.`n******  PLEASE KILL PROCESS ID [ {1} ]  ********`n" -f $killWaitTimeSeconds, $process.Id
		$resultMsg = "`n`nRESULT:  SCRIPT IS HUNG AND PROCESS NEEDS TO BE TERMINATED"
	} else {
		$timeoutMsg = ".`n`nProcess {0} has been terminated.  Please check script logs.`n" -f $process.Id
		$resultMsg = "`n`nRESULT:  SCRIPT WAS TERMINATED DUE TO TIMEOUT"
	}
	
} # if ($wpError) 


$procStats = @''
.

CPU Time (seconds)  : [ {0} ]
CPU Time (ticks)    : [ {1} ]
PeakWorkingSet64    : [ {2} ]
StartTime           : [ {3} ]
ExitTime            : [ {4} ]
Exit Code           : [ {5} ]

''@ -f $process.CPU, $process.TotalProcessorTime.Ticks, $process.PeakWorkingSet64, $process.StartTime, $process.ExitTime, $process.ExitCode



if ($process.ExitCode -eq 0) {
	
	$procStats | write-output
	
	$DatetoDelete = (Get-Date).AddDays([math]::Abs($logMaxDays) * -1)
	"`nLooking for log files older than {0} in: {1}" -f $DatetoDelete, $ldir | write-output
	
	$oldLogFiles = Get-ChildItem $lfiles | Where-Object { $_.LastWriteTime -lt $DatetoDelete }
	if ($oldLogFiles.Count -ge 1) {
		''`nDeleting {0} log files'' -f $oldLogFiles.Count | write-output
		$oldLogFiles | Remove-Item
	} else {
		write-output "`nNo log files to delete."
	}
	"`nRESULT:  Script completed successfully." | write-output
	
} else {
	# Either script failed or it hung/timed-out	
	
	$ErrorActionPreference = ''Stop''  # a write-error will exit the script immediately (and cleanly)
	if ($timeoutMsg) {
		$procStats + $timeoutMsg + $resultMsg | Write-Error
		# throw $throwMsg
	} else {
		$procStats + "`n`nRESULT:  SCRIPT RETURNED A FAILURE CODE.  Please check the script log files.`n" | write-error
		#throw ''SCRIPT RETURNED A FAILURE CODE''
	}
	
} # if ($process.ExitCode -eq 0)


', 
		@database_name=N'master', 
		@flags=40
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Script Completes - Exit 0', 
		@step_id=4, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'<#
	PowerShell Agent Job Step Error Handling

	IMPORTANT THINGS TO KNOW:

		* Write-Host does not work, must use Write-Output (STDOUT) or Write-Error (STDERR)
		* Only STDOUT _or_ STDERR will be added to the job step log, not both.  If there is any STDERR, no STDOUT will be returned
		* $ErrorActionPreference setting and -ErrorAction parameter are key to whether the script terminates or adds the error to the log
		* If you try to exit with [Environment]::Exit(1) nothing is returned
		* Use throw to terminate the script and add the error to the log
		* STDERROR does not use newlines, and appears to trim blank lines, so add a period then newline to help format output
		* SilentlyContinue will not be logged, so you can use it to write your own messages with Write-Error
		* Continue will be logged and is unformatted

	2021-03-25	ronthepolymath.wordpress.com

#>

$JobName = ''test''
$psarg = @();

if (1 -eq 1) {
	# script completes in time and is successful
	$psarg += "-Noninteractive -Command Start-Sleep 5; exit 0"
	$waitTimeSeconds = 30 #60 * 60 * 24
	$maxWaitTimeIteration = 32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 300
}

if (1 -eq 0) {
	# script completes in time but has exit code
	$psarg += "-Noninteractive -Command Start-Sleep 5; exit 1"
	$waitTimeSeconds = 30 #60 * 60 * 24
	$maxWaitTimeIteration = 32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 300
}

if (1 -eq 0) {
	# script runs out of time and is terminated
	$psarg += "-Noninteractive -Command Start-Sleep 25; exit 1"
	$waitTimeSeconds = 11 # 30 #60 * 60 * 24
	$maxWaitTimeIteration = 3 # 32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 300
}

if (1 -eq 0) {
	# script runs out of time but fails to terminate the process
	$psarg += "-Noninteractive -Command Start-Sleep 55; exit 0"
	$waitTimeSeconds = 11 #60 * 60 * 24
	$maxWaitTimeIteration = 3 #32767 # max value for Wait-Process -Timeout parameter (it is not [Int32])
	$killWaitTimeSeconds = 4 # 300
	$noKillDebug = $true
}

$ldir = "C:\temp\LOGS\$JobName\"
$logMaxDays = 180
$lfiles = $ldir + ''*.log''
mkdir -path $ldir -erroraction silentlycontinue
$dt = get-date -Format yyyymmdd_HHmm
$fout = $ldir + $JobName.split(".")[0] + "." + $dt + ".stdout.log"
$ferr = $ldir + $JobName.split(".")[0] + "." + $dt + ".stderr.log"

$ErrorActionPreference = ''Continue'' # you must have this as Continue for the write-error output to appear

$process = start-process -WorkingDirectory "C:\temp" -RSO $fout -RSE $ferr powershell.exe -ArgumentList $psarg -passthru  # worked

<#############   MAKE NO CHANGES BELOW HERE   #################>

<#
	Wait-Process -TimeOut parameter is not Int32 !!!  Max value is 32767   !!!
	Need to loop if the timeout value exceeds this limitation
	
	If the modulus is zero then the remainder needs to be set to iteration since the last wait is not run in the loop
#>
$waitTimeIterations = $waitTimeSeconds / $maxWaitTimeIteration
$waitTimeRemainder = $waitTimeSeconds % $maxWaitTimeIteration
if ($waitTimeRemainder -eq 0) { $waitTimeRemainder = $maxWaitTimeIteration; } # reset the remainder when modulus is zero
while ($waitTimeIterations -gt 1) {
	# we do not care about these waits as are only run when the $waitTimeSeconds > $maxWaitTimeIteration
	$process | Wait-Process -Timeout $maxWaitTimeIteration -ErrorAction SilentlyContinue # do not include the ErrorVariable here
	$waitTimeIterations = $waitTimeIterations - 1
}
$process | Wait-Process -Timeout $waitTimeRemainder -ErrorVariable wpError -ErrorAction SilentlyContinue 

if ($wpError) {
	"`n`n*****`nAttempting to terminate Process ID {0} because it did not complete within allotted time of {1} seconds...`n*****`n" -f $process.Id, $waitTimeSeconds | write-error 
	
	if (-not $noKillDebug) { Stop-Process -Id $process.Id -ErrorAction SilentlyContinue }
	Wait-Process -Id $process.Id -timeout $killWaitTimeSeconds -ErrorAction SilentlyContinue 
	
	if ($process.ExitTime -eq $null) {
		$timeoutMsg = ".`n`nProcess did not terminate after {0} seconds and appears to be hung.`n******  PLEASE KILL PROCESS ID [ {1} ]  ********`n" -f $killWaitTimeSeconds, $process.Id
		$resultMsg = "`n`nRESULT:  SCRIPT IS HUNG AND PROCESS NEEDS TO BE TERMINATED"
	} else {
		$timeoutMsg = ".`n`nProcess {0} has been terminated.  Please check script logs.`n" -f $process.Id
		$resultMsg = "`n`nRESULT:  SCRIPT WAS TERMINATED DUE TO TIMEOUT"
	}
	
} # if ($wpError) 


$procStats = @''
.

CPU Time (seconds)  : [ {0} ]
CPU Time (ticks)    : [ {1} ]
PeakWorkingSet64    : [ {2} ]
StartTime           : [ {3} ]
ExitTime            : [ {4} ]
Exit Code           : [ {5} ]

''@ -f $process.CPU, $process.TotalProcessorTime.Ticks, $process.PeakWorkingSet64, $process.StartTime, $process.ExitTime, $process.ExitCode



if ($process.ExitCode -eq 0) {
	
	$procStats | write-output
	
	$DatetoDelete = (Get-Date).AddDays([math]::Abs($logMaxDays) * -1)
	"`nLooking for log files older than {0} in: {1}" -f $DatetoDelete, $ldir | write-output
	
	$oldLogFiles = Get-ChildItem $lfiles | Where-Object { $_.LastWriteTime -lt $DatetoDelete }
	if ($oldLogFiles.Count -ge 1) {
		''`nDeleting {0} log files'' -f $oldLogFiles.Count | write-output
		$oldLogFiles | Remove-Item
	} else {
		write-output "`nNo log files to delete."
	}
	"`nRESULT:  Script completed successfully." | write-output
	
} else {
	# Either script failed or it hung/timed-out	
	
	$ErrorActionPreference = ''Stop''  # a write-error will exit the script immediately (and cleanly)
	if ($timeoutMsg) {
		$procStats + $timeoutMsg + $resultMsg | Write-Error
		# throw $throwMsg
	} else {
		$procStats + "`n`nRESULT:  SCRIPT RETURNED A FAILURE CODE.  Please check the script log files.`n" | write-error
		#throw ''SCRIPT RETURNED A FAILURE CODE''
	}
	
} # if ($process.ExitCode -eq 0)


', 
		@database_name=N'master', 
		@flags=40
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Strip quotes from CSV in PowerShell

Prior to PowerShell 7.0, there is no native way to strip quotation marks from a CSV file or pipeline. This can be accomplished with minimal performance impact using a filter function.

Below will strip quotes from a CSV unless an element contains a comma:

# Strips the quotes from a CSV unless the element contains a comma
filter ConvertFromCsv-StripQuotes {
  param
  (
	 [regex]$regEx = "\G(?<start>^|,)((`"(?<output>[^,`"]*?)`"(?=,|`$))|(?<output>`".*?(?<!`")(`"`")*?`"(?=,|`$))|(?<output>))",
	 [Parameter(ValueFromPipeline = $true)]
	 $val
  )
  $_ -replace $regEx, '${start}${output}'
}

$DataTable | ConvertTo-Csv -NoTypeInformation -Delimiter "," | ConvertFromCsv-StripQuotes | Out-File -FilePath $OutFile

Below will strip the quotes around all elements in a tab-separated file, even if an element contains a comma:

# Strips the quotes from a TSV even if an element contains a comma
filter ConvertFromTsv-StripQuotes {
  param
  (
	 [regex]$regEx = "\G(?<start>^|\t)((`"(?<output>[^\t`"]*?)`"(?=\t|`$))|(?<output>`".*?(?<!`")(`"`")*?`"(?=\t|`$))|(?<output>))",
	 [Parameter(ValueFromPipeline = $true)]
	 $val
  )
  $_ -replace $regEx, '${start}${output}'
}

$DataTable | ConvertTo-Csv -NoTypeInformation -Delimiter "`t" | ConvertFromTsv-StripQuotes | Out-File -FilePath $OutFile

Below will strip all quotes from a TSV and convert the TAB separators to “||“.

# Convert from a TSV with quotes to a delimited without quotes.  Not as fast due to the if statement.
# Do not use with a comma delimiter as there are not quotes around the field if it contains a comma.
filter ConvertFromTsv-StripQuotes2 {
  param
  (
	 [regex]$regEx = "\G(?<start>^|\t)((`"(?<output>[^\t`"]*?)`"(?=\t|`$))|(?<output>`".*?(?<!`")(`"`")*?`"(?=\t|`$))|(?<output>))",
	 $delimiter,
	 [Parameter(ValueFromPipeline = $true)]
	 $val
  )
  if ($delimiter) {
		 ($_ -replace $regEx, '${start}${output}').replace("`t", $delimiter)
  } else {
		 $_ -replace $regEx, '${start}${output}'
  }
}

$DataTable | ConvertTo-Csv -NoTypeInformation -Delimiter "`t" | ConvertFromTsv-StripQuotes2  -delimiter "||" | Out-File -FilePath $OutFile

Props to the commenters for the CSV regex: https://stackoverflow.com/questions/24074205/convertto-csv-output-without-quotes/57086294

Microsoft Quietly Adds TLS 1.2 Support to MDAC and SQLOLEDB

The October 2020 Preview releases of Windows builds 1809 (KB4580390), 1903/1909 (KB4580386), and 2004/20H2 (KB4580364) include the following change:

  • Adds support for the Transport Layer Security (TLS) 1.1 and 1.2 protocols when connecting to SQL Server using the data providers in Microsoft Data Access Components (MDAC).

I tested an ADODB.Connection using the SQLOLEDB provider in a VBScript and confirmed that SQLOLEDB and TLS 1.2 now work together on these newer builds, yet fails as expected on earlier builds with the DNETLIB ConnectionOpen SECDoClientHandshake SSL Security Error. This would also apply to any embedded connection in Access, Excel, etc. which uses SQLOLEDB by default for connections to SQL Server.

Also included in the same updates:

  • Addresses an issue with SQL Server that might cause performance issues if you configure a Linked Server provider to load out-of-process.

I could not find any additional information or guidance about either of these updates, and the TLS 1.2 Support for SQL Server KB3135244 has not been updated to include this information.

Notepad++ User Defined Language for TNSNAMES.ORA

Use this to add color coding and block collapse to a TNSNAMES.ORA file for Oracle.

<NotepadPlus>
<UserLang name="TNSNAMES" ext="ora" udlVersion="2.1">
<Settings>
<Global caseIgnored="yes" allowFoldOfComments="yes" foldCompact="no" forcePureLC="2" decimalSeparator="2" />
<Prefix Keywords1="no" Keywords2="no" Keywords3="no" Keywords4="no" Keywords5="no" Keywords6="no" Keywords7="no" Keywords8="no" />
</Settings>
<KeywordLists>
<Keywords name="Comments">00# 01 02 03 04</Keywords>
<Keywords name="Numbers, prefix1"></Keywords>
<Keywords name="Numbers, prefix2"></Keywords>
<Keywords name="Numbers, extras1"></Keywords>
<Keywords name="Numbers, extras2"></Keywords>
<Keywords name="Numbers, suffix1"></Keywords>
<Keywords name="Numbers, suffix2"></Keywords>
<Keywords name="Numbers, range"></Keywords>
<Keywords name="Operators1">=</Keywords>
<Keywords name="Operators2"></Keywords>
<Keywords name="Folders in code1, open">(</Keywords>
<Keywords name="Folders in code1, middle"></Keywords>
<Keywords name="Folders in code1, close">)</Keywords>
<Keywords name="Folders in code2, open"></Keywords>
<Keywords name="Folders in code2, middle"></Keywords>
<Keywords name="Folders in code2, close"></Keywords>
<Keywords name="Folders in comment, open">##https://docs.oracle.com/database/121/NETRF/tnsnames.htm#NETRF007</Keywords>
<Keywords name="Folders in comment, middle"></Keywords>
<Keywords name="Folders in comment, close"></Keywords>
<Keywords name="Keywords1">#DESC_LIST_OR_DESC_OR_ADDR_LIST source_route failover load_balance </Keywords>
<Keywords name="Keywords2">#DESCRIPTION enable sdu type_of_service connect_timeout retry_count retry_delay transport_connect_timeout compression compression_levels  security ssl_server_cert_dn</Keywords>
<Keywords name="Keywords3">#KEYWORDS </Keywords>
<Keywords name="Keywords4">#ADDRESS_PARAMS protocol host community service pipe port </Keywords>
<Keywords name="Keywords5">#DESCRIPTION_OR_ADDRESS_PROTOCOL  recv_buf_size send_buf_size </Keywords>
<Keywords name="Keywords6">#CONNECT_DATA sid server srvr service_name server instance_name ur global_name hs rdb_database </Keywords>
<Keywords name="Keywords7">#FAILOVER_PARMS backup type method transaction retries delay commit_outcome</Keywords>
<Keywords name="Keywords8">#FIXED_OPTS select basic tcp nmp spx on off yes no true false shared pooled dedicated low high preconnect session none ok</Keywords>
<Keywords name="Delimiters">00description_list 01 02= 03description 04 05= 06address_list 07 08= 09address 10 11= 12connect_data 13 14= 15failover_mode 16 17= 18IFILE 19 20(( EOL )) 21 22 23</Keywords>
</KeywordLists>
<Styles>
<WordsStyle name="DEFAULT" fgColor="000000" bgColor="FFFFFF" fontStyle="0" nesting="0" />
<WordsStyle name="COMMENTS" fgColor="000000" bgColor="FFFFFF" fontStyle="0" nesting="0" />
<WordsStyle name="LINE COMMENTS" fgColor="68DF11" bgColor="FFFFFF" fontStyle="2" nesting="0" />
<WordsStyle name="NUMBERS" fgColor="FF00FF" bgColor="FFFFFF" fontStyle="1" nesting="0" />
<WordsStyle name="KEYWORDS1" fgColor="A97C21" bgColor="FFFFFF" fontStyle="1" nesting="0" />
<WordsStyle name="KEYWORDS2" fgColor="FF8000" bgColor="FFFFFF" fontStyle="0" nesting="0" />
<WordsStyle name="KEYWORDS3" fgColor="FF0000" bgColor="FFFF00" fontStyle="0" nesting="0" />
<WordsStyle name="KEYWORDS4" fgColor="008040" bgColor="FFFFFF" fontStyle="0" nesting="0" />
<WordsStyle name="KEYWORDS5" fgColor="AF9116" bgColor="FFFFFF" fontStyle="1" nesting="0" />
<WordsStyle name="KEYWORDS6" fgColor="0000FF" bgColor="FFFFFF" fontStyle="0" nesting="0" />
<WordsStyle name="KEYWORDS7" fgColor="5555FF" bgColor="FFFFFF" fontStyle="2" nesting="0" />
<WordsStyle name="KEYWORDS8" fgColor="0080C0" bgColor="FFFFFF" fontStyle="1" nesting="0" />
<WordsStyle name="OPERATORS" fgColor="FF8040" bgColor="FFFFFF" fontStyle="1" nesting="0" />
<WordsStyle name="FOLDER IN CODE1" fgColor="FF0000" bgColor="FFFFFF" fontStyle="1" nesting="0" />
<WordsStyle name="FOLDER IN CODE2" fgColor="000000" bgColor="FFFFFF" fontStyle="0" nesting="0" />
<WordsStyle name="FOLDER IN COMMENT" fgColor="000000" bgColor="FFFFFF" fontStyle="0" nesting="0" />
<WordsStyle name="DELIMITERS1" fgColor="400080" bgColor="DFDFFF" fontStyle="2" nesting="0" />
<WordsStyle name="DELIMITERS2" fgColor="7429B8" bgColor="FFFFFF" fontStyle="1" nesting="1" />
<WordsStyle name="DELIMITERS3" fgColor="800000" bgColor="FFFFFF" fontStyle="3" nesting="16777219" />
<WordsStyle name="DELIMITERS4" fgColor="008000" bgColor="D5FFDC" fontStyle="1" nesting="7" />
<WordsStyle name="DELIMITERS5" fgColor="0000FF" bgColor="FFFFFF" fontStyle="1" nesting="16" />
<WordsStyle name="DELIMITERS6" fgColor="5555FF" bgColor="DFDFFF" fontStyle="3" nesting="0" />
<WordsStyle name="DELIMITERS7" fgColor="FF0000" bgColor="FFFF00" fontStyle="1" nesting="16777216" />
<WordsStyle name="DELIMITERS8" fgColor="408080" bgColor="FFFFFF" fontStyle="0" nesting="0" />
</Styles>
</UserLang>
</NotepadPlus>

Cycle SQL Error Log when it reaches a certain size

Add this as a SQL Agent job.  This is useful for preventing very large error log files and can also help alert on errors due to frequent cycling.

 

/*

            Cycle error log when exceeds given size

            Ron Klimaszewski

            2015-09-30

            https://ronthepolymath.wordpress.com/

*/

SET NOCOUNT ON

DECLARE       @limit float, @mailto nvarchar(150), @mailsubject nvarchar(100), @mailquery nvarchar(100), @v varchar(15)

SELECT @v = CAST(serverproperty(‘productversion’) as varchar)

 

— Set the limit to 3MB

SET @limit = 3000000

SET @mailto = ‘sqldbas@somecompany.com’

SET @mailsubject = ‘SQL Farm Warning: Error logs cycled on ‘ + @@SERVERNAME

SET @mailquery = ‘SELECT * FROM ##logs order by Lognum’

 

IF OBJECT_ID(‘tempdb..##logs’) IS NOT NULL drop table ##logs

create table ##logs ( Lognum int, Logdate datetime, [Size (bytes)] float)

insert into ##logs exec master..xp_enumerrorlogs

if ( select [Size (bytes)] from ##logs where LogNum = 0) > @limit

begin

            exec master.dbo.sp_cycle_errorlog

            IF (LEFT(@V,4) = ‘8.00’)

            BEGIN

                        exec master.dbo.xp_sendmail @recipients= @mailto, @subject = @mailsubject, @query = @mailquery, @width = 300

            END

            ELSE BEGIN

                        exec msdb..sp_send_dbmail @recipients= @mailto, @subject = @mailsubject, @query = @mailquery

            END

 end

drop table ##logs

Chain too long? Here’s a quick fix.

Say you are connecting safety chains from a trailer to a vehicle, but comes close to or touches the road.  If you drive like that, you might not have much chain left.  Of say you are using a loading ramp on a pickup truck, and its chains are too long.  The purpose of the safety chain is to prevent the ramp from slipping off the tailgate–too long and they serve no purpose.

How do you remedy these situations?  Simple.  Follow Chubby Checker’s advice and do The Twist!

Just take the chain and twist it a few times and it will shorten up.

http://www.youtube.com/watch?v=pHGXwQeUk7M

Hello world!

A polymath (Greek: πολυμαθής, polymathēs, “having learned much”)[1] is a person whose expertise spans a significant number of different subject areas; such a person is known to draw on complex bodies of knowledge to solve specific problems.

When I get the time, I will post things I find important for others to know, primarily relating to Enterprise IT, but also on cooking, making beer, home improvements, camping, automotive, motorcycling, and religion.