Adding fields to table = SQL Trouble

Johannes Nielsen · **Posted:** Fri Jan 14, 2011 2:50 pm

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.

Attachment:

Readpast.png [ 39.45 KiB | Viewed 747 times ]

Any ideas to what is causing this?

ara3n · **Posted:** Fri Jan 14, 2011 3:57 pm

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

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

Johannes Nielsen · **Posted:** Fri Jan 14, 2011 4:27 pm

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 · **Posted:** Fri Jan 14, 2011 5:04 pm

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

Johannes Nielsen · **Posted:** Fri Jan 14, 2011 5:05 pm

2009 is in the pipeline, but at this very moment this error is halting some modifications

But thank for your help!

ara3n · **Posted:** Fri Jan 14, 2011 5:22 pm

Do you have always row lock enabled?

Johannes Nielsen · **Posted:** Fri Jan 14, 2011 6:20 pm

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 · **Posted:** Sat Jan 15, 2011 9:27 am

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 · **Posted:** Sun Jan 16, 2011 1:53 pm

Hi Kamil

Thanks for your input

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

kine · **Posted:** Sun Jan 16, 2011 10:09 pm

They are not officially supported...

Fommo · **Posted:** Mon Apr 02, 2012 7:06 pm

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 · **Posted:** Mon Apr 02, 2012 9:44 pm

Quote:

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 · **Posted:** Mon Apr 02, 2012 9:58 pm

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 · **Posted:** Tue Apr 03, 2012 8:23 am

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 · **Posted:** Wed Apr 04, 2012 11:28 am

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:

Code: Select all

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.

mibuso.com

Adding fields to table = SQL Trouble

Who is online