Insert results of linked server into temp table from SQL Server 2016 to SQL Server 2019

Question

SQL Server 2016 version:

Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64)   Aug  7 2021 01:20:37 Copyright (c) Microsoft Corporation  
Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

SQL Server 2019 version:

Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64)   Apr 11 2022 16:24:07   Copyright (C) 2019 Microsoft Corporation  
Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

I have multiple SQL Server 2016 and SQL Server 2019 instances. I need to gather information from all the servers into one big report. One server connects to all the others via a Linked Server and runs a query to build the report. This used to work perfectly when I only had SQL Server 2016 talking to each other, but when I started trying to query SQL Server 2019, it stopped working.

I was originally doing the query through OPENQUERY but this wasn't working from SQL Server 2016 to SQL Server 2019.

I changed the query so that I could execute it like this. This query works if I execute it from SQL Server 2016.:

EXEC [LinkedServerSQL2019].msdb..sp_executesql N'SELECT top 10   event_time, action_id, 
   succeeded, class_type, server_principal_name,target_database_principal_name, 
   database_name, object_name, statement, additional_information,t.host_name, 
   program_name
FROM [sys].[fn_get_audit_file] (
    ''FilePath*.sqlaudit'',
    NULL,
    NULL
) t 
LEFT JOIN sys.dm_exec_sessions e ON e.session_id=t.session_id 
                                 AND e.login_name = t.server_principal_name 
WHERE [event_time] >= dateadd(day,-1,GETUTCDATE())
  AND succeeded = 1  
  AND action_id = ''LGIS'' ';

But, when I try to get the results in a temp table, it stops working.

-- The Temp table is created on SQL Server 2016 
CREATE TABLE #TempDump 
(
    event_time datetime, 
    action_id varchar(100), 
    succeeded bit, 
    class_type varchar(10),  
    server_principal_name nvarchar(max),
    target_database_principal_name nvarchar(max), 
    database_name varchar(max), 
    object_name varchar(max), 
    statement varchar(max), 
    additional_information varchar(max), 
    host_name varchar(max), 
    program_name varchar(max)
)

INSERT INTO #TempDump
EXEC [LinkedServerSQL2019].msdb..sp_executesql N'SELECT top 10   event_time, action_id, 
  succeeded, class_type, server_principal_name,target_database_principal_name, 
  database_name, object_name, statement, additional_information,t.host_name, 
  program_name
FROM [sys].[fn_get_audit_file] (
    ''FilePath*.sqlaudit'',
    NULL,
    NULL
) t 
left join  sys.dm_exec_sessions e on e.session_id=t.session_id and 
   e.login_name=t.server_principal_name 
WHERE  [event_time] >= dateadd(day,-1,GETUTCDATE())
  and succeeded=1 and action_id=''LGIS'' ';

I get this message:

I don't understand why I'm able to execute the query on its own, and why I can't do the Insert in a temp table?

This works when it's SQL Server 2016 to SQL Server 2016, but it doesn't when it's SQL Server 2016 to SQL Server 2019.

Any ideas?

score 0 · Accepted Answer · answered Aug 23 '22 at 19:03

0

I'm adding the solution here in case it can help someone else.

You need to set the option Enable Promotion of Distributed Transactions for RPC to FALSE in the Linked Server Properties in order for this to work.

answered Aug 23 '22 at 19:03

Danielle Paquette-Harvey

1,691
1
16
31

Insert results of linked server into temp table from SQL Server 2016 to SQL Server 2019

1 Answers1