Global Dimension Change

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. 

  

Parents
  • 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

Reply Children
Related
Recommended