NAV 2013R2: The following SQL-error was unexpected. The prepared Statement with handle xyz was ...

Heinrich

Hi all,

on May, 10th, this error arises the first time: "The following SQL-error was unexpected. The prepared Statement with handle xyz was not found."
The error pops up in a message box and you find it in the event log of the NST-Server.

This is a part of an event log entry:

Server instance: NAVServerProd
Tenant ID: default
Session type: RoleTailoredClient
Session ID: 12
User: Domain\Username
Type: Microsoft.Dynamics.Nav.Types.Exceptions.NavSqlException
SuppressMessage: False
SuppressExceptionCreatedEvent: False
FatalityScope: None
Message:
Der folgende SQL-Fehler war nicht erwartet.
Die vorbereitete Anweisung mit dem Handle 4571 wurde nicht gefunden.
SQL-Anweisung:
SELECT "timestamp","Type","No_",.... FROM "NAV2013".dbo."Company$Table 123" WITH(READUNCOMMITTED) WHERE ("Type"=@0 AND "No_"=@1) OPTION(OPTIMIZE FOR UNKNOWN)
StackTrace:



Only few of our custumers are on NAV 2013 R2 and we have not seen a similar error elsewhere.
Any hints / tips?

Thanks in advance

Heinrich

Some further information: Most times this error occures only one (out of five) NST at a time is involved. Since May every NST had this error several times.

ThomasHagenmeyer

Hello Heinrich,

just a bit of additional info: we encountered this situation as well since spring now and then. We are running NAV 2016. It seems to be related to the uptime of the service(s), if we restart the services in 2-3 days rhythm, it doesn't show up. As we didn't implement a new CU or so we assume that this behaviour was introduced by some update to non-NAV software, probably SQL-Server. When the error shows up and we do not restart the services, it is thrown more often as time goes on, in about half an hour we cannot use NAV until restart of the services.

We had some list pages on tables containing BLOB fields for showing traffic light pics inidicating status of the line. The picture was assigned to the BLOB field on page level during runtime (so it is not used to store the BLOBS). Users using these pages were affected by this error more often than others, so we changed the pages so the blob field in the page rec isn't used anymore, that helped a bit (the error only appears now and then now), but didn't avoid the error completely.

Unfortunately I have no solution up to now except the regular restart of the NSTs.

Bye,
Thomas

Heinrich

Hi Thomas,

thanks for the Feedback.
Unfortunately we already restart the services every day...

The customer moved to SQL 2017 CU14 in April. I'm not sure if this is connected because the first 4 weeks after the upgrade they didn't have this error.

We have some BLOB fields on pages, too. Up to now I don't know what pages the users were in when the error pops up. I'll try to get this Information and route your feedback to the developers.

Did you (or your Dynamics NAV partner) open a support case at Microsoft? They still have to support NAV 2016 ;-)

Best
Heinrich

ThomasHagenmeyer

Hi Heinrich,

you may get a hint regarding the pages used from the error message: the sql statement in our case often was related to the BLOB fields. But it's not only the BLOBs, other statements are affected, too. I made a bit of research: in our case, the error appeared in February already. We didn't open a Microsoft support case, for the error now doesn't really hurt (our 2- or 3-day period of restarting the services does the job normally) and we cannot reproduce it. We just can wait until it shows up and then shurely it is a bad time to let the system in the error state until someone has done her/his research... our user's won't be so happy ...
As we already look forward to an upgrade to Business Central on premise, we decided to live with it.

Cheers,
Thomas