Home

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
Mike760
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
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies