I have a linked server connection to Oracle from SQL server. I am calling Stored procedure from Stored Procedure. The second SP has Query to Linked server. If I main SP giving me exception.
OLE DB provider "OraOLEDB.Oracle" for linked server "ASPQA" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
Msg 7391, Level 16, State 2, Procedure GetMemberShipStatus, Line 19
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ASPQA" was unable to begin a distributed transaction.
below SP giving me error.
ALTER PROCEDURE [dbo].[aspnet_Membership_CheckMemberStatus]
@constitId varchar(10)
AS
BEGIN
DECLARE @Result TABLE (
result INT,
mesage VARCHAR(100))
Insert @Result (result, mesage)
Exec [MyNet].[dbo].[GetMemberShipStatus] @constitId -- Linked Server SQL Server SP
select result, mesage from @Result
END
ALTER PROCEDURE [dbo].[GetMemberShipStatus]
@v_constit_id_in as varchar(100)
AS
BEGIN
DECLARE @v_locked_out_result int, @v_lockout_msg varchar(1000)
Exec ('Call MEI.member_dir_check_lockout(?,?,?)', @v_constit_id_in, @v_locked_out_result OUTPUT,@v_lockout_msg OUTPUT) AT ASPQA
Select @v_locked_out_result as Result,@v_lockout_msg as Msg
END
If I run Exec GetMemberShipStatus] '3383662' working fine,
But not working from Exec aspnet_Membership_CheckMemberStatus '3383662'