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

Comments

• Options

  Chinmoy Member Posts: 359

  2012-11-06

  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

  0
• Options

  rsaritzky Member Posts: 469

  2012-11-06

  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.

  Ron

  0
• Options

  rsaritzky Member Posts: 469

  2012-11-06

  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

  Ron

  0
• Options

  edwiedj Member Posts: 5

  2013-03-19

  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 ....

  0
• Options

  seff_yohanson Member Posts: 23

  2013-07-02

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

  0
• Options

  radek.bb Member Posts: 49

  2013-10-15

  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...
  :?

  0
• Options

  radek.bb Member Posts: 49

  2013-10-15

  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

  0
• Options

  radek.bb Member Posts: 49

  2013-10-23

  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/

  0
• Options

  dougshepard3 Member Posts: 8

  2015-03-05

  Any change you could provide the code for the three triggers?
  
  Thanks!
  
  -Doug

  0