[master_admin].[dbo].[fn_RunDuration]

Leave a reply 
CREATE FUNCTION [dbo].[fn_RunDuration] (@nRunDuration INT)
RETURNS VARCHAR(128)
AS 
BEGIN 
    DECLARE @vcMsg VARCHAR(128),
        @nH INT,
        @nM INT,
        @nS INT,
        @vcH VARCHAR(48),
        @vcM VARCHAR(48),
        @vcS VARCHAR(48)
    SELECT  @nH = LEFT(RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 6), 2),
            @nM = LEFT(RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 4), 2),
            @nS = RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 2),
            @vcH = CASE WHEN @nH = 0 THEN ''
                        WHEN @nH = 1 THEN CAST(@nH AS VARCHAR) + ' hour, '
                        ELSE CAST(@nH AS VARCHAR) + ' hours, '
                   END,
            @vcM = CASE WHEN @nM = 0 THEN ''
                        WHEN @nM = 1 THEN CAST(@nM AS VARCHAR) + ' minute, '
                        ELSE CAST(@nM AS VARCHAR) + ' minutes, '
                   END,
            @vcS = CASE WHEN @nS = 1 THEN CAST(@nS AS VARCHAR) + ' second'
                        ELSE CAST(@nS AS VARCHAR) + ' seconds'
                   END,
            @vcMsg = @vcH + @vcM + @vcS

    RETURN @vcMsg
END

[master_admin].[dbo].[fn_INTtoDateTime]

Leave a reply 
CREATE FUNCTION [dbo].[fn_INTtoDateTime] (
     @vcDate VARCHAR(8),
     @vcTime VARCHAR(6)
    )
RETURNS DATETIME
AS 
BEGIN
    SET @vcDate = RIGHT(RTRIM('00000000' + CONVERT(CHAR(8), @vcDate) + ' '), 8)
    SET @vcTime = RIGHT(RTRIM('00000000' + CONVERT(CHAR(6), @vcTime) + ' '), 6)

    IF @vcDate = '00000000' 
        SET @vcDate = LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), CAST(0 AS SMALLDATETIME), 121),
                                                   '-', ''), ':', ''),
                                   SPACE(1), ''), 8)
	
    RETURN (  
	CAST(SUBSTRING(@vcDate,1,4) + '-' + SUBSTRING(@vcDate,5,2) + '-' + SUBSTRING(@vcDate,7,2) + ' ' + 
		SUBSTRING(@vcTime,1,2) + ':' + SUBSTRING(@vcTime,3,2) + ':' + SUBSTRING(@vcTime,5,2) 
	AS DATETIME)
	)
END

SQL Agent: What ran last night after time …

Leave a reply 
------------------------------------------------------------------------------------------------------------------------
--
-- What ran last night after time ...
--
SELECT  [master_admin].[dbo].[fn_INTtoDateTime]([run_date], [run_time]) AS [RunDate],
        DATENAME(WEEKDAY, CONVERT(VARCHAR(10), [run_date], 112)),
        [j].[name],
        [master_admin].[dbo].[fn_RunDuration]([run_duration]),
        [h].[step_id],
        [s].[step_name],
        [command] AS [StepCommand],
        [instance_id]
FROM    [msdb].[dbo].[sysjobhistory] [h] WITH (NOLOCK)
JOIN    [msdb].[dbo].[sysjobs] [j] WITH (NOLOCK)
ON      [j].[job_id] = [h].[job_id]
JOIN    [msdb]..[sysjobsteps] [s] WITH (NOLOCK)
ON      [s].[job_id] = [j].[job_id]
        AND [s].[step_id] = [h].[step_id]
WHERE   [run_date] >= CONVERT(VARCHAR(8), DATEADD(DAY, -0, GETDATE()), 112)

AND [h].[run_time] >= 021439
ORDER BY [RunDate]

Required objects
[fn_INTtoDateTime]
[fn_RunDuration]

 

What are all those old tempdb temp tables?

Leave a reply

SQL Server caches the definitions of temp tables and table variables, to reduce the load on the system catalog in tempdb on busy systems which creates a lot of temp tables. Typically they have a name that starts with # and is followed by eight hex digits. -Erland Sommarskog

SQL Server Temporary Table Caching

This tip will describe the condition to allow caching of a SQL Server temporary table and a demonstration to benchmark the performance between a cached vs. non-cached temporary table.

Source: www.mssqltips.com/sqlservertip/4406/sql-server-temporary-table-caching/

 

 

PowerShell: Order of command precedence

Leave a reply

Here is the order of command precedence that is used by the command discovery process in Windows PowerShell:

· Alias: All Windows PowerShell aliases in the current session

· Filter/Function: All Windows PowerShell functions

· Cmdlet: The cmdlets in the current session (“Cmdlet” is the default)

· ExternalScript: All .ps1 files in the paths that are listed in the Path environment variable ($env:PATH)

· Application: All non-Windows-PowerShell files in paths that are listed in the Path environment variable

· Script: Script blocks in the current session

SSIS Package Errors

Leave a reply

Here are some we’ve encountered with resolutions.

Failed to decrypt protected XML node ... "Key not valid for use in specified state."

The creator of the package is someone else than the process owner of the account running it.
If you create the package and run from within SQL the executing account would be the service account.


https://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/

PowerShell: Function Get-LastUpdates shows most recent Windows updates

Leave a reply 
Function Get-LastUpdates {

    Param(
        [parameter(Position=0, Mandatory = $false)] [Int32] $DaysAgo = 0,
        [parameter(Position=1, Mandatory = $false)] [Int32] $LastNum
    )
    Process {
    
        $Session = New-Object -ComObject "Microsoft.Update.Session"
        $Searcher = $Session.CreateUpdateSearcher()
 
        if ($LastNum) {
            $historyCount = $LastNum
        } else {
            $historyCount = $Searcher.GetTotalHistoryCount()
        }
    
        # Limit the returned rows by $historyCount
        $HistItems = $Searcher.QueryHistory(0, $historyCount) 
 
     #$HistItems | ?{$_.Title -ne $null} |sort Date | Select-Object Date, Title, @{n="desc"; e={$_.Description.substring(0,10)}},  @{name="Operation"; expression={switch($_.operation){ 1 {"Installation"}; 2 {"Uninstallation"}; 3 {"Other"}}}}
     #$HistItems |select -First 1
        if ($DaysAgo -eq 0) {
            $HistItems | ?{$_.Title -ne $null} |sort Date | Select-Object Date, Title
        } else {
            $HistItems | ?{$_.Title -ne $null} | ?{$_.Date -gt (Get-Date).AddDays(-$DaysAgo) } |sort Date | Select-Object Date, Title
    
        }
        # https://itbeco.wordpress.com/microsoft/powershell/
        #Select-Object Date, @{expression={$COMPUTERNAME};Label="Host"}, @{name="Operation"; expression={switch($_.operation){1 {"Installation"}; 2 {"Uninstallation"}; 3 {"Other"}}}}, @{name="Status"; expression={switch($_.resultcode){1 {"In Progress"}; 2 {"Succeeded"}; 3 {"Succeeded With Errors"};4 {"Failed"}; 5 {"Aborted"}}}},@{name="Title";expression={[regex]::Match($_.Title,'(KB[0-9]{6,7})').Value}}
    }
}

And to run from DOS

@ECHO OFF
 
if {%1}=={} (
@ECHO USAGE
@ECHO   GetLastUpdates DaysAgo
@ECHO.
GOTO :FINISHED
)


Powershell -noprofile -command "&{ . E:\MSSQL\PS\adhoc\Get-LastUpdates.ps1; Get-LastUpdates -DaysAgo %1 }

:FINISHED