NAV Code and SQL Procedure in same transaction

SonGoten13
SonGoten13 Member Posts: 44
edited 2013-10-09 in SQL General
Hi all,

1. Do some NAV code that inserts some data
2. Call a stored sql procedure with automation that should have access to the data from 1.
There should be NO commit between 1 and 2 because when a error occurs it should roll back the hole thing.

Is it possible to access uncommitted data in an sql procedure?

SQL Server 2008, NAV 2009 R2 Classic Client
greetings

Answers

  • ara3n
    ara3n Member Posts: 9,258
    As long as nav is not delay inserting the records (bulk insert) and you stored procedure is reading uncommitted data.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    You should add the (WITH NOLOCK) hint to your stored procedure.
    Marije Brummel | Freelance Business Central Specialist
  • SonGoten13
    SonGoten13 Member Posts: 44
    thanks for the fast responses

    @ara3n
    - i dond't really know what you mean by "bulk insert"; how can i check if thats the case?; in my concreate example here, NAV is posting a sales shipment
    - how can i tell my sql procedure to do that? i tried around with "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" but with no success

    @Mark
    - you mean CREATE PROCEDURE [dbo].[CoolProcedureName] @param1 varchar(20) WITH NOLOCK AS [...] ? That brings a compile error "unknown option". Otherwise i don't know where to put that "hint"
  • bbrown
    bbrown Member Posts: 3,268
    Keep in mind that the external call to SQL is its own transaction. So you have to be careful of when it is called. Consider the following:

    1. Make some changes in NAV
    2. Make some changes in SQL

    Here, everything would get rolled back regardless for where the error happens. But now consider this.

    1. Make some changes in NAV
    2. Make some changes in SQL
    3. More changes in NAV

    If an error occurs in step 3, only the changes in NAV get rolled back. The SQL changes will be committed.
    There are no bugs - only undocumented features.
  • SonGoten13
    SonGoten13 Member Posts: 44
    @bbrown: thanks for the info; would be no problem in my example, cause i commit it anyway after the sql code

    Well, i broke the whole thing down to a small insert in nav and select in sql example, and there it works when i use "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED".

    So not a general problem. However in my real life scenario it doesn't work. Anyone an idea wich parameters could influence that behaviour?
    @Edit: Found a solution. A modify/get/find in NAV from that record i want to select in SQL made it work. Makes no sense to me, as there are no changes to that rec in NAV after an insert, that happens before, but well, it does the job.
  • ara3n
    ara3n Member Posts: 9,258
    thats the nav delayed insert (bulk insert feature)
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • SonGoten13
    SonGoten13 Member Posts: 44
    ah ok, good to know, thx.
    Is this always enabled or is it controllable?
  • ara3n
    ara3n Member Posts: 9,258
    always enabled. you have to call a modify for nav to actually write to db
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Kishorm
    Kishorm Member Posts: 921
    There are some situations were NAV won't bulk insert e.g. when the table has a RecordID field or a BLOB field. One thing you can do if you don't want to bulk insert is to use an IF e.g. ...

    IF INSERT THEN ;

    ...this will also not bulk insert. Of course if you need it to error if the insert fails then you could do...

    IF NOT INSERT THEN ERROR(InsertFailedText);

Categories