Results 1 to 2 of 2

Thread: sp_send_dbmail and QUOTED_IDENTIFIER

  1. #1
    Join Date
    Sep 2002
    Posts
    159

    sp_send_dbmail and QUOTED_IDENTIFIER

    Hi,
    saved evendata() itno table
    trying to send result as mail ,getting QUOTED_IDENTIFIER error
    without mail query executes fine

    What could be missing ?

    declare @query_1 varchar(8000)

    set @query_1 = 'select
    CAST(CAST(auditDescription.query(''/EVENT_INSTANCE/PostTime/text()'') AS VARCHAR(64)) AS DATETIME),
    CAST(auditDescription.query(''/EVENT_INSTANCE/EventType/text()'') AS VARCHAR(100)),
    CAST(auditDescription.query(''/EVENT_INSTANCE/LoginName/text()'') AS VARCHAR(100)),
    CAST(auditDescription.query(''/EVENT_INSTANCE/HostName/text()'') AS VARCHAR(100)),
    CAST(auditDescription.query(''/EVENT_INSTANCE/NTUserName/text()'') AS VARCHAR(100)),
    CAST(auditDescription.query(''/EVENT_INSTANCE/NTDomainName/text()'') AS VARCHAR(100)),
    CAST(CAST(auditDescription.query(''/EVENT_INSTANCE/Success/text()'') AS VARCHAR(64)) AS INTEGER) from dbo.DDLServerChangeTable'



    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'alex_G',
    @recipients = 'alex_G@boom.cooom',
    @query = @query_1


    Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
    Query execution failed: Msg 1934, Level 16, State 1, Server BOOM\SQL_2005, Line 1
    SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
    Msg 0, Level 11, State 0, Line 0
    A severe error occurred on the current command. The results, if any, should be discarded.

  2. #2
    Join Date
    Sep 2002
    Posts
    159
    found workaround

    declare @query_1 varchar(8000)

    set @query_1 = 'SET QUOTED_IDENTIFIER ON '
    set @query_1 = @query_1 + char(13)

    set @query_1 = @query_1 + '
    select top 1
    CAST(CAST(auditDescription.query(''/EVENT_INSTANCE/PostTime/text()'') AS VARCHAR(64)) AS DATETIME),
    CAST(auditDescription.query(''/EVENT_INSTANCE/EventType/text()'') AS VARCHAR(15)),
    CAST(auditDescription.query(''/EVENT_INSTANCE/LoginName/text()'') AS VARCHAR(20)),
    CAST(auditDescription.query(''/EVENT_INSTANCE/HostName/text()'') AS VARCHAR(20)),
    CAST(auditDescription.query(''/EVENT_INSTANCE/NTUserName/text()'') AS VARCHAR(20)),
    CAST(auditDescription.query(''/EVENT_INSTANCE/NTDomainName/text()'') AS VARCHAR(20)),
    CAST(CAST(auditDescription.query(''/EVENT_INSTANCE/Success/text()'') AS VARCHAR(64)) AS INTEGER) from dbo.DDLServerChangeTable
    order by id desc '

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'alex_G',
    @recipients = 'alex_G@boom.cooom',
    @query = @query_1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •