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 = 'xxxxxxxx@xxxxx.com',
    @query = 'SET NOCOUNT ON;

SELECT CONVERT(NVARCHAR,d.name), 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)

| improve this question | | | | |

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, '''')
| improve this answer | | | | |
  • its working, thank you , such a simpe modification i didn't notice , – Sfirita Feb 13 '18 at 11:57
  • No problem, it's an easy mistake to make when starting out with T-SQL. If you are happy with my answer, click the green V button next to it, to give me points :-) – Oreo Feb 13 '18 at 12:20
  • 2
    Instead of mucking about with double single quotes, I always prefer to use QUOTENAME. In addition to wrapping object names in brackets, you can specify quotemarks. So something like 'WHERE m.mirroring_role_desc <> ' + QUOTENAME(mirror, '''') – Jonathan Fite Feb 13 '18 at 13:01
  • I totally agree with you Jonathan when using objects. In the case above we are looking for the value MIRROR, not [MIRROR]. Edit: oh wait, I just saw the extra parameter in QUOTENAME, I have just learned a new thing! Thanks! – Oreo Feb 13 '18 at 14:13
  • @JonathanFite Using QUOTENAME with the '''' parameter is going to make my nested T-SQL's nested T-SQL's nested T-SQL look so much nicer, wow. Cheers m8. – Smörgåsbord Feb 13 '18 at 20:36

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