April 7, 2016 at 12:47 am
Hi guys,
i'm creating a procedure with SET ANSI_WARNINGS OFF and inside this procedure i'm creating a table and inserting data in it.while i'm executing the procedure i'm getting error as below mentioned.i have created a server based trigger in my server if i disabled that trigger it is working while enable it is not working,In this trigger i'm inserting newly created or Altered table name and username. is there any relation between ANSI_WARNINGS and Server trigger.
Error Msg:
" Msg 1934, Level 16, State 1, Procedure %%, Line 34
SELECT failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'.
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 data type methods and/or spatial index operations. "
Trigger:
CREATE TRIGGER %%
ON ALL SERVER
WITH EXECUTE AS 'sa'
AFTER
CREATE_TABLE,ALTER_TABLE,DROP_TABLE,CREATE_VIEW, ALTER_VIEW, DROP_VIEW
, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
, CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
DECLARE @lEventType NVARCHAR(MAX)
DECLARE @lLoginName NVARCHAR(50)
DECLARE @lDatabaseName NVARCHAR(50)
DECLARE @lSchemaName NVARCHAR(50)
DECLARE @lObjectType NVARCHAR(100)
DECLARE @lObjectName NVARCHAR(150)
DECLARE @lTsqlCommand NVARCHAR(MAX)
DECLARE @lClientHost NVARCHAR(30)
DECLARE @lSpId SMALLINT
SELECT @lEventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(MAX)'),
@lLoginName = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(50)'),
@lDatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(50)'),
@lSchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(50)'),
@lObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'NVARCHAR(100)'),
@lObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(150)'),
@lTsqlCommand = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@lClientHost = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(30)'),
@lSpId = EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]', 'SMALLINT')"
This all variables will insert in to a table.
Can anybody please tell me why this error is getting.
Regards,
Vijay
April 7, 2016 at 8:48 am
It's as the error message indicates.
SELECT failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'.
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 data type methods[/i] and/or spatial index operations.
To use XML methods, which the trigger uses, ANSI_WARNINGS must be ON.
See https://technet.microsoft.com/en-us/library/ms188285(v=sql.105).aspx
Cheers!
April 7, 2016 at 9:57 am
CREATE TRIGGER %%
ON ALL SERVER
WITH EXECUTE AS 'sa'
AFTER
CREATE_TABLE,ALTER_TABLE,DROP_TABLE,CREATE_VIEW, ALTER_VIEW, DROP_VIEW
, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
, CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NOCOUNT ON;
SET NUMERIC_ROUNDABORT OFF;
SET XACT_ABORT ON;
--...rest of trigger as before...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply