The problem
While trying to setup NAS server for client to do the nightly Job Queue events we were stuck with the problem of NAS failing to do anything after starting up.
In the event log there were three Event ID 20010 Warnings:
The following ODBC error occurred: Error: [Microsoft][ODBC Driver Manager] Function sequence error State ID: HY010
The following ODBC error occurred: Error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state State ID: 24000
The Application Server for Microsoft Dynamics NAV NAVSRVAPL-SQL could not initialize properly. The server will attempt to initialize every 30 seconds until this is successful.
When starting without any jobs in the Ready state NAS would initialize but didn’t have these warnings because it had nothing to do.
The Resolution
After series of investigations we came up with the clue to investigate further. I created the test table that had only two fields: 1. Primary key, 2. Datetime. As the primary key field was setup as Autoincrement we managed to recreate the error we saw with NAS using the same user that was used to start NAS. This was strange because this user had dbo role in SQL server that is required for user when using Autoincrement property – as NAV Development Documentation states. When removing Autoincrement property from the primary key field the error didn’t appear anymore.
We setup simple codeunit, that would increment this simple table, in Job Queue but the problem reappeared again. After “digging deep” in the NAS initialization process in standard NAV codeunits we found out that the two tables that are used in this process also had Autoincrement property set to YES. These are tables 405 Change Log Entry and 474 Job Queue Log Entry.
Because inserting records in these two tables were failing the NAS failed to do anything but to report error. We removed Autoincrement property and added code to populate primary keys of these two tables incrementaly. After that the NAS started normally and was executing jobs in Job Queue.
The Conclusion
We believe that the problem is, still, with the assigned SQL rights for the user that is running NAS, although it had db_owner rights on that database, but we could not investigate further because this was production environment. All the other NAS implemenations were working properly without this “intervention” on the standard Microsoft Dynamics NAV code or tables. But this is the workaround we had to implement to overcome this situation.
Update
After moving client’s database to another SQL server this problem went away (as did the few other). This encourages me to believe that the db_owner rights were messed up somehow on initial SQL server.