2

I had a job sending an irrelevant email to a group of users.

The title of the email was Staging -> [AUPAIR] arrivalDate trigger issue.

When I query the jobs table in the msdb database using the title above, I got no answer:

USE [msdb]
GO
SELECT    j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON    js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON    s.srvid = j.originating_server_id
WHERE    js.command LIKE N'%Staging -> [AUPAIR] arrivalDate trigger issue%'

Only when I alter my query as below, could I find the bandit job:

USE [msdb]
GO
SELECT    j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON    js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON    s.srvid = j.originating_server_id
--WHERE    js.command LIKE N'Staging -> [AUPAIR] arrivalDate trigger issue%'
WHERE    js.command LIKE N'%Staging -> %'

This is what was the command on the jobstep:

if exists (
select count(1) as total
,year(modify_date) as [Year]
,month(modify_date) as [Month]
from repl_aupair r
where r.arrivaldate is not null
and junocore_applicationid not in(
select applicationid from junocore.dbo.replicationUpdateControl
where arrivaldatechanged = 1 )
and modify_Date >='2017-01-26 00:38:46.197'
group by year(modify_date),month(modify_date)
having year(modify_date) >= 2017
--order by [Year] desc
)
begin
EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'DBA',
            @recipients = 'my_emails@mycompany.com',
            @body = 'APIA_Repl_Sub.dbo.repl_aupair arrivalDate not null and replicationUpdateControl arrivalDateChanged still 0',
            @subject = 'Staging -> [AUPAIR] arrivalDate trigger issue ';
end

Why did my first query not work?

Conclusion:

I modified my script and the following two versions work!

After the answer by EzLo:

USE [msdb]
GO
SELECT  j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON  js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON  s.srvid = j.originating_server_id
WHERE js.command  LIKE '%Staging -> \[AUPAIR] arrivalDate trigger issue%' ESCAPE '\'


USE [msdb]
GO
SELECT  j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON  js.job_id = j.job_id
JOIN    master.dbo.sysservers s
    ON  s.srvid = j.originating_server_id
WHERE js.command  LIKE '%Staging -> [[]AUPAIR] arrivalDate trigger issue%'
8

The problem with your first query is the use of unescaped brackets with the LIKE operator.

When using the LIKE operator, there are a few special characters such as brackets that require escaping if you want to look for them. Characters enclosed in brackets will tell the SQL engine to look for a match between any character listed inside (same as regular expressions).

DECLARE @string VARCHAR(100) = 'ABC'

SELECT 'match!' WHERE @string LIKE '%[CX]%' -- Sting must have a C or an X
-- Result: match!

In your case, using [AUPAIR] inside the like will only match 1 letter (either A, U, P, I or R), and not the whole word.

DECLARE @string VARCHAR(100) = 'Staging -> [AUPAIR] arrivalDate trigger issue'

SELECT 'match!' WHERE @string LIKE '%Staging -> [AUPAIR] arrivalDate trigger issue%'
-- Result: (none)

This will however match with the following strings (just 1 letter!):

'Staging -> A arrivalDate trigger issue'
'Staging -> U arrivalDate trigger issue'
'Staging -> P arrivalDate trigger issue'
'Staging -> I arrivalDate trigger issue'
'Staging -> R arrivalDate trigger issue'

To correctly search for brackets, you can use 2 solutions:

  • Declare a custom ESCAPE character.

    DECLARE @string VARCHAR(100) = 'Staging -> [AUPAIR] arrivalDate trigger issue'
    
    SELECT 'match!' WHERE @string LIKE '%Staging -> \[AUPAIR] arrivalDate trigger issue%' ESCAPE '\'
    -- Result: match!
    
  • Enclose the opening bracket with brackets.

    DECLARE @string VARCHAR(100) = 'Staging -> [AUPAIR] arrivalDate trigger issue'
    
    SELECT 'match!' WHERE @string LIKE '%Staging -> [[]AUPAIR] arrivalDate trigger issue%'
    -- Result: match!
    

Please note that there is no need to escape the closing bracket since this one will only be interpreted as a LIKE special character as long as there is an opening one before it (which won't be if you escaped it).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.