Record Variable Using Cached Values??

headley27

Here's the Scenario:

Custom Report #1

Used by Purchasing Staff Modifies a Custom Table to update it's state: 'Active' (On PreReport) or 'Inactive' (OnPostReport)

Custom Report #2

Used by Warehouse Staff Shouldn't be run when Report #1 is running Reads the Custom Table to determine if Report #1 is running

Additional information is gathered from this Custom Table in the same fashion.

The problem that I have is that the 'current' value isn't always pulled from this table as though it is being 'cached' somehow.

This is an extreme example (I only need to run Report #2 once) but it will explain what I am facing:

Step 1: I start Report #2 . 'Inactive' state is correctly returned for Report #1 (Report #2 runs for 1 minute). Step 2: I start Report #1 after Report #2 has finished running (Report #1 runs for 10 minutes). Step 3: I start Report #2 while Report #1 is running. 'Inactive' state is incorrectly returned (runs for 1 minute). Step 4: I start Report #2 again while Report #1 is running. 'Inactive' state is still incorrectly returned (runs for 1 minute). Step 5: I start Report #2 a third time while Report #1 is running. 'Active' state is now correctly returned (runs for 1 minute). Step 6: Report #1 finishes running. Step 7: I start Report #2. 'Active' state is incorrectly returned (runs for 1 minute). Step 8: I start Report #2 again. 'Inactive' state is correctly returned (runs for 1 minute).

I am running a SQL Database and can verify that it contains the correct value at all times.

Querying the database using Rec.GET does not always pull the current value from the SQL database.

There is no rhyme or reason as to which value it decides to use. Sometimes it's the current value, sometimes it's the cached value.

I have tried Rec.RESET as well as SELECTLATESTVERSION without success. ](*,)

Has anyone experienced this issue?

Thank you very much for your assistance.

Waldo

I will try to give you some tips.

1) Make sure when you set your state (active/inactive) that you put a COMMIT (use wisely), so that the state is commited to the database. Do this OnPreReport as well as OnPostReport.
2) With CLEAR(recVar) you get a fresh copy of your data to your variable.
3) You might want to forget about all this, and play arround with LOCKTABLE and such ... :-k

I know that on SQL Server, NAV reads uncommitted data (READUNCOMMITTED). This might be the reason that it's behaving this way. Off course, only when you didn't put a COMMIT after the change of state...

headley27

Thanks Waldo.

I had been commiting the state to the database. I can see the change happen using SQL Enterprise Manager / Query Analyzer.

I tried the Clear() function without success.

What do you mean by 'play around with LOCKTABLE and such ...'

Thanks again.

Waldo

Well, if you lock the tabel in Report 1, and in report 2 you try to lock that same table ... .

Make any sense?

Mbad

Maybe even locktable on report one and if not modify on report 2?

headley27

Thanks for the explanations Waldo & Mbad.

I can't lock the table in question but I do have more to add.

Again, my example was a bit extreme.
It was provided to present what I seemed to be experiencing.
I have confirmed however that the steps should have read:

Step 1: I start Report #2 . 'Inactive' state is correctly returned for Report #1 (Report #2 runs for 1 minute). Step 2: I start Report #1 after Report #2 has finished running (Report #1 runs for 10 minutes). Step 3: I start Report #2 while Report #1 is running. 'Inactive' state is incorrectly returned (runs for 1 minute).

Step 4: Changed to > I start Report #2 again while Report #1 is running. 'Active' state is correctly returned (runs for 1 minute).
Step 5: Ignore
Step 6: Report #1 finishes running.
Step 7: I start Report #2. 'Active' state is incorrectly returned (runs for 1 minute).
Step 8: I start Report #2 again. 'Inactive' state is correctly returned (runs for 1 minute).A minor difference, but I just wanted to note that the issue wouldn't persist beyond 1 full pass of Report #2 while Report #1 was running.
The issue would correct itself in less than 1 minute, never more.

I ran some tests where I would start Report #1 and then run Report #2 immediately following.
If I didn't get the expected warning message, I would cancel Report #2 and quickly re-open it.
I would continue to do so until Navision displayed the proper warning message.

The average time that Navision would take to grab the value that I was expecting seemed to be between 15 and 20 seconds.

I am a little confused considering the correct values were returned instantly by SQL Query Analyzer, SQL Enterprise Manager and (if I recall correctly) even the Navision Debugger but not the Navision Client.

Anyways, to make a long story short, I figured that I could live with a 15 to 20 second delay as it was better than doing nothing.

I saved the changes to the database (I was previously running Report #2 using Tools > Designer > File > Run as I was testing my changes before saving them).

As strange as it may sound everything now works fine. Since recompiling the object (Report #2), this issue does not seem to be occuring. + :shock: + :-k

I don't really understand what has happened here but perhaps someone could shed some light.

Thanks again for your assistance.

Waldo

Have you ever thought of trying to use a single instance codeunit?

headley27

No, I hadn't.

Everything seems to be working as it should, but I will keep this in mind should further modifications be required.

Thanks again.

Waldo

headley27 wrote:

No, I hadn't.

Everything seems to be working as it should, but I will keep this in mind should further modifications be required.

Thanks again.

When I think of it ... a single instance codeunit won't work with your problem ... it only works when using one client, and I suppose you're using more than one :oops:

David_Singleton

headley27 wrote:

...
I saved the changes to the database (I was previously running Report #2 using Tools > Designer > File > Run as I was testing my changes before saving them).

As strange as it may sound everything now works fine. Since recompiling the object (Report #2), this issue does not seem to be occuring. + :shock: + :-k

I don't really understand what has happened here but perhaps someone could shed some light.

Thanks again for your assistance.

This is weird. This was a known bug in version 2.50, and I think it was in at least one service pack in 2.60, but I have not seen it in any of the new versions.

Which version of finsql are you running.

headley27

I am using 3.70A.

Perhaps I am not losing my mind afterall.

Strange...isn't it.

Waldo

headley27 wrote:

I am using 3.70A.

May be you can do a runtime upgrade to the latest version (4.0SP3 or may be even 5.0)? I think you got a pretty good reason to do this

headley27

Waldo wrote:

May be you can do a runtime upgrade to the latest version (4.0SP3 or may be even 5.0)? I think you got a pretty good reason to do this

Where is the best place to start researching this?

I would assume that I would need help from a consultant to implement such a change but would like to do some of my own legwork first so that I understand at least some of what is involved.

For example, my system is heavily customized in key sections (a custom pricing engine, reservation modifications, etc.) ....
.... Can I even upgrade or is Navision built to handle upgrades with heavy customization?

Thanks for your help.

David_Singleton

Just upgrading the executables means uninstall all the clients, then install the new version. You then just connect to the existing server.

But yes you should do it with your consultant for the first time, at least them there as a backup if something goes wrong.

headley27

David Singleton wrote:

Just upgrading the executables means uninstall all the clients, then install the new version. You then just connect to the existing server.

Wow. That's great to know.

I thought you had to upgrade the server at the same time.

I have a current support contract with Microsoft so I am pretty sure the upgrade will be covered.

You learn something everyday. Thanks so much.

Kudos to Waldo and David Singleton.

Waldo

No problem, Dude

What you must remember is that there has to be done one thing on the server: replace the xp_ndo.dll file on the Binn directory in the SQL Server program files... .