SQL Server EVENTDATA.ApplicationName vs APP_NAME()

-1 votes
1 answer
41 views
                          I have created the following test environment:

    USE [OmegaCoreAudit]
    GO
    
    /****** Object:  Table [dbo].[TEST_TRAIL]    Script Date: 6/9/2025 8:02:50 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[TEST_TRAIL](
    	[TIMESTAMP_STS] [datetime2](7) NOT NULL,
    	[APP_01] [nvarchar](200) NULL,
    	[APP_02] [nvarchar](200) NULL,
    	[SQL_TEXT] [nvarchar](2000) NULL,
    	[LOGIN_NAME] [nvarchar](200) NULL,
    	[MODE] [nvarchar](50) NULL
    ) ON [PRIMARY]
    GO
    
    USE [master]
    GO
    
    /****** Object:  DdlTrigger [ALL_SRV_LOG]    Script Date: 6/9/2025 7:39:42 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER TRIGGER [ALL_SRV_LOG]
    ON ALL SERVER WITH EXECUTE AS 'sa'
    FOR LOGON
    AS
    
    BEGIN
    
    DECLARE @EventData XML; 
    declare @v_app_name_01 nvarchar(128);
    declare @v_sql_text nvarchar(2000);
    declare @v_app_name_02 nvarchar(128);
    
    SET @EventData = EVENTDATA();
    set @v_app_name_01	= @EventData.value('(/EVENT_INSTANCE/ApplicationName)', 'nvarchar(128)');
    set @v_sql_text		= @EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(2000)');
    
    set @v_app_name_02 = APP_NAME();
    
    insert into [OmegaCoreAudit].dbo.[TEST_TRAIL]
    (TIMESTAMP_STS, app_01, app_02, sql_text, login_name, mode)
    values
    (CURRENT_TIMESTAMP, @v_app_name_01, @v_app_name_02, @v_sql_text, ORIGINAL_LOGIN(), 'LOG') ;
    
    END	
    
    USE [master]
    GO
    
    /****** Object:  DdlTrigger [ALL_SRV_DDL]    Script Date: 6/9/2025 7:21:26 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER TRIGGER [ALL_SRV_DDL]
    ON ALL SERVER WITH EXECUTE AS 'sa'
    FOR DDL_EVENTS
    AS
    
    BEGIN
    
    DECLARE @EventData XML; 
    declare @v_app_name_01 nvarchar(128);
    declare @v_sql_text nvarchar(2000);
    declare @v_app_name_02 nvarchar(128);
    
    SET @EventData = EVENTDATA();
    set @v_app_name_01	= @EventData.value('(/EVENT_INSTANCE/ApplicationName)', 'nvarchar(128)');
    set @v_sql_text		= @EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(2000)');
    
    set @v_app_name_02 = APP_NAME();
    
    insert into [OmegaCoreAudit].dbo.[TEST_TRAIL]
    (TIMESTAMP_STS, app_01, app_02, sql_text, login_name, mode)
    values
    (CURRENT_TIMESTAMP, @v_app_name_01, @v_app_name_02, @v_sql_text, ORIGINAL_LOGIN(), 'DDL') ;
    
    END

	

Left it for a while, and when queried the table, noticed that fields:

APP_01 (populated by EventData.ApplicationName) - is always null.
APP_02 (populated by APP_NAME()) - is populated.

I can understand that for LOGON events the EventData.ApplicationName gives no value, as in the following URL:
https://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd 

... this is somehow indicated.

Question:
Why on DDL_EVENT the APP_01 is always empty (while APP_NAME() does give a value)?

best regards

Altin
                        
Asked by altink (129 rep)
Jun 9, 2025, 06:13 PM
Last activity: Jun 9, 2025, 10:35 PM
SQL Server EVENTDATA.ApplicationName vs APP_NAME()

Related Questions
