Tuesday, March 23, 2010

Strange Error after DDL Trigger

I created a DDL trigger on my server (SQL Server 2008 with SP1) to pick the database name which is listed below.

CREATE TRIGGER [ddlsvrtrg_create_database_backup_paths] ON ALL SERVER

FOR CREATE_DATABASE

AS

SET NOCOUNT ON

DECLARE @xmlEventData XML,

@database_name VARCHAR(50),@path varchar(500),@fullPath varchar(500),@diffpath varchar(500)

SET @xmlEventData = eventdata()

SELECT @database_name = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(150)')

.

.

.

After creating this trigger, I tried to create a database from the UI and you will get the following error.

TITLE: Microsoft SQL Server Management Studio

------------------------------

Create failed for Database 'DB_DDL'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. 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. (Microsoft SQL Server, Error: 1934)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=1934&LinkId=20476

------------------------------

BUTTONS:

OK

However, if I create a database from a script it will work. Also, I generated the script from the create database UI. That is still working.

Also, this issue does not exist in the SQL Server 2005 with SP3.

No comments:

Post a Comment