TSQL: Find characters in one string missing from another

I have a set of characters, all of which must match the characters in another set.

e.g. All characters in “RW” must exist in “RWCE”.

There’s probably an easier way, but here’s one solution.

  1. Create a CTE table of the string to test, converting a set of letters to rows.
  2. Return any rows of just the missing or failed characters
  3. If a row EXISTS we want to report the error.

DECLARE @vcNewPermissions VARCHAR(128)
SELECT @vcNewPermissions = 'RW'
--
IF OBJECT_ID('tempdb.dbo.[#IllegalPermissions]') IS NOT NULL
    DROP TABLE [#IllegalPermissions]
--
;WITH    [mycte]([x])
          AS (
              SELECT    SUBSTRING([a].[FindString], [v].[number] + 1, 1)
              FROM      (
                         SELECT @vcNewPermissions AS [FindString]
                        ) [a]
              JOIN      [master].[dbo].[spt_values] [v]
              ON        [v].[number] < LEN([a].[FindString])
              WHERE     [v].[type] = 'P'
             )
    SELECT  [x]
    INTO    [#IllegalPermissions]
    FROM    [mycte]
    WHERE   CHARINDEX([x], 'RWCESVXT') = 0

IF EXISTS ( SELECT  *
            FROM    [#IllegalPermissions] )
    RAISERROR('ERROR: permission not found',16,1)

Msg 11520, Level 16, State 1

Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘EXECUTE master.dbo.xp_sqlagent_is_starting @retval OUTPUT’ in procedure ‘sp_is_sqlagent_starting’ invokes an extended stored procedure.

In SQL 2012 sp_help_job has changed. See this solution:

Workaround: SQL Server 2012 – OPENROWSET on sp_help_job throws “The metadata could not be determined”

Issue:In SQL 2012, stored procedures that call any other stored procedures or extended stored procedures that return result sets should specify WITH RESULT SETSSQL Agent’s stored procedure sp_help_job  – returns single resultset if no parameters are passed to stored procedure.  – returns 3 resultsets if job_id or job_name is specified. MSDN linkWhen RESULTSET is not described,…

Source: blogs.msdn.microsoft.com/sqlagent/2012/07/12/workaround-sql-server-2012-openrowset-on-sp_help_job-throws-the-metadata-could-not-be-determined/

PowerShell: Check all servers for failed jobs.

This is a two part process.

First we use our [master_admin].[dbo].[rp_JobFailures] stored procedure to report any failures on the server. Here’s the guts of our procedure.

DECLARE @nDaysAgo INT
SELECT  @nDaysAgo = 1
SELECT  CAST([j].[name] AS VARCHAR(128)) [FAILED_job_name],
        LEFT(DATENAME(dw, CAST([run_date] AS CHAR(8))) + SPACE(1)
             + CAST([master_admin].[dbo].[fn_INTtoDateTime]([h].[run_date], [h].[run_time]) AS VARCHAR), 30) [RunDate],
        CASE [run_status]
          WHEN 0 THEN 'Failed'
          WHEN 1 THEN 'Succeeded'
          WHEN 2 THEN 'Retry'
          WHEN 3 THEN 'Canceled'
          WHEN 4 THEN 'In progress'
        END [job_status],
        (ISNULL(MAX([s].[step_id]), '')) [StepID],
        (ISNULL(MAX([s].[step_name]), '')) [StepName],
        (ISNULL(MAX([command]), MAX([h].[message]))) [StepCommand],
        MAX([operator_id_emailed]) [NotifiedID]
FROM    [msdb]..[sysjobhistory] [h]
JOIN    [msdb]..[sysjobs] [j]
ON      [j].[job_id] = [h].[job_id]
JOIN    [msdb]..[syscategories] 1
ON      [j].[category_id] = 1.[category_id]
LEFT OUTER JOIN (
                 -- All jobsteps that have a final history job outcome of
	SELECT  [s].[step_name],
            [s].[command],
            [s].[step_id],
            [s].[job_id],
            [last_run_outcome]
    FROM    [msdb]..[sysjobsteps] [s] WITH (NOLOCK)
    LEFT OUTER JOIN (
                     SELECT [job_id]
                     FROM   [msdb]..[sysjobhistory] [h]
                     WHERE  [run_status] = 0
                            AND [h].[run_date] >= REPLACE(CONVERT(VARCHAR(10), DATEADD(DAY, -@nDaysAgo, GETDATE()), 121),
                                                          '-', '')
                    ) [okay]
    ON      [okay].[job_id] = [s].[job_id]
    WHERE   [okay].[job_id] IS NOT NULL
            AND [last_run_date] >= REPLACE(CONVERT(VARCHAR(10), DATEADD(DAY, -@nDaysAgo, GETDATE()), 121), '-', '')
                ) [s]
ON      [h].[job_id] = [s].[job_id]
        AND [h].[step_id] = [s].[step_id]
WHERE   [h].[run_date] >= REPLACE(CONVERT(VARCHAR(10), DATEADD(DAY, -@nDaysAgo, GETDATE()), 121), '-', '')
        AND [run_status] NOT IN (1, 4)
GROUP BY [j].[name],
        [run_date],
        [run_time],
        [run_status]
ORDER BY [h].[run_date],
        [h].[run_time],
        [j].[name] 

Then we use PowerShell to run that procedure on each of our SQL Servers, stored in $AllServersList.

# ------------------------------------------------------------------------------------------------------------------------
# What jobs failed since yesterday?

$TSQL = 'EXEC [master_admin].[dbo].[rp_JobFailures] @nDaysAgo=1, @vcSkipJob=NULL'

$VerbosePreference="Continue"
$bDEBUG = $true
#	$bDEBUG = $false ; $VerbosePreference="SilentlyContinue" ; $dt=@()
$AllServersList | %{
	$CurrentServer = $_
	Write-Host -ForegroundColor Blue "$(Get-Date -Format g)	[$CurrentServer]"
	Write-Verbose "	Invoke-sqlcmd2 -ConnectionTimeout 10 -ServerInstance $_ -Query ""$TSQL"""; 

	if (!($ExclusionList -contains $CurrentServer)) {
		if ($bDEBUG -eq $false) {
			$dr = Invoke-sqlcmd2 -ConnectionTimeout 10 -ServerInstance $CurrentServer -Query "$TSQL" -As DataRow
			if ($dr -ne $null) {
				$dr | add-member -name ServerName -type noteproperty -value "$CurrentServer"
				$dt += $dr
			}
		}
	} else {
		Write-Host -ForegroundColor Red "Skipping $CurrentServer $_"
	}
}

$dt |Select-Object ServerName, RunDate, Failed_Job_Name, StepID, StepName, StepCommand |ft -AutoSize

You can see I like to run Foreach without doing anything initially to make sure I got it right. Then highlighting from the commented $bDEBUG line to the end gets us what we want. I also use an exclusion list which is simply an array of server names to bypass.

The SQL purists will suggest we just run it within SSMS. Just create server groups for all your servers and right-click and run the query.

SQL Backups reviewing LSNs

Here’s a nice page with some code which shows how to build a database test case sample where you can run your own tests.

How do I map a differential/transaction log backup to its full backup without using MSDB?

If you have multiple full, differential and transaction log backups, there are chances you may end up facing the error below during the restore process. “This differential backup cannot be restored because the database has not been restored to the correct earlier state”   You know that any differential/T-log  backup is mapped to particular full…

Source: blogs.msdn.microsoft.com/sqlserverfaq/2013/05/22/how-do-i-map-a-differentialtransaction-log-backup-to-its-full-backup-without-using-msdb/

I like this site’s simple list of rules which shows the FULL backup and where the CheckpointLSN and FirstLSN could be found in the DIFF and LOG backups.

Considering SQL Server Log Sequence Numbers for Backups

Take a look at this blog and understand SQL Server log sequence numbers for backups and know why LSN is important while restoring the database

Source: www.sqlmvp.org/sql-server-log-sequence-numbers-for-backups/

This page has nice graphics following the LSNs.

Understanding SQL Server Log Sequence Numbers for Backups

This tip describes SQL Server Log Sequence Numbers (LSNs) and how the sequence numbers link full, differential and transaction log backups. We will look at sample backups and how these LSN values can be read from the backup files to determine the restore

Source: www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/

SQL: Get last queries executed on a server

Use this to see what you or others were recently doing.


USE [master]
GO
SELECT [execquery].[last_execution_time],
[execsql].1  
FROM [sys].[dm_exec_query_stats] AS [execquery]
CROSS APPLY [sys].[dm_exec_sql_text]([execquery].[sql_handle]) AS [execsql]
WHERE [last_execution_time] > DATEADD(HOUR, -1, GETDATE())
AND [execsql].1 LIKE '%execq%'
ORDER BY [execquery].[last_execution_time] DESC

 

SQL: Review Database File Growth

We can do this checking the default trace.


--
-- Review Database File Growth
--
DECLARE @trcfilename VARCHAR(1000);
SELECT @trcfilename = [path]
FROM [sys].[traces]
WHERE [is_default] = 1
SELECT [StartTime],
DB_NAME([databaseid]) AS [DatabaseName],
[Filename],
SUM(([IntegerData] * 8) / 1024) AS [Growth in MB],
([Duration] / 1000) AS [Duration in seconds],
RIGHT('0' + CAST(([Duration] / 1000) / 3600 AS VARCHAR), 2) + ':' + RIGHT('0'
+ CAST((([Duration] / 1000) / 60) % 60 AS VARCHAR),
2) + ':' + RIGHT('0'
+ CAST(([Duration] / 1000)
% 60 AS VARCHAR), 2) [TimeHHMMSS]
FROM ::
FN_TRACE_GETTABLE(@trcfilename, DEFAULT)
WHERE ([EventClass] = 92
OR [EventClass] = 93
)
--AND StartTime >= DATEADD(WEEK, -1,GETDATE())
GROUP BY [StartTime],
[Databaseid],
[Filename],
[IntegerData],
[Duration]
ORDER BY [StartTime]

SQL: Does Trim eliminate CRLF?

What will LTRIM and RTRIM do with CR LF or CRLF?


IF OBJECT_ID('tempdb.dbo.[#testtable]') IS NOT NULL
DROP TABLE [#testtable]

CREATE TABLE [#testtable] ([TestCRLF] VARCHAR(50))

INSERT INTO [#testtable]
([TestCRLF]
)
VALUES ('ThereIsNoSpace' + CHAR(13) + CHAR(10)
)
INSERT INTO [#testtable]
([TestCRLF]
)
VALUES ('ThereIsNoSpace' + CHAR(13)
)
INSERT INTO [#testtable]
([TestCRLF]
)
VALUES ('ThereIsNoSpace' + CHAR(10)
)

SELECT [TestCRLF] + 'Z' AS NoPostProcessing
FROM [#testtable]

SELECT LTRIM(RTRIM([TestCRLF])) + 'Z' AS Trims
FROM [#testtable]

SELECT RTRIM(REPLACE(REPLACE(REPLACE(REPLACE([TestCRLF], CHAR(13) + CHAR(10), ' '), CHAR(10) + CHAR(13), ' '), CHAR(13), ' '),
CHAR(10), ' ')) + 'Z' ReplaceAndTrim
FROM [#testtable]

Here’s what the output looks like:

Of course if we output to text the Z character would be in the next line. Since we are trying to clean up data with spaces or CRLF this shows a good demonstration of the problem.

(Thanks to Chris Trump!)

SQL Lessons: PowerShell basics

Part of my series pointing to valuable instruction on the web.

This one demonstrates the following functions by Chad Miller.

  • invoke-sqlcmd2
  • Write-DataTable
  • Out-DataTable
  • Add-SqlTable

Use PowerShell to Collect Server Data and Write to SQL

Learn about Windows PowerShell

Source: blogs.technet.microsoft.com/heyscriptingguy/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql/

SQL Lessons: SQL Server Configuration Manger

Part of my series pointing to valuable instruction on the web.

What Every Accidental DBA Needs to Know Now: SQL Server Configuration Manger

The SQL Server Configuration Manager is an critical but often overlooked application for proper SQL Server instance management and, well, “configuration”. In Tim Ford’s continuing series aimed at the Accidental Database Administrator he takes time to explain the basics of this tool, its use, and its features.

Source: m.sqlmag.com/database-security/what-every-accidental-dba-needs-know-now-sql-server-configuration-manger