Problem processing quotes by T-SQL during Job creation [closed]

Posted on

Question :

This is my first post ever in this website, which I found very helpful and instructive.

I need to execute a job with this step (which checks the mirroring state and send the result to me via Database Mail)

DECLARE @tab char(1) = CHAR(9);

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Mail',
    @recipients = '',
    @query = 'SET NOCOUNT ON;

SELECT CONVERT(NVARCHAR,, ISNULL(m.mirroring_state_desc, "")
FROM sys.databases d
LEFT JOIN sys.database_mirroring m ON m.database_id = d.database_id
WHERE m.mirroring_role_desc <> 'MIRROR'
    , @subject = 'Mirroring state'
    , @query_result_width = 80
    , @query_result_separator = @tab
    , @exclude_query_output = 0
    , @query_no_truncate = 0
    , @query_result_no_padding = 1
    , @attach_query_result_as_file = 1
    , @query_attachment_filename = 'CSV_Extract.csv';

The problem I have is that when I save the step, and try to execute the job it gives me a syntax error, because apparently T-SQL doesn’t process the quotes with the condition WHERE I put in there, the ‘MIRROR’ parameter.

How can i rewrite this code to make sure the parameter ‘MIRROR’ in the WHERE condition will be processed? (I managed to get it to work by deleting that line, but it sent me a result of all databases, even tempdb)

Answer :

Your code is nearly there, just change:

WHERE m.mirroring_role_desc <> 'MIRROR'


WHERE m.mirroring_role_desc <> ''MIRROR''

Jonathan Fite also mentions this nice QUOTENAME() solution in the comments below:

WHERE m.mirroring_role_desc <> ' + QUOTENAME(MIRROR, '''')

Leave a Reply

Your email address will not be published. Required fields are marked *