asked on

SQL Server Error re: ANSI_PADDING in Server Trigger, Msg 1934

I have the following server-level trigger code that generates the error:

Msg 1934, Level 16, State 1, Procedure ResetRecoveryMode, Line 7
SELECT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. 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.

The condition under which I get this error is a script I have that will create a new database on the server, as well as create users and schemas in the 'myDatabase' database.

Any ideas?

USE [master]
GO

/****** Object:  DdlTrigger [ResetRecoveryMode]    Script Date: 7/9/2015 4:18:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [ResetRecoveryMode] ON ALL SERVER
	FOR ALTER_DATABASE
AS
	BEGIN 
		DECLARE	@tsql NVARCHAR(MAX); 
		DECLARE @Server SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(MAX)')
		SELECT	@tsql = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)'); 
		IF (( CHARINDEX('RECOVERY FULL', @tsql) > 0 ) OR ( CHARINDEX('RECOVERY BULK', @tsql) > 0 )) AND ( @Server = 'myDatabase' )
			BEGIN 
				EXECUTE msdb.dbo.sp_start_job 'Reset myDatabase Recovery Model';
			END 
	END;
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [ResetRecoveryMode] ON ALL SERVER
GO

Open in new window

Mayank Gairola

Vitor Montalvão

By the error you're missing a SET ANSI_PADDING ON before the CREATE TRIGGER.

D B

ASKER

I don't see that the link provides a reason for the error or a fix other than deleting the trigger (in which case, might as well not have it anyway. I am not doing any replication, which is what seems to be the cause in the link. Is this a bug?

I want to leave the trigger and fix the problem!

Vitor,

The ANSI_PADDING setting only applies to creating a table or adding a new column (from that point forward) or creating an indexed view (in which case it must be ON. This trigger is doing none of those, However, before making this post, I tried that to no avail. Typically you issue a SET ANSI_PADDING before, as I stated above, creating a table, adding a column to a table, or creating an indexed view.

Vitor Montalvão

I ran your code in my SQL Server instance and didn't get any error.
Can you check if this returns zero for all lines and columns?

SELECT 
	name,
    is_ansi_nulls_on,
    is_ansi_padding_on,
    is_ansi_warnings_on,
    is_arithabort_on,
    is_concat_null_yields_null_on,
    is_numeric_roundabort_on,
    is_quoted_identifier_on
FROM sys.databases

Open in new window

D B

ASKER

All zeros.

I did find that in my script hat builds the new database, the following statement generates one of the errors:
ALTER DATABASE myDatabase SET TRUSTWORTHY ON

D B

ASKER

I believe the other exception is being generated by a procedure the is being executed that does an ALTER DATABASE myDatabase ADD FILEGROUP xxxx
and/or
ALTER DATABASE myDatabase ADD FILE(...)

I would like to know how to 'fix' this without necessarily having to disable the trigger.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D B

ASKER

I am perplexed as I made a comment that that is what I did and it appeared to have fixed the error. However, it does not appear to have been posted, thus I will go ahead and accept your solution Scott, even though I'd already fixed it. It might help others who come across this problem.

Scott Pletcher

The ANSI_PADDING is not saved when a proc/trigger is created, whereas the ANSI_NULLS and QUOTED_IDENTIFIER settings are. Thus, to be sure of the correct setting, you must set it in the trigger itself. The setting will revert to its original value when the trigger ends, i.e., this setting is not permanent, it's just for the duration of the trigger.