Executing stored procedure via linked server through trigger

I have created a linked server on a SQL Server connecting to an Azure SQL datawarehouse.

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 stored procedure 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 stored procedures via trigger definitions

**Update :**The above issue was resolved based on suggestion provided by David.

Now I am in another issue 🙁

I will give you a context of what I am trying to accomplish :

I have a database server hosted in IaaS which also includes an MDS(Master data services) database along with normal one.

My intention is, whenever someone publishes a new data in an MDS table, an SP should be triggered in Azure SQL datawarehouse.

So I went with the below approach:

Create a linked server between IaaS SQL and Azure SQL DW
Create a trigger

I initially created a dummy trigger in a normal database in that server and tested out the linked server property change which you suggested and that worked as expected when I tried to insert a record into a table.

But when I followed the same process of creating a trigger on an MDS backend table, and when I am trying to insert a new records via UI explorer ; I am getting the error:

And unable to add any records.

Note: I am able to update existing records in UI but not add new records as I have created the trigger on Just Insert and not Update.

So is there any other property that I need to change ?

Note : The Is_trustworthy_On property is enabled on the MDS database already

Answer

Like the error says, you can’t enlist the remote proc execution in your trigger’s transaction. So try

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

Advertisement

Answer