	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

Question

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

9.1

Error calling an Oracle stored procedure from SQL Server

Asked by freyeso in Delphi Curiosities, MS SQL Server, SQL Server 2005

Tags: Linked Server SQL Server 2005, Remote Stored procedure

I have an Oracle Linked Server en SQL Server 2005. I can call an Oracle's stored procedure from SQL Server 2005 and the Oracle's Data is updated. There is a trigger in a SQL Server table which call the oracle stored procedure, when the stored procedure is called from trigger return the error : OLE DB provider "OraOLEDB.Oracle" for linked server "XXX" returned message "ORA-02089: COMMIT is not allowed in a subordinate session. I need to know how fix that error.

View the Solution FREE for 30 Days

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:

           
           
             Calling stored procedure from SQL Server Editor:
 
declare @lc_err CHAR(5)
declare @lc_err2 CHAR(254);
declare @return_status int;
 
EXECUTE ('begin  PKGRLINVOICE.PRCHANGESTATUS (?,?,?,?,?); end;', 'AAAAA', 111111, 'H', @lc_err OUTPUT,@lc_err2 OUTPUT) AT XXX; 
 
Trigger in SQL Server 2005:
 
USE [Envio]
GO
/****** Object:  Trigger [dbo].[trg_update_icc_status]    Script Date: 04/20/2009 16:34:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- Batch submitted through debugger: SQLQuery1.sql|7|0|C:\Documents and Settings\bigdog\Local Settings\Temp\2\~vs8.sql
-- Batch submitted through debugger: SQLQuery3.sql|7|0|C:\Documents and Settings\bigdog\Local Settings\Temp\1\~vs1F.sql
--THE EVALUATION VERSION TRIMS COLUMN NAMES AND VARIABLES TO 7 CHARACTERS
--
ALTER trigger [dbo].[trg_update_XXXX] on [dbo].[RECEIVER]
after update 
   AS
   DECLARE @SWV_NE CHAR(10)
   DECLARE @SWV_NE2 NUMERIC(15,0)
   DECLARE @SWV_NE3 CHAR(2)
   DECLARE @SWV_Cu CURSOR
   SET @SWV_Cu = CURSOR  FOR SELECT ID_BRAN,ID_RECE,ID_FLAG FROM inserted
   OPEN @SWV_Cu
   FETCH NEXT FROM @SWV_Cu INTO @SWV_NE,@SWV_NE2,@SWV_NE3
   WHILE @@FETCH_STATUS = 0
   begin
 
      declare @lc_err CHAR(5)
      declare @lc_err2 CHAR(254)
      --set @lc_err = '11111'
      --set @lc_err2 = '                                                                                                               '
      DECLARE @ID_BRANCH CHAR(6);
      DECLARE @ID_FLAG_RECEIVER CHAR(1);
      SET @ID_BRANCH = LTRIM(RTRIM(@SWV_NE))
      SET @ID_FLAG_RECEIVER = LTRIM(RTRIM(@SWV_NE3))
      
      EXECUTE ( 'begin PKGRLINVOICE.PRCHANGESTATUS (?,?,?,?,?); end;', @ID_BRANCH, @SWV_NE2, @ID_FLAG_RECEIVER, @lc_err OUTPUT,@lc_err2 OUTPUT) AT XXX; 
      
      --EXECUTE sp_change_status @SWV_NE,@SWV_NE2,@SWV_NE3,@lc_err OUTPUT,@lc_err2 OUTPUT
      FETCH NEXT FROM @SWV_Cu INTO @SWV_NE,@SWV_NE2,@SWV_NE3
   end
   CLOSE @SWV_Cu
 
 
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[trg_update_icc_status]', @order=N'First', @stmttype=N'UPDATE'
           
         

	Title
1	Alternative to SELECT .. FOR UPDATE
2	Unable to login as SYS after changing i…

Error calling an Oracle stored procedure from SQL Server

Asked by freyeso in Delphi Curiosities, MS SQL Server, SQL Server 2005

Tags: Linked Server SQL Server 2005, Remote Stored procedure

About this solution