-
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.
-
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
-
Forum Rules
|
|