Executing Stored Procedure via Linked server

Question

Hey,
I have created a linked server on a SQL server connecting to Azure SQL datawarehouse.

USE [master]  
GO  
  
/****** Object:  LinkedServer [LinkedserverTest]    Script Date: 1/27/2022 4:05:46 AM ******/  
EXEC master.dbo.sp_addlinkedserver @server = N'LinkedserverTest', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'AZserver.database.windows.net', @catalog=N'AEDW'  
 /* For security reasons the linked server remote logins password is changed with ######## */  
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedserverTest',@useself=N'False',@locallogin=NULL,@rmtuser=N'SQLACcount',@rmtpassword='########'  
  
GO  
  
EXEC master.dbo.sp_serveroption @server=N'LinkedserverTest', @optname=N'collation compatible', @optvalue=N'false'  
GO  
  
EXEC master.dbo.sp_serveroption @server=N'LinkedserverTest', @optname=N'data access', @optvalue=N'true'  
GO  
  
EXEC master.dbo.sp_serveroption @server=N'LinkedserverTest', @optname=N'dist', @optvalue=N'false'  
GO  
  
EXEC master.dbo.sp_serveroption @server=N'LinkedserverTest', @optname=N'pub', @optvalue=N'false'  
GO  
  
EXEC master.dbo.sp_serveroption @server=N'LinkedserverTest', @optname=N'rpc', @optvalue=N'true'  
GO  
  
EXEC master.dbo.sp_serveroption @server=N'LinkedserverTest', @optname=N'rpc out', @optvalue=N'true'  
GO  
  
EXEC master.dbo.sp_serveroption @server=N'LinkedserverTest', @optname=N'sub', @optvalue=N'false'  
GO  
  
EXEC master.dbo.sp_serveroption @server=N'LinkedserverTest', @optname=N'connect timeout', @optvalue=N'0'  
GO  
  
EXEC master.dbo.sp_serveroption @server=N'LinkedserverTest', @optname=N'collation name', @optvalue=null  
GO  
  
EXEC master.dbo.sp_serveroption @server=N'LinkedserverTest', @optname=N'lazy schema validation', @optvalue=N'false'  
GO  
  
EXEC master.dbo.sp_serveroption @server=N'LinkedserverTest', @optname=N'query timeout', @optvalue=N'0'  
GO  
  
EXEC master.dbo.sp_serveroption @server=N'LinkedserverTest', @optname=N'use remote collation', @optvalue=N'true'  
GO  
  
EXEC master.dbo.sp_serveroption @server=N'LinkedserverTest', @optname=N'remote proc transaction promotion', @optvalue=N'true'  
GO

When I execute a stored procedure of SQL DW via SSMS as below, it is getting executed as expected.

Exec Linkedservername,Databasename.dbo.spname

But when I try to have that SP executed as a part of trigger definition

CREATE TRIGGER [TriggerTest]  
  
    ON [dbo].[TriggerTest]  
  
    AFTER INSERT    
  
    AS  
  
    BEGIN  
  
	exec LSName.databasenm.dbo.SPname  
	end

And when I try to insert a record into the table, I get the below error:

Msg 596, Level 21, State 1, Line 18
Cannot continue the execution because the session is in the kill state.
Msg 46710, Level 20, State 1, Line 19
Unsupported transaction manager request 0 encountered. SQL Server Parallel DataWarehousing TDS endpoint only supports local transaction request for 'begin/commit/rollback'.

So am i missing anything or it is not possible to trigger cross database SPs via trigger definations

Accepted Answer

When you are in a trigger, you are inside a transaction defined by the statement that fired the trigger. By default, SQL Server attempts to promote local transactions to be a distributed transaction. As we can tell from the error message, this is not possible in this case, so you need to turn it off:

EXEC sp_serveroption @server=N'LinkedserverTest', @optname=N'remote proc transaction promotion', @optvalue=N'false'

Executing Stored Procedure via Linked server

0 additional answers