Error in Linke Server with Oracle DB

Business Central/NAV Developers Forum
1

Hello

We use NAV 2009 with SQL 2005 (lates Updates) and have linked an Oracle 10.2.0.5.0 Server into NAV. On the Oracle site we have some Views. On The SQL Server site we have a Linked Server Object, where I can see these views. Now we integreated these views into the NAV DB.

To get the work, we also installed the Distributet Transaction Coordinator :slight_smile:

When open the Query Analizer with the NAV DB we can select, update, insert and delete all tables (also with TRANSACTIOn Begin, Commit and Rollback) . When we do the same in NAV, we can read all tables, but when we try to insert, update or delete, we get the following error:

ODEV1 is the Oracle Server

This Error comes with LinkedInTransaction = Yes in the NAV Object definition

The following SQL Server error or errors occurred when accessing the ic_product table:

7395,“42000”,[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to start a nested transaction for OLE DB provider “OraOLEDB.Oracle” for linked server “ODEV1”. A nested transaction was required because the XACT_ABORT option was set to OFF.
7412,“01000”,[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider “OraOLEDB.Oracle” for linked server “ODEV1” returned message “Cannot start more transactions on this session.”.

SQL:
UPDATE “ECNE_DEV”.“dbo”.“ic_product” WITH (REPEATABLEREAD) SET “ICPR_USER_UPDATE”=‘RAGG’ WHERE (“ICPR_PRODUCT_NUMBER”=550112.0)

This Error comes with LinkedInTransaction = No in the NAV Object definition

The following SQL Server error or errors occurred when accessing the ic_product table:

7392,“42000”,[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot start a transaction for OLE DB provider “OraOLEDB.Oracle” for linked server “ODEV1”.
7412,“01000”,[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider “OraOLEDB.Oracle” for linked server “ODEV1” returned message “Neither the isolation level nor a strengthening of it is supported.”.

SQL:
UPDATE “ECNE_DEV”.“dbo”.“ic_product” WITH (REPEATABLEREAD) SET “ICPR_USER_UPDATE”=‘BKR’ WHERE (“ICPR_PRODUCT_NUMBER”=550112.0)

I guess LinkedInTransaction mus be set to Yes int his case. So we talking about the error “Cannot start more transactions on this session”.

In the Profiler we found more Information:

UPDATE “ECNE_DEV”.“dbo”.“ic_product” WITH (REPEATABLEREAD) SET “ICPR_USER_UPDATE”=@P1 WHERE (“ICPR_PRODUCT_NUMBER”=@P2)
OLE DB provider “OraOLEDB.Oracle” for linked server “DEV1” returned message “Cannot start more transactions on this session.”.
Error: 7395, Severity: 16, State: 2
Unable to start a nested transaction for OLE DB provider “OraOLEDB.Oracle” for linked server “ODEV1”. A nested transaction was required because the XACT_ABORT option was set to OFF.

Microsoft Support gave us the following solution (which does not work):

If they create the following stored procedure then all NAV clients will automatically execute it when they establish a connection to the DB:
use
go
drop procedure dbo.sp_$ndo$loginproc
go
CREATE PROCEDURE [sp_$ndo$loginproc] @appname VARCHAR(64) = NULL, @appversion VARCHAR(16) = NULL AS
SET XACT_ABORT ON
go

GRANT EXECUTE ON [sp_$ndo$loginproc] TO public
GO

So we still habe no solution for this case.

Has anyone made anythin like this before? Hat was the settings for the DTC and the Linked Server Object? Any spectial things to do esle?
Kind regards
René

2

I guess this is an authentication problem: if you query the table with SSMS you are using your logon credentials and security context; probably sysadmin or db_owner on both (SQL, Oracle) systems.

If the View is ran via NAV it is using and Application Role, e.g. with “Standard” security it’s “$ndo$shadow”; hence you’re running the View-queries in that context. But you cannot assign access right to the “shadow” role on a remote server - it’s local within zthe NAV database context.

So when creating your Linked Server to Oracle you should use/define a sysadmin logon to Oracle 
 maybe this could help 


And: everything you do within the “loginproc” is just valid within this context - not for the NAV session! Hence, once the procedure was left the SETtings are back to default 


3

Hello Jörg.

I guss this is not the problem. As i know it is a special user with permission to the linked server. Look at the trace log of the profiler:

SP:StmtStarting UPDATE “ECNE_DEV”.“dbo”.“ic_product” WITH (REPEATABLEREAD) SET “ICPR_USER_UPDATE”=@P1 WHERE (“ICPR_PRODUCT_NUMBER”=@P2)
User Error Message OLE DB provider “OraOLEDB.Oracle” for linked server “LIONDEV1” returned message “Cannot start more transactions on this session.”.
Exception Error: 7395, Severity: 16, State: 2
User Error Message Unable to start a nested transaction for OLE DB provider “OraOLEDB.Oracle” for linked server “LIONDEV1”. A nested transaction was required because the XACT_ABORT option was set to OFF.

If we can’t find a solution, maybe dani will call you on the phone :slight_smile:

Regards
René