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