Sep 13 2019 11:39 AM
We recently migrated a database from SQL 2008 to SQL 2016. In the database there is a trigger that executes a remote stored procedure which makes a call on a linked Oracle database. Similar to this:
BEGIN TRY
EXECUTE ( 'BEGIN REMOTE_CALL_API(IN_parm1 => ?, IN_parm2 => ?, IN_parm3 => ?, OUT_code1 => ? ); END;',
@in_parm1,
@in_parm2,
@in_parm3,
@out_code1
) AT ORACLEDB;
END TRY
When we run this on the 2008 server it works fine, but on the 2016 server the external call does not execute and fails. Are there any other special permissions that need to be set in 2016 to allow this to execute? We verified the connection to the linked database and if I run this as just a regular query, it runs fine, just not within the stored procedure.
Thanks!
Sep 23 2019 12:49 AM - edited Sep 23 2019 07:38 AM
EXEC requires to enable distributed transaction
see
https://stackoverflow.com/questions/6999906/distributed-transaction-error