• Thanks Gail, that pointed me in the right direction. Here is the result...

    Error message in SQL Error Log:

    SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

    The problem was when I created the log table I didn't use the SET ANSI_NULLS ON, SET QUOTED_IDENTIFIER ON options, even though I had tried including the options in the trigger itself.

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

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

    SET ANSI_NULLS ON

    SET QUOTED_IDENTIFIER ON

    drop table [ARRANTS].[dbo].[LOGON_AUDIT]

    CREATE TABLE [ARRANTS].[dbo].[LOGON_AUDIT](

    [Audit_ID] [int] IDENTITY(1,1) NOT NULL,

    [Server_Name] [varchar](255) NULL,

    [Login_Name] [varchar](255) NULL,

    [Login_Type] [varchar](255) NULL,

    [Client_Host] [varchar](255) NULL,

    [Application_Name] [varchar](255) NULL,

    [Post_Time] [datetime] NULL DEFAULT (getdate()),

    [Event_XML] [xml] NULL

    )

    select * from [ARRANTS].[dbo].[LOGON_AUDIT]

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

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

    Create TRIGGER [DR_AUDIT_LOGON_TRIGGER]

    ON ALL SERVER WITH EXECUTE AS 'sa'

    FOR LOGON

    AS

    BEGIN

    SET NOCOUNT ON

    SET ANSI_NULLS ON

    SET QUOTED_IDENTIFIER ON

    declare

    @xml XML,

    @PostTime datetime,

    @ServerName varchar(255),

    @LoginName varchar(255),

    @LoginType varchar(255),

    @ClientHost varchar(255),

    @app_name varchar(255)

    begin try

    set @xml = EVENTDATA()

    set @PostTime = convert(datetime, CONVERT(varchar, @xml.query('data(/EVENT_INSTANCE/PostTime)')))

    set @ServerName = CONVERT(varchar(255), @xml.query('data(/EVENT_INSTANCE/ServerName)'))

    set @LoginName = CONVERT(varchar(255), @xml.query('data(/EVENT_INSTANCE/LoginName)'))

    set @LoginType = CONVERT(varchar(255), @xml.query('data(/EVENT_INSTANCE/LoginType)'))

    set @ClientHost = CONVERT(varchar(255), @xml.query('data(/EVENT_INSTANCE/ClientHost)'))

    set @app_name = app_name()

    insert into [ARRANTS].dbo.LOGON_AUDIT

    (Server_Name, Login_Name, [Login_Type], Client_Host, Application_Name, Post_Time, Event_XML)

    select

    @ServerName, @LoginName, @LoginType, @ClientHost, @app_name, @PostTime, @xml

    end try

    begin catch

    print 'Error Message: ' + ERROR_MESSAGE()

    end catch

    END