SQL 2016 EXECUTE issue

%3CLINGO-SUB%20id%3D%22lingo-sub-854864%22%20slang%3D%22en-US%22%3ESQL%202016%20EXECUTE%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854864%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20recently%20migrated%20a%20database%20from%20SQL%202008%20to%20SQL%202016.%26nbsp%3B%20In%20the%20database%20there%20is%20a%20trigger%20that%20executes%20a%20remote%20stored%20procedure%20which%20makes%20a%20call%20on%20a%20linked%20Oracle%20database.%26nbsp%3B%20Similar%20to%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBEGIN%20TRY%3C%2FP%3E%3CP%3E%26nbsp%3B%20EXECUTE%20(%20'BEGIN%20REMOTE_CALL_API(IN_parm1%20%3D%26gt%3B%20%3F%2C%20IN_parm2%20%3D%26gt%3B%20%3F%2C%20IN_parm3%20%3D%26gt%3B%20%3F%2C%20OUT_code1%20%3D%26gt%3B%20%3F%20)%3B%20END%3B'%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%40in_parm1%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%40in_parm2%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%40in_parm3%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%40out_code1%3C%2FP%3E%3CP%3E%26nbsp%3B%20)%20AT%20ORACLEDB%3B%3C%2FP%3E%3CP%3EEND%20TRY%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20we%20run%20this%20on%20the%202008%20server%20it%20works%20fine%2C%20but%20on%20the%202016%20server%20the%20external%20call%20does%20not%20execute%20and%20fails.%26nbsp%3B%20Are%20there%20any%20other%20special%20permissions%20that%20need%20to%20be%20set%20in%202016%20to%20allow%20this%20to%20execute%3F%26nbsp%3B%20We%20verified%20the%20connection%20to%20the%20linked%20database%20and%20if%20I%20run%20this%20as%20just%20a%20regular%20query%2C%20it%20runs%20fine%2C%20just%20not%20within%20the%20stored%20procedure.%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-869800%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%202016%20EXECUTE%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869800%22%20slang%3D%22en-US%22%3E%3CP%3EEXEC%20requires%20to%20enable%20distributed%20transaction%3C%2FP%3E%3CP%3Esee%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F6999906%2Fdistributed-transaction-error%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F6999906%2Fdistributed-transaction-error%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fsqlforum%2F2010%2F12%2F20%2Ffaq-how-do-i-configure-sql-server-to-enable-distributed-transactions-via-linked-server%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fblogs.msdn.microsoft.com%2Fsqlforum%2F2010%2F12%2F20%2Ffaq-how-do-i-configure-sql-server-to-enable-distributed-transactions-via-linked-server%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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!

1 Reply
Highlighted