创建SQL作业时T-SQL引号处理问题求助

Hey there! Let’s fix that syntax error you’re running into with your database mirroring check job. The issue with the 'MIRROR' parameter in your WHERE clause almost always boils down to how you’re handling single quotes in T-SQL—especially if you’re embedding the query inside a Database Mail call or dynamic SQL.

First, let’s clarify the root cause: In T-SQL, when you wrap a string in single quotes, any single quote inside that string needs to be escaped using two consecutive single quotes (''). If you don’t do this, SQL Server thinks the string ends early, throwing a syntax error.

Example of the Wrong Approach (What’s Causing Your Error) #

If your original code looked something like this (using sp_send_dbmail with a query parameter), the inner 'MIRROR' quote clashes with the outer quotes wrapping the @query string:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YourMailProfile',
    @recipients = 'your@email.com',
    @subject = 'DB Mirror Status',
    @query = 'SELECT DB_NAME(database_id), mirroring_state_desc FROM sys.database_mirroring WHERE mirroring_role_desc = 'MIRROR'';

Fixed Code: Escape the Inner Single Quotes #

Simply replace the single ' around MIRROR with two '':

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YourMailProfile',
    @recipients = 'your@email.com',
    @subject = 'DB Mirror Status',
    @query = 'SELECT DB_NAME(database_id), mirroring_state_desc FROM sys.database_mirroring WHERE mirroring_role_desc = ''MIRROR''';

Alternative: Use Dynamic SQL with QUOTENAME #

If you’re building the query dynamically (e.g., storing it in a variable), you can use the QUOTENAME function to handle the quoting automatically. This is especially useful if you’re working with variable values:

DECLARE @Query NVARCHAR(MAX);
SET @Query = 'SELECT DB_NAME(database_id), mirroring_state_desc FROM sys.database_mirroring WHERE mirroring_role_desc = ' + QUOTENAME('MIRROR', '''');

-- Execute the query or pass it to sp_send_dbmail
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YourMailProfile',
    @recipients = 'your@email.com',
    @subject = 'DB Mirror Status',
    @query = @Query;

Full Example for Your Job Step #

Here’s a complete, robust script you can use in your job:

-- Declare variables to hold status results
DECLARE @MirrorStatus NVARCHAR(MAX);

-- Fetch mirror status for databases in MIRROR role (escaped quotes)
SELECT @MirrorStatus = COALESCE(@MirrorStatus + CHAR(13) + CHAR(10), '')
    + 'Database: ' + DB_NAME(database_id) 
    + ' | State: ' + ISNULL(mirroring_state_desc, 'Not Mirrored')
    + ' | Role: ' + ISNULL(mirroring_role_desc, 'N/A')
FROM sys.database_mirroring
WHERE mirroring_role_desc = ''MIRROR''; -- Escaped single quotes here

-- Handle case where no mirror databases exist
IF @MirrorStatus IS NULL
    SET @MirrorStatus = 'No databases found in MIRROR role.';

-- Send the status email
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YourMailProfile',
    @recipients = 'your@email.com',
    @subject = 'Daily Database Mirror Status Check',
    @body = @MirrorStatus;

This should eliminate the syntax error and correctly return the mirror status results to your email.

内容的提问来源于stack exchange，提问作者Sfirita