Adding fields to table = SQL Trouble

Johannes_Nielsen

Hi

Using NAV 5.0 and SQL 2008 on Server 2008

I've begun to recieve this error when adding new fields to a table, regarding "READPAST Lock" and Isolation Levels.



Any ideas to what is causing this?

ara3n

NAV 5 is not compatible with sql 2008 you need sp1.

http://dynamicsuser.net/blogs/waldo/arc ... -2008.aspx

Johannes_Nielsen

Hi Ara3n

The wierd thing is that it has'nt been a problem since a month ago. We've been running SQL2008 from the beginning.

Yesterday I created a new database and restored a backup of my Production database onto it, and it does'nt have this problem :-k

ara3n

I suggest to do exe upgrade to 2009 R2. You get all the benefits and features and performance improvements.

Johannes_Nielsen

2009 is in the pipeline, but at this very moment this error is halting some modifications
But thank for your help!

ara3n

Do you have always row lock enabled?

Johannes_Nielsen

I believe I've located the culprit

My company has just been merged with another who stil uses XAL (former Damgaard Data product before merger with Navision and later picked up by MS).

An XAL vendor, who has set up some replication on several tables on the Database, is to blame, because I realise now that only these tables are affected. :roll:

So, I'm sorry to have bothered you and I really do appreciate your input!

kine

This problem with replicated tables should be corrected in one hotfix for NAV 5.00SP1. I expect that it will not be corrected for NAV 5.0 w/o SP1.

Johannes_Nielsen

Hi Kamil

Thanks for your input

Just to clarify, Replication of tables are suppose to work with a NAV-database?

kine

They are not officially supported...

Fommo

Today I got exactly this problem i Dynamics NAV 2009 R2. We have added a lot of fields before without any problems. And suddenly today we can't add any new fields in multiple tables.
So, this is still a problem!!!

The only reference I get is KB 978100, but the hotfix in that KB is old and applies to NAV 2009 SP1. The customer has build 6.00.32012.
We did the change of Isolation Level and it works now, but I'm really curious about an explaination why this suddenly occurs (and the customer IT department demands an explaination).
What is the downside of changing Isolation Level, can it make the system unstable? Is it possible to change back to SERIALIZABLE now when I got the new fields in place?

I hope someone can clarify this as I get no answers from Microsoft, they just point to the KB.

kine

but the hotfix in that KB is old and applies to NAV 2009 SP1

All hotfixes are for NAV 2009 SP1! There are NO HOTFIXES for NAV 2009 R2! Just read the info about versions and hotfixes on partnersource. All hotfixes are released for NAV 2009 SP1.

Fommo

kine wrote:

All hotfixes are for NAV 2009 SP1! There are NO HOTFIXES for NAV 2009 R2! Just read the info about versions and hotfixes on partnersource. All hotfixes are released for NAV 2009 SP1.

Thanks for you reply. However, that's not even true. There are a lot of hotfixes, or upgrades if you prefer, to NAV 2009 R2. The base build number for R2 is 32012, but the latest build is 33210 containing hotfixes 2675790, 2678831 and 2677811. All these hotfixes apply to BOTH NAV 2009 SP1 and R2. We've already needed some of the fixes for some R2 customers.

This is not relevant for this question though, since there are no hotfixes what I can see for this particular issue in NAV 2009 R2.

kine

Just want to clear it for other: all hotfixes after build 32074 are for BOTH versions - SP1 and R2, but they are officially for SP1. R2 is since this build same as SP1. There are no special hotfixes for R2, only for SP1 applicable to both.

If your customer have build 32012, it is really "old" build today. Actual is 33240. I recommend to use it even when the error is not described as solved. Sometime the bugs are solved bu not documented. Of course, do not forget that this build will need to "upgrade" the database and recompile the objects (change from something older than 32942 to something newer needs this upgrade).

Fommo

kine wrote:

Just want to clear it for other: all hotfixes after build 32074 are for BOTH versions - SP1 and R2, but they are officially for SP1. R2 is since this build same as SP1. There are no special hotfixes for R2, only for SP1 applicable to both.

Thanks for that clarification, Kamil. I'm sorry for being rude and unpolite. :oops:
I'll discuss this with the customer, it's a big global company so to upgrade them it will require a lot of work replacing all the clients and server components.

However, just for everyone to know, I've found the solution to the problem in those KB articles. The script to tell NAV to use REPEATABLE READ instead of SERIALIZABLE for some specific queries also works in NAV 2009:

update [$ndo$dbproperty] set diagnostics = diagnostics | 4194304

After running this we have no problems, and the solution should be considered pretty safe since it's only affecting the troublesome queries.

kine

Fommo wrote:

...I'm sorry for being rude and unpolite. :oops: ...

Don't worry, I haven't take it as rude or unpolite... 8)

Good to see, that you have managed it.

And regarding the update in big companies - yes, it is problem and with NAV 2009 and newer it is bigger problem than before (keeping the same builds). It is why it is good to think about e.g. App-V technology or ClickOnce to distribute the correct version to correct people.