How to record the command or stored produre called by linked server

Question

How to record the command or stored produre called by linked server

kingster

Old Hand

Points: 331
More actions
October 13, 2022 at 1:39 am

#4098922
For example :
ServerA calls SP:SPA, inside SPA, there is comand like exec [172.26.*.*].dbo.testSP '***'
On ServerA， how to record like below
ServerIP, SPName
172.26.*.* SPA
Thanks.
- This topic was modified 2 years, 1 month ago by kingster.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Is modifying SPA so that it writes to a table whenever the linked server is used an option? If not, have you tried setting up an audit?

John

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

Extended Events are your bestest friend. Specifically, in addition to the normal query captures of rpc_completed and sql_batch_completed, you can look at ole_db_data_reader to see remote queries specifically.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

O, this is a nice solution track.

It will need some fiddling with, because it spews a load of "ole%" events.

I've ended up with this to start discovering it all:

/*
Generate xE event session to trace remote queries action in a database

ref: https://www.sqlservercentral.com/forums/topic/how-to-record-the-command-or-stored-produre-called-by-linked-server#post-4099148
*/
Declare @CreateSession bit = 0 ;

Declare @DropSession bit = 0 ;

Declare @StartSession bit = 0 ;


Declare @Debug bit = 0 ;


/* Check for Linked Servers*/if NOT exists ( select * from sys.servers where server_id <> 0  )
BEGIN
throw 50000, 'This instance does not have Linked Servers' ,1 ;
END


if @CreateSession = 1
begin
---
set nocount on
Declare @Today char(8) = convert(char(8), getdate(),112 )
--Declare @TargetDbid int = db_id( @TargetDatabaseName )

-- Select @TargetDbid
Declare @TraceFileName Nvarchar(1000)
set @TraceFileName = 'C:\temp\DBA_XE_Capture_Remote_Queries_' + replace(replace(replace(convert(char(13),getdate(),121),'-',''),' ','_'),':','') + '_' -- + '.trc' wordt automatisch toegevoegd

declare @LogFolderName nvarchar(245) 

SET @LogFolderName = ''
/*
* Get SQLServer Errorlog path
*/Create table #tmpRegValues ([Value] varchar(50), [Data] varchar(1000))
insert into #tmpRegValues 
exec master..xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters' 

Select @LogFolderName = substring(Data, 3,datalength(Data) - charindex('\',reverse(Data)) - 2) 
from #tmpRegValues 
where Data like '-e%'

DROP TABLE #tmpRegValues

select @TraceFileName = replace(@TraceFileName,'C:\temp',@LogFolderName)

Print '@TraceFileName ' + @TraceFileName ;


Declare @DDL nvarchar(max) = N'
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = ''DBA_Capture_Remote_Queries'')
      DROP EVENT SESSION [DBA_Capture_Remote_Queries] ON SERVER;

CREATE EVENT SESSION [DBA_Capture_Remote_Queries]
ON SERVER
ADD EVENT sqlserver.rpc_completed(
ACTION 
(
  sqlserver.client_app_name-- ApplicationName from SQLTrace
, sqlserver.client_hostname-- HostName from SQLTrace
, sqlserver.database_id-- DatabaseID from SQLTrace
, sqlserver.nt_username-- NTDomainName from SQLTrace
-- NTUserName implemented by another Action in XE already
, sqlserver.server_instance_name-- ServerName from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
-- DBUserName not implemented in XE for this event
-- EndTime implemented by another Action in XE already
-- StartTime implemented by another Action in XE already
)
WHERE 
(
sqlserver.client_app_name NOT LIKE ''%SQL Profiler%''
)
),
ADD EVENT sqlserver.rpc_starting(
ACTION 
(
  sqlserver.client_app_name-- ApplicationName from SQLTrace
, sqlserver.client_hostname-- HostName from SQLTrace
, sqlserver.database_id-- DatabaseID from SQLTrace
, sqlserver.nt_username-- NTDomainName from SQLTrace
, sqlserver.server_instance_name-- ServerName from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
)
WHERE 
(
sqlserver.client_app_name NOT LIKE ''%SQL Profiler%''
)
),
ADD EVENT sqlserver.sql_batch_completed(
ACTION 
(
  sqlserver.client_app_name-- ApplicationName from SQLTrace
, sqlserver.client_hostname-- HostName from SQLTrace
, sqlserver.database_id-- DatabaseID from SQLTrace
, sqlserver.nt_username-- NTDomainName from SQLTrace
, sqlserver.server_instance_name-- ServerName from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
)
WHERE 
(
sqlserver.client_app_name NOT LIKE ''%SQL Profiler%''
)
),
ADD EVENT sqlserver.sql_batch_starting(
ACTION 
(
  sqlserver.client_app_name-- ApplicationName from SQLTrace
, sqlserver.client_hostname-- HostName from SQLTrace
, sqlserver.database_id-- DatabaseID from SQLTrace
, sqlserver.nt_username-- NTDomainName from SQLTrace
, sqlserver.server_instance_name-- ServerName from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
)
WHERE 
(
sqlserver.client_app_name NOT LIKE ''%SQL Profiler%''
)
),
ADD EVENT sqlserver.login(
ACTION 
(
  sqlserver.client_app_name-- ApplicationName from SQLTrace
, sqlserver.client_hostname-- HostName from SQLTrace
, sqlserver.nt_username-- NTDomainName from SQLTrace
, sqlserver.server_instance_name-- ServerName from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
)
WHERE 
(
sqlserver.client_app_name NOT LIKE ''%SQL Profiler%''
)
),
ADD EVENT sqlserver.logout(
ACTION 
(
  sqlserver.client_app_name-- ApplicationName from SQLTrace
, sqlserver.client_hostname-- HostName from SQLTrace
, sqlserver.database_id-- DatabaseID from SQLTrace
, sqlserver.nt_username-- NTDomainName from SQLTrace
, sqlserver.server_instance_name-- ServerName from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
)
WHERE 
(
sqlserver.client_app_name NOT LIKE ''%SQL Profiler%''
)
),
ADD EVENT sqlserver.existing_connection(
ACTION 
(
  sqlserver.client_app_name-- ApplicationName from SQLTrace
, sqlserver.client_hostname-- HostName from SQLTrace
, sqlserver.nt_username-- NTDomainName from SQLTrace
, sqlserver.server_instance_name-- ServerName from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
)
WHERE 
(
sqlserver.client_app_name NOT LIKE ''%SQL Profiler%''
)
),
ADD EVENT sqlserver.module_start(
ACTION 
(
  sqlserver.database_id-- DatabaseID from SQLTrace
, sqlserver.database_name-- DatabaseName from SQLTrace
, sqlserver.server_instance_name-- ServerName from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
)
),
ADD EVENT sqlserver.module_end(
ACTION 
(
  sqlserver.database_id-- DatabaseID from SQLTrace
, sqlserver.database_name-- DatabaseName from SQLTrace
, sqlserver.server_instance_name-- ServerName from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
)
),
ADD EVENT sqlserver.oledb_error(
ACTION 
(
  sqlserver.client_app_name-- ApplicationName from SQLTrace
, sqlserver.client_hostname-- HostName from SQLTrace
, sqlserver.client_pid-- ClientProcessID from SQLTrace
, sqlserver.database_id-- DatabaseID from SQLTrace
, sqlserver.database_name-- DatabaseName from SQLTrace
, package0.event_sequence-- EventSequence from SQLTrace
, sqlserver.is_system-- IsSystem from SQLTrace
, sqlserver.nt_username-- NTDomainName from SQLTrace
, sqlserver.request_id-- RequestID from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.server_principal_sid-- LoginSid from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
, sqlserver.session_server_principal_name-- SessionLoginName from SQLTrace
, sqlserver.transaction_id-- TransactionID from SQLTrace
)
WHERE 
(
sqlserver.client_app_name NOT LIKE ''%SQL Profiler%''
)
),

ADD EVENT sqlserver.oledb_call(
ACTION 
(
  sqlserver.client_app_name-- ApplicationName from SQLTrace
, sqlserver.client_hostname-- HostName from SQLTrace
, sqlserver.client_pid-- ClientProcessID from SQLTrace
, sqlserver.database_id-- DatabaseID from SQLTrace
, sqlserver.database_name-- DatabaseName from SQLTrace
, package0.event_sequence-- EventSequence from SQLTrace
, sqlserver.is_system-- IsSystem from SQLTrace
, sqlserver.nt_username-- NTDomainName from SQLTrace
, sqlserver.request_id-- RequestID from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.server_principal_sid-- LoginSid from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
, sqlserver.session_server_principal_name-- SessionLoginName from SQLTrace
, sqlserver.transaction_id-- TransactionID from SQLTrace
)
WHERE 
(
sqlserver.client_app_name NOT LIKE ''%SQL Profiler%''
)
),
ADD EVENT sqlserver.oledb_data_read(
ACTION 
(
  sqlserver.client_app_name-- ApplicationName from SQLTrace
, sqlserver.client_hostname-- HostName from SQLTrace
, sqlserver.client_pid-- ClientProcessID from SQLTrace
, sqlserver.database_id-- DatabaseID from SQLTrace
, sqlserver.database_name-- DatabaseName from SQLTrace
, package0.event_sequence-- EventSequence from SQLTrace
, sqlserver.is_system-- IsSystem from SQLTrace
, sqlserver.nt_username-- NTDomainName from SQLTrace
, sqlserver.request_id-- RequestID from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.server_principal_sid-- LoginSid from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
, sqlserver.session_server_principal_name-- SessionLoginName from SQLTrace
, sqlserver.transaction_id-- TransactionID from SQLTrace
)
WHERE 
(
sqlserver.client_app_name NOT LIKE ''%SQL Profiler%''
)
),
ADD EVENT sqlserver.oledb_provider_information(
ACTION 
(
  sqlserver.client_app_name-- ApplicationName from SQLTrace
, sqlserver.client_hostname-- HostName from SQLTrace
, sqlserver.client_pid-- ClientProcessID from SQLTrace
, sqlserver.database_id-- DatabaseID from SQLTrace
, sqlserver.database_name-- DatabaseName from SQLTrace
, package0.event_sequence-- EventSequence from SQLTrace
, sqlserver.is_system-- IsSystem from SQLTrace
, sqlserver.nt_username-- NTDomainName from SQLTrace
, sqlserver.request_id-- RequestID from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.server_principal_sid-- LoginSid from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
, sqlserver.session_server_principal_name-- SessionLoginName from SQLTrace
, sqlserver.transaction_id-- TransactionID from SQLTrace
)
WHERE 
(
sqlserver.client_app_name NOT LIKE ''%SQL Profiler%''
)
),
ADD EVENT sqlserver.oledb_provider_initialized(
ACTION 
(
  sqlserver.client_app_name-- ApplicationName from SQLTrace
, sqlserver.client_hostname-- HostName from SQLTrace
, sqlserver.client_pid-- ClientProcessID from SQLTrace
, sqlserver.database_id-- DatabaseID from SQLTrace
, sqlserver.database_name-- DatabaseName from SQLTrace
, package0.event_sequence-- EventSequence from SQLTrace
, sqlserver.is_system-- IsSystem from SQLTrace
, sqlserver.nt_username-- NTDomainName from SQLTrace
, sqlserver.request_id-- RequestID from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.server_principal_sid-- LoginSid from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
, sqlserver.session_server_principal_name-- SessionLoginName from SQLTrace
, sqlserver.transaction_id-- TransactionID from SQLTrace
)
WHERE 
(
sqlserver.client_app_name NOT LIKE ''%SQL Profiler%''
)
),
ADD EVENT sqlserver.oledb_query_interface(
ACTION 
(
  sqlserver.client_app_name-- ApplicationName from SQLTrace
, sqlserver.client_hostname-- HostName from SQLTrace
, sqlserver.client_pid-- ClientProcessID from SQLTrace
, sqlserver.database_id-- DatabaseID from SQLTrace
, sqlserver.database_name-- DatabaseName from SQLTrace
, package0.event_sequence-- EventSequence from SQLTrace
, sqlserver.is_system-- IsSystem from SQLTrace
, sqlserver.nt_username-- NTDomainName from SQLTrace
, sqlserver.request_id-- RequestID from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.server_principal_sid-- LoginSid from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
, sqlserver.session_server_principal_name-- SessionLoginName from SQLTrace
, sqlserver.transaction_id-- TransactionID from SQLTrace
)
WHERE 
(
sqlserver.client_app_name NOT LIKE ''%SQL Profiler%''
)
),
ADD EVENT sqlserver.deprecation_final_support(
ACTION 
(
  sqlserver.client_app_name-- ApplicationName from SQLTrace
, sqlserver.client_hostname-- HostName from SQLTrace
, sqlserver.client_pid-- ClientProcessID from SQLTrace
, sqlserver.database_id-- DatabaseID from SQLTrace
, sqlserver.database_name-- DatabaseName from SQLTrace
, package0.event_sequence-- EventSequence from SQLTrace
, sqlserver.is_system-- IsSystem from SQLTrace
, sqlserver.nt_username-- NTDomainName from SQLTrace
, sqlserver.request_id-- RequestID from SQLTrace
, sqlserver.server_instance_name-- ServerName from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.server_principal_sid-- LoginSid from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
, sqlserver.session_server_principal_name-- SessionLoginName from SQLTrace
, sqlserver.transaction_id-- TransactionID from SQLTrace
, sqlserver.transaction_sequence-- XactSequence from SQLTrace
, sqlserver.tsql_frame-- IntegerData2 from SQLTrace
)
WHERE 
(
sqlserver.client_app_name NOT LIKE ''%SQL Profiler%''
)
),
ADD EVENT sqlserver.xml_deadlock_report(
ACTION 
(
  package0.event_sequence-- EventSequence from SQLTrace
, sqlserver.is_system-- IsSystem from SQLTrace
, sqlserver.server_instance_name-- ServerName from SQLTrace
, sqlserver.server_principal_name-- LoginName from SQLTrace
, sqlserver.server_principal_sid-- LoginSid from SQLTrace
, sqlserver.session_id-- SPID from SQLTrace
, sqlserver.session_server_principal_name-- SessionLoginName from SQLTrace
, sqlserver.transaction_id-- TransactionID from SQLTrace
)
)
ADD TARGET package0.event_file(SET filename=N'''+@TraceFileName+'_.xel'',max_file_size=(5),max_rollover_files=(1))
WITH (MAX_MEMORY=4096 KB
,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY=30 SECONDS
,MAX_EVENT_SIZE=0 KB
,MEMORY_PARTITION_MODE=NONE
,TRACK_CAUSALITY=OFF
,STARTUP_STATE=OFF)
Print ''xEvent SESSION [DBA_Capture_Remote_Queries] created'';
Print ''/* start / stop xE session */ALTER EVENT SESSION [DBA_Capture_Remote_Queries] ON SERVER STATE = START;

ALTER EVENT SESSION [DBA_Capture_Remote_Queries] ON SERVER STATE = STOP;
''

if '+convert(char(1), @StartSession)+' = 1 
begin
ALTER EVENT SESSION [DBA_Capture_Remote_Queries] ON SERVER STATE = START;
end
'
if @Debug = 1 
begin
Select convert(xml, @DDL ) as DDL ;
end

exec sp_executesql @stmt = @DDL ;

end
else if @DropSession = 1 
begin
Declare @DropDDL nvarchar(max) = N'
if exists (Select *
from sys.server_event_sessions AS XS
WHERE XS.name = ''DBA_Capture_Remote_Queries''
)
begin
DROP EVENT SESSION [DBA_Capture_Remote_Queries] ON SERVER 
print ''xE SESSION [DBA_Capture_Remote_Queries] on server dropped''
end'
if @Debug = 1 
begin
Select convert(xml, @DropDDL ) as DropDDL
end

exec sp_executesql @stmt = @DropDDL ;

end

else

begin

-- Process xe trace results

SET NOCOUNT ON;

if object_id('tempdb..#XELog') is null
begin
CREATE TABLE #XELog(
[timestamp_utc] [datetime2](7) NOT NULL PRIMARY KEY,
[file_name] [nvarchar](500)  NOT NULL,
[file_offset] [bigint] NOT NULL
)
end

if object_id('tempdb..#XeEventData') is null
begin
    CREATE TABLE #XeEventData
([event_data_XML]    [XML] NULL, 
[file_name]     [NVARCHAR](260)  NOT NULL, 
[file_offset]   [BIGINT] NOT NULL, 
[timestamp_utc] DATETIME2(7) NOT NULL
);

end
else
begin
truncate table #XeEventData ;
end

IF Object_id('tempdb..#Trace_XE_data') is null
BEGIN
/* drop TABLE #Trace_XE_data */CREATE TABLE #Trace_XE_data
(id_no INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
sample_time_utc DATETIME2(3) NOT NULL,
database_id int null, 
database_name VARCHAR(128)  NULL,
event_name VARCHAR(50) NOT NULL,
session_id SMALLINT  NULL,
duration BIGINT  NULL,
cpu_time BIGINT  NULL,
physical_reads BIGINT  NULL,
logical_reads BIGINT  NULL,
writes BIGINT  NULL,
row_count BIGINT  NULL,
client_app_name VARCHAR(128)  NULL,
client_host_name VARCHAR(128) NULL,
nt_username VARCHAR(128)  NULL,
sql_text varchar(max) NULL, 
FullStatement varchar(max) NULL,
batch_text varchar(max) NULL, 

[timestamp_Local] [datetime2](7) NULL,
[timestamp_UTC_STMT] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[is_cached] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[is_recovered] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[is_dac] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[packet_size] [int] NULL,
[options] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[options_text] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[server_principal_name] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[server_instance_name] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[connection_reset_option] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[object_name] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[data_stream] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[linked_server_name] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[provider_name] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[provider_clsid] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[transaction_id] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[session_server_principal_name] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[server_principal_sid] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[request_id] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[is_system] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[event_sequence] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[client_pid] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[opcode] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[method_name] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[parameters] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[properties] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

event_data_xml xml NULL,
Min_timestamp_utc datetime NULL,
Max_timestamp_utc datetime NULL
)
;

-- CREATE NONCLUSTERED INDEX xNCI_extended_events_data_sample_time_utc ON #Trace_XE_data (sample_time_utc) WITH (DATA_COMPRESSION = PAGE);
END

    DECLARE @XEFilename [NVARCHAR](500)
, @XEfileoffset BIGINT
, @wrkxml XML
, @XERootFileName NVARCHAR(4000)
, @RwCount int ;

/* get session target information */    SELECT TOP 1 @wrkxml = CONVERT(XML, XST.target_data)
FROM sys.dm_xe_session_targets AS XST
    INNER JOIN sys.dm_xe_sessions AS XS
            ON XS.address = XST.event_session_address
    WHERE XS.name = 'DBA_Capture_Remote_Queries';

Set @RwCount = @@rowcount ;

    SELECT @XERootFileName = @wrkxml.value('(EventFileTarget/File/@name)[1]', 'nvarchar(4000)');
/* only keep root part ( as used with the XE event creation ) */ 
    SELECT @XERootFileName = SUBSTRING(@XERootFileName, 0, CHARINDEX('__', @XERootFileName) + 1);

/* get last recorde XE Log info */    SELECT TOP (1) @XEFilename = [file_name]
                    , @XEfileoffset = file_offset
FROM #XELog
    ORDER BY timestamp_utc DESC;

Set @RwCount = @@rowcount ;

    IF @RwCount = 0
        BEGIN
            SELECT @XEFilename = NULL
                 , @XEfileoffset = NULL;
        END;
BEGIN TRY  

     INSERT INTO #XeEventData
            SELECT CONVERT(XML, event_data) AS event_data_XML
                , file_name
                , file_offset
                , timestamp_utc
            FROM sys.fn_xe_file_target_read_file(
  @XERootFileName + '*.xel'
, @XERootFileName + '*.xem'
, @XEFilename, @XEfileoffset);
if @Debug = 1 
begin
Select '1' as '#XeEventData', @@ROWCOUNT as nRows;
end

END TRY  
BEGIN CATCH  
 
 INSERT INTO #XeEventData
            SELECT CONVERT(XML, event_data) AS event_data_XML
                , file_name
                , file_offset
                , timestamp_utc
            FROM sys.fn_xe_file_target_read_file(
  @XERootFileName + '*.xel'
, @XERootFileName + '*.xem'
, NULL, NULL);


if @Debug = 1 
begin
Select '2' as '#XeEventData', @@ROWCOUNT as nRows;
end
END CATCH  

truncate table #XELog ;
    /* store last row for XE_Log */    INSERT INTO #XELog
            SELECT TOP (1) [timestamp_utc]
, [file_name]
                        , [file_offset]
            FROM #XeEventData
            ORDER BY [timestamp_utc] DESC;

if @Debug = 1 
begin
Select * 
from #XELog
end

truncate table #Trace_XE_data ;

INSERT INTO #Trace_XE_data
SELECT 
timestamp_utc,
event_data_xml.value('(event/action[@name="database_id"]/value)[1]', 'INT') AS database_id,
event_data_xml.value('(event/action[@name="database_name"]/value)[1]', 'SYSNAME') AS database_name,
event_data_xml.value('(event/@name)[1]', 'VARCHAR(50)') As event_name,
event_data_xml.value('(event/action[@name="session_id"]/value)[1]', 'SMALLINT') AS session_id,
event_data_xml.value('(event/data[@name="duration"]/value)[1]', 'BIGINT') AS duration,
event_data_xml.value('(event/data[@name="cpu_time"]/value)[1]', 'BIGINT') AS cpu_time,
event_data_xml.value('(event/data[@name="physical_reads"]/value)[1]', 'BIGINT') AS physical_reads,
event_data_xml.value('(event/data[@name="logical_reads"]/value)[1]', 'BIGINT') AS logical_reads,
event_data_xml.value('(event/data[@name="writes"]/value)[1]', 'BIGINT') AS writes,
event_data_xml.value('(event/data[@name="row_count"]/value)[1]', 'BIGINT') AS row_count,
event_data_xml.value('(event/action[@name="client_app_name"]/value)[1]', 'VARCHAR(128)') AS client_app_name,
event_data_xml.value('(event/action[@name="client_hostname"]/value)[1]', 'VARCHAR(128)') AS client_host_name,
isnull(event_data_xml.value('(event/action[@name="nt_username"]/value)[1]', 'SYSNAME'), '??') AS nt_username,
event_data_xml.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(max)') AS sql_text,
event_data_xml.value('(event/data[@name="statement"]/value)[1]', 'VARCHAR(max)') AS statement,
event_data_xml.value('(event/data[@name="batch_text"]/value)[1]', 'VARCHAR(max)') AS batch_text,

event_data_xml.value('(event/action[@name="timestamp"]/value)[1]', 'datetime2') AS timestamp_Local,
event_data_xml.value('(event/action[@name="timestamp (UTC)"]/value)[1]', 'VARCHAR(max)') AS timestamp_UTC_STMT,
event_data_xml.value('(event/action[@name="is_cached"]/value)[1]', 'VARCHAR(10)') AS is_cached,
event_data_xml.value('(event/action[@name="is_recovered"]/value)[1]', 'VARCHAR(10)') AS is_recovered,
event_data_xml.value('(event/action[@name="is_dac"]/value)[1]', 'VARCHAR(10)') AS is_dac,
event_data_xml.value('(event/action[@name="packet_size"]/value)[1]', 'int') AS packet_size,
event_data_xml.value('(event/action[@name="options"]/value)[1]', 'VARCHAR(max)') AS options,
event_data_xml.value('(event/action[@name="options_text"]/value)[1]', 'VARCHAR(max)') AS options_text,
event_data_xml.value('(event/action[@name="server_principal_name"]/value)[1]', 'VARCHAR(256)') AS server_principal_name,
event_data_xml.value('(event/action[@name="server_instance_name"]/value)[1]', 'VARCHAR(256)') AS server_instance_name,
event_data_xml.value('(event/action[@name="connection_reset_option"]/value)[1]', 'VARCHAR(256)') AS connection_reset_option,
event_data_xml.value('(event/action[@name="object_name"]/value)[1]', 'VARCHAR(256)') AS object_name,
event_data_xml.value('(event/data[@name="data_stream"]/value)[1]', 'VARCHAR(max)') AS data_stream,
event_data_xml.value('(event/data[@name="linked_server_name"]/value)[1]', 'VARCHAR(256)') AS linked_server_name,
event_data_xml.value('(event/data[@name="provider_name"]/value)[1]', 'VARCHAR(256)') AS provider_name,
event_data_xml.value('(event/data[@name="provider_clsid"]/value)[1]', 'VARCHAR(256)') AS provider_clsid,
event_data_xml.value('(event/action[@name="transaction_id"]/value)[1]', 'VARCHAR(max)') AS transaction_id,
event_data_xml.value('(event/action[@name="session_server_principal_name"]/value)[1]', 'VARCHAR(256)') AS session_server_principal_name,
event_data_xml.value('(event/action[@name="server_principal_sid"]/value)[1]', 'VARCHAR(max)') AS server_principal_sid,
event_data_xml.value('(event/action[@name="request_id"]/value)[1]', 'VARCHAR(max)') AS request_id,
event_data_xml.value('(event/action[@name="is_system"]/value)[1]', 'VARCHAR(10)') AS is_system,
event_data_xml.value('(event/action[@name="event_sequence"]/value)[1]', 'VARCHAR(max)') AS event_sequence,
event_data_xml.value('(event/action[@name="client_pid"]/value)[1]', 'VARCHAR(max)') AS client_pid,
event_data_xml.value('(event/data[@name="opcode"]/value)[1]', 'VARCHAR(max)') AS opcode,
event_data_xml.value('(event/data[@name="method_name"]/value)[1]', 'VARCHAR(max)') AS method_name,
event_data_xml.value('(event/data[@name="parameters"]/value)[1]', 'VARCHAR(max)') AS parameters,
event_data_xml.value('(event/data[@name="properties"]/value)[1]', 'VARCHAR(max)') AS properties,
event_data_xml.value('(event/data[@name="message"]/value)[1]', 'VARCHAR(max)') AS message,

event_data_xml
, min (timestamp_utc) over ( partition by 1 ) Min_timestamp_utc
, max (timestamp_utc) over ( partition by 1 ) Max_timestamp_utc


FROM #XeEventData;

/* Check remote queries */if exists ( select * from sys.servers where server_id <> 0  )
begin
;with cteRemoteOps as (
Select session_id, client_host_name, nt_username, linked_server_name, sum(duration) as OLEDuration, min(sample_time_utc) min_sample_time_utc, max(sample_time_utc) max_sample_time_utc
-- select * 
from #Trace_XE_data
where event_name like 'ole%'
group by session_id, client_host_name, nt_username, linked_server_name
)
Select top ( 100 ) @@servername SQLInstance
, T.*
, ROps.linked_server_name, Rops.OLEDuration
from #Trace_XE_data T
inner join cteRemoteOps ROps 
on ROps.session_id = T.session_id 
and ROps.client_host_name = T.client_host_name
and Rops.max_sample_time_utc = T.sample_time_utc
where T.event_name not like 'ole%'
order by id_no -- desc  ;

end

end

ps Keep in mind this xe session monitors for outgoing linked server calls.

This reply was modified 1 year, 6 months ago by Johan Bijnens.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

As a bit of a sidebar and without knowing the particulars, it's pretty much a worse practice to make calls within a stored procedure to an actual IP address. If that address ever changes, then you have to find all the code that explicitly used it and change all that code.

My recommendation is that you use a linked server for that and stick to a 2 part naming convention,

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

I've been testing a bit to detect such queries at the linked server itself ...

For me, it turns out the only actual way to detect it is when you have the linked server security set to to use a specific account.

In such case you can xe-trace for that account usage and work from there on.