Global Dimension Change

Business Central/NAV Technical Forum
1

Hey Friends,

Though it’s not recommended but now we need to change a global dimension in between of the Project which has around entries in lacks. As per the standard process I navigated to General Ledger Setup → Action → Change Global Dimension. After around 1 hr of execution I am getting this error

A connection to SQL server is no longer usable.
This could be caused by one of the following reasons:

  • The server has been shut down manually or because of an error.
  • The SQL server connection settings are not correct.
  • A network failure has occurred.
  • A hardware failure has occurred on the server or on your computer.

I did every possible way to sort it out like -

  1. Adding Port in Firewall Exception

  2. Increasing SQL Command timeout.

3.Giving db_datareader, db_datawriter, db_ddladmin to the service account and NT Authority\Network Service account with default schema as $ndo$navlistener.

4.Restarting NAV And SQL Services.

I too stopped all those SQL Jobs going behind and I was the single user doing this Operation still every time I landed up with the same Issue.

I too went through https://msdn.microsoft.com/en-us/library/dn951484(v=nav.90).aspx where Even ID 216 and 112 was the case which was logging up into Event Viewer and did whatever was recommended but still on the same place.

Always Thanks to DUG.

2

If it stops several times approximately after the same time - try to change the SQL connection timeout to some HUGE value.

It depends on NAV version / the batch job which executes, but NAV is wilful to run such DB updates in one big transaction. I don’t recall what I was trying to perform when I got these same errors, but it was something very time-consuming, too. Then this approach helped me, don’t know will it work for your issue. SQL server sees no activity while NAV is “thinking” and cancels client conn on timeout.

PS Don’t forget to set the timeout BACK to reasonable value afterwards! (it’s measured in milliseconds)
update - in latest MSSQL versions units are SECONDS, set ZERO fir infinite

3

… and clean out all Analysis by Dimensions if you have any before the change (e.g. by setting new starting date), then UPDATE them afterwards again

4

Hi Modris,

First of all thanks for your reply.

Ya as I mentioned in my try list - This was the first part which I did, i changed SQl Command Timeout to 23:50:00. My NAV Version 2013 R2 build no 36366.

I removed all Analysis by Dimension.

5

Hello,

but here are just some ideas, suggestions.

Check for triggers in SQL Server. Maybe there are some.

Check for scheduled jobs in SQL, which block the activity.

Have you checked for this value in SQL Server? (Remote Query Timeout.)

Maybe in some of the tables there is a Flowfield which is not optimized? (I had some problems in the past with a poor filtered Flowfield.)

pastedimage1479938450904v1.png
pastedimage1479938450904v1.png704×632 25.4 KB

best regards,

6

Hello,

I have found another setting in SQL. This could be the culprit too.

pastedimage1479938711383v1.png
pastedimage1479938711383v1.png704×632 32.9 KB

7

Hi Thomas,

Thanks for your time.

I increased the time interval, Remot Query Timeout including disabling flowfields which seems to be heavy but still the same case. No Triggers in SQL.

Am not sure but just a thought that it might be something related to IOPS of the Cloud but not sure.Mine is 500

8

Hello,

just another suggestion, idea:

Today I tried to run the Adjust Cost entries Report and it took forevere (usually it takes 5-6 mins per day), after I’ve seen that it won’t finish(without error) I tried to run it filtered for every unadjusted item one by one. The culprit was an unset Dimension in the past. I disabled mandatory posting on the affected account and it worked.

Try to disable mandatory posting of Dimensions in the General Ledger for all accounts. Not sure if it has to do with your problem but I think it’s worth a shot.

Also don’t thank me for your time.

The blogs of you people and this forum helped me alot to understand how Dynamics NAV works and how to develop in it.

I have to thank you.

best regards,

9

Thank you so much for your kind words Thomas, A river of joy flowing inside the heart :slight_smile:

Did this too as but still the same case. :slight_smile:

10

Hi Thomas/Modris

Finally I managed to solve this Issue. This was all about the Inconsistency in Data which was totally Clueless.