NAV Code and SQL Procedure in same transaction

SonGoten13

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

ara3n

As long as nav is not delay inserting the records (bulk insert) and you stored procedure is reading uncommitted data.

Marije_Brummel

You should add the (WITH NOLOCK) hint to your stored procedure.

SonGoten13

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

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.

SonGoten13

@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

thats the nav delayed insert (bulk insert feature)

SonGoten13

ah ok, good to know, thx.
Is this always enabled or is it controllable?

ara3n

always enabled. you have to call a modify for nav to actually write to db

Kishorm

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);