August 4, 2017 at 8:41 am
I have a stored procedure that I have created to pull some XML data from an Extended Event that I have created to capture failed logins. The procedure creates just fine but when I run the procedure, I get the following error:
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 517 [Batch Start Line 0]
Query execution failed: Msg 1934, Level 16, State 1, Server HOSQLDBDVM01\HINSTDV01, 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 filtered indexes and/or query notifications and/or XML d
ata type methods and/or spatial index operations.
I realize this issue has to do with the setting for the QUOTED_IDENTIFIER. Everything I have read says that is must be ON and that must be set BEFORE the CREATE PROCEDURE statement because it is done at parse time. I have tried everything I can think of and I can't get it to work. If anyone can identify the issue, I would greatly appreciate the help. Here is the code for the procedure:USE [DBA]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Capture_Login_Failures]') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.Capture_Login_Failures
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Capture_Login_Failures]
AS
DECLARE @alert_msg nvarchar(max),
@alert_query nvarchar(max),
@alert_subject nvarchar(255),
@count int
CREATE TABLE #login_fail(
[host] varchar(2000),
varchar(2000),
[app] varchar(2000),
[text_desc] varchar(8000),
[logdate] datetime2,
[state] tinyint,
[error] int)
-- Determine if there have been any login failures in the last five minutes
;WITH event_data AS
(
SELECT data = CONVERT(XML, event_data)
FROM sys.fn_xe_file_target_read_file(
'C:\Failed_Logins*.xel',
'C:\Failed_Logins*.xem',
NULL, NULL
)
),
tabular AS
(
SELECT
[host] = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(4000)'),
= data.value('(event/action[@name="nt_user_name"]/value)[1]','nvarchar(4000)'),
[app] = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(4000)'),
[date/time] = data.value('(event/@timestamp)[1]','datetime2'),
[error] = data.value('(event/data[@name="error_number"]/value)[1]','int'),
[state] = data.value('(event/data[@name="state"]/value)[1]','tinyint'),
[message] = data.value('(event/data[@name="message"]/value)[1]','nvarchar(250)')
FROM event_data
)
SELECT @count = COUNT(*)
FROM tabular
WHERE error = 18456
AND DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [date/time]) > DATEADD(mi, -5, getdate())
IF @count >= 5
BEGIN
-- Send e-mail to SQL Admins
SET @alert_subject = @@SERVERNAME + ' - Login Failure Alert'
SET @alert_msg = 'There have been at least five login failures in the last five minutes. '
SET @alert_msg = @alert_msg + 'Please take a look at this server to investigate why there are so '
SET @alert_msg = @alert_msg + 'many login failures and please take the appropriate action.'
SET @alert_msg = @alert_msg + CHAR(13) + CHAR(13)
SET @alert_query = ';WITH event_data AS '
SET @alert_query = @alert_query + '( '
SET @alert_query = @alert_query + 'SELECT data = CONVERT(XML, event_data) '
SET @alert_query = @alert_query + 'FROM sys.fn_xe_file_target_read_file( '
SET @alert_query = @alert_query + '''C:\Failed_Logins*.xel'', '
SET @alert_query = @alert_query + '''C:\Failed_Logins*.xem'', '
SET @alert_query = @alert_query + 'NULL, NULL '
SET @alert_query = @alert_query + ') '
SET @alert_query = @alert_query + '), '
SET @alert_query = @alert_query + 'tabular AS '
SET @alert_query = @alert_query + '( '
SET @alert_query = @alert_query + 'SELECT '
SET @alert_query = @alert_query + '[host] = data.value(''(event/action[@name="client_hostname"]/value)[1]'',''nvarchar(4000)''), '
SET @alert_query = @alert_query + ' = data.value(''(event/action[@name="nt_user_name"]/value)[1]'',''nvarchar(4000)''), '
SET @alert_query = @alert_query + '[app] = data.value(''(event/action[@name="client_app_name"]/value)[1]'',''nvarchar(4000)''), '
SET @alert_query = @alert_query + '[date/time] = data.value(''(event/@timestamp)[1]'',''datetime2''), '
SET @alert_query = @alert_query + '[error] = data.value(''(event/data[@name="error_number"]/value)[1]'',''int''), '
SET @alert_query = @alert_query + '[state] = data.value(''(event/data[@name="state"]/value)[1]'',''tinyint''), '
SET @alert_query = @alert_query + '[message] = data.value(''(event/data[@name="message"]/value)[1]'',''nvarchar(250)'') '
SET @alert_query = @alert_query + 'FROM event_data '
SET @alert_query = @alert_query + ') '
SET @alert_query = @alert_query + 'SELECT [host],,[app],[message],DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [date/time]),[state],[error] '
SET @alert_query = @alert_query + 'FROM tabular '
SET @alert_query = @alert_query + 'WHERE error = 18456 '
SET @alert_query = @alert_query + 'AND DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [date/time]) > DATEADD(mi, -5, getdate()) '
SET @alert_query = @alert_query + 'ORDER BY DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [date/time]) DESC'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Database Team', @recipients = 'me@me.com',
@subject = @alert_subject, @body = @alert_msg, @importance = 'High',
@query = @alert_query, @execute_query_database = 'DBA',
@query_result_header = 0
END
SELECT @alert_query
-- Clean up
DROP TABLE #login_fail
DELETE dbo.login_failure_log
WHERE log_date < DATEADD(dd, -90, getdate())
GO
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
August 4, 2017 at 11:21 am
i think it is the query that is passed to the send mail task...that query gets executed in a different context.
change this:SET @alert_query = ';WITH event_data AS '
to this:SET @alert_query = ' SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
;WITH event_data AS '
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply