Service Broker SP not writing to a linked server RRS feed

  • Question

  • Hi,

    I'm trying to implement Asynchronous Triggers via Service Brokers using this setup:

    Script Center > Repository > Databases > Service Broker Sample: Asynchronous Triggers

    As far as I follow the scenario exactly (creating a trigger that converts the dataset of 'inserted' and 'deleted' tables into XML, passing this dataset to a stored pocedure via Service Broker, and eventually making that stored procedure use the inserted/deleted values for writing a transaction history table), it works as expected.

    But I need to write transaction history tables to a linked server (another machine running PostgreSQL). Now, I created that linked server with a test database and a table, connected to it, and could even make the above mentioned stored procedure write to the linked server, like:

    EXEC AdventureWorks2014.dbo.sp_department @inserted, @deleted

    But when I actually make an update to a table whose trigger passes data through to the Service Broker, nothing gets written to my linked server. Instead, I get an error "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."

    What could be wrong? Is it possible to use Service Broker to write to a table in a linked server?
    Monday, July 8, 2019 11:15 AM

All replies