"Unable to start a nested transaction.." deleting linked rec

rsaritzky

Hi,

I am reading records from a second (non-NAV database). The records go into a General Journal. After reading all the records, I want to delete the records from the source non-NAV table.

Rather than using the "standard" ADO method, I created a linked table in NAV. I can read the records just fine, but when I try to DELETE the records, I receive an error:

---

Microsoft Dynamics NAV

---

The following SQL Server error(s) occurred while accessing the USBIRD_ALLOC_TXN table:

7395,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "XXXXXX". 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 "SQLNCLI" for linked server "XXXXXX" returned message "Cannot start more transactions on this session.".

SQL:
DELETE FROM "YYYYY"."dbo"."SOURCETABLE" WITH (REPEATABLEREAD) WHERE ("RecID"=N'1')

Googling this error message hasn't been much help. If you're executing Transact-SQL code, then you can theoretically set the proper switch, but I'm just doing a DELETEALL (also tried looping through each record and doing a DELETE).

Has anyone had success deleting records from a linked server table via C/AL Code not using ADO?

Thx

Chinmoy

Not very sure though, I remember once I had a similar problem and I used a view to manage the situation. Y don't u try to first expose the table on a view and then do your transactions on the view instead of the table.

Please let us know if this is of any help.

Chn

rsaritzky

Chinmoy wrote:

Not very sure though, I remember once I had a similar problem and I used a view to manage the situation. Y don't u try to first expose the table on a view and then do your transactions on the view instead of the table.

Please let us know if this is of any help.

Chn

Chinmoy wrote:

Not very sure though, I remember once I had a similar problem and I used a view to manage the situation. Y don't u try to first expose the table on a view and then do your transactions on the view instead of the table.

Chn

That's exactly what I'm doing. I have a view defined in my NAV database that does a

CREATE View [dbo].[SOURCETABLE] AS
SELECT
EntryNumber
,DepartmentCode
,ProjectCode
,GLAccountNo
,Description
,TransAmt
,PostingDate
,UserID
FROM [XXXXXXXXX].[LinkedDatabaseName.[dbo].[GLImportTable]

I then defined a Linked Table SOURCETABLE in NAV (LinkedView=Yes).

Then, after reading the records (which works fine), I tried both a DELETEALL and looping through the table record by record and doing a DELETE. Received the above error.

rsaritzky

I was able to resolve this error - I had missed setting the "LinkedInTransaction" property to "No". After setting this property, the DELETEALL (and DELETE) work successfully.

Ron

edwiedj

rsaritzky wrote:

I was able to resolve this error - I had missed setting the "LinkedInTransaction" property to "No". After setting this property, the DELETEALL (and DELETE) work successfully.

Ron

thank bro .... it's work ....

seff_yohanson

Amazing, thanks! This has saved me a lot of time and confusion!

radek.bb

Hi guys,
Is it only me - or this stopped working in NAV2013 R2?...
"LinkedInTransaction" property still exists but looks like it is not handled by NAV anymore...
:?

radek.bb

In Nav2009 updating a value in normal table was executed like this (from SQL Profiler):

1/
[SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2/
declare @p1 int
set @p1=175
exec sp_prepexec @p1 output,...,N'SELECT  ....
select @p1
3/
declare @p1 int
set @p1=176
exec sp_prepexec @p1 output,....,N'UPDATE ....
select @p1

While updating value in linked table (having LinkedInTransaction set to "NO") like this:

1/
[b]SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED[/b]
2/
declare @p1 int
set @p1=165
exec sp_prepexec @p1 output, ..., N'SELECT  ....
select @p1
3/
declare @p1 int
set @p1=166
exec sp_prepexec @p1 output, .... , N'UPDATE ....
select @p1

IN NAV2013 - both types are executed in the same way:

1/
exec sp_execute 1,@lastKnownTimeStamp=4574745
2/
declare @p1 int
set @p1=20
exec sp_prepexec @p1 output,....,N'SELECT ...
select @p1
3/
declare @p1 int
set @p1=21
exec sp_prepexec @p1 output,...,N'UPDATE ....
select @p1

The only difference I can see is that for update of linked table @p1 parameter is initialised as NULL

3/
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,...,N'UPDATE ....
select @p1

I could be wrong - as I am no expert on this - but something is not right there...

Any comments? :-k

radek.bb

Yep, I was correct... this property is no longer working in NAV2013 (it was not working probably in NAV2009 RTC either).

I haven't got the answer yet if they are going to re-implement this feature - but I got from Microsoft quite clever workaround - which combined with delay insert set on pages - makes linked data fully editable again.

They told me to create INSTEAD OF triggers for any view:

CREATE TRIGGER [dbo].[Set_XACT_ABORT_ON]
   ON  [dbo].[LOCAL View Name] 
   Instead of Update
AS 
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
SET NOCOUNT ON;
      SET XACT_ABORT ON;
      --Change this below line of code depending on your requirements..
      update  [Remote Server name].[Database Name].[dbo].[Remote Table name]
                 set STATUS = (select STATUS from INSERTED) ;                
        
END
GO

I had to dig a bit but in the end I ended with all three triggers (insert, update, modify) being overloaded - and it works. \:D/

dougshepard3

Any change you could provide the code for the three triggers?

Thanks!

-Doug