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

LVL 15
dbbishopAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mayank GairolaSr. Application Support Engg.Commented:
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
By the error you're missing a SET ANSI_PADDING ON before the CREATE TRIGGER.
0
dbbishopAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
dbbishopAuthor Commented:
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
0
dbbishopAuthor Commented:
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.
0
Scott PletcherSenior DBACommented:
Add the setting in the trigger itself.  I've tweaked minor other parts of the trigger as well.

USE [master]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [ResetRecoveryMode]
      ON ALL SERVER
      FOR ALTER_DATABASE
AS
    SET NOCOUNT ON;
    SET ANSI_PADDING ON;
            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
GO

ENABLE TRIGGER [ResetRecoveryMode] ON ALL SERVER
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbbishopAuthor Commented:
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.
0
Scott PletcherSenior DBACommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.