In the last weeks we updated two of our customers. Both got a new SQL-Server and both have now this error: "The prepared statement with handle xyz was not found."

The error occures several times throughout the day but not on all mid tier servers. If the error arises the transaction stops. In some cases the mid tier server shows this error for some minutes (not to all users) and works correct some time later. In most cases we have to restart the mid tier service.

This is what changed:

  1. both have now a VMWare VM (prior it was a Hardware Server)
  2. Windows Server changed from Win 2008 / Win 2012 to Win 2016
  3. SQL Server version changed from 2012 to 2017 (customer a) / 2014 to 2016 (customer b)

The error is logged in the Windows Eventlog on mid tier servers that interact with the databases and the users get a appropiate message box. We don't see this at the SQL server. Maybe we don't look at the right log...

Do you have any hint or suggestion or advice what we can do, where we should look, what to change to get rid of this error?

Our customers use MS Dynamics NAV 2013R2.

Thanks in advance Heinrich

  • Can you not trace to determine the statement? Is it coming from a forced plan? Commented Jul 31, 2019 at 14:07
  • I'm not that familiar with modern SQL features. Could you please explain a bit more? The statements vary. Up to know we didn't see a pattern. How can I trace if a plan is forced?
    – Heinrich
    Commented Aug 1, 2019 at 17:50

1 Answer 1


You can potentially find the error message through Extended Events. Create and start the following session:

 ADD EVENT sqlserver.error_reported
  ADD TARGET package0.asynchronous_file_target
    SET FILENAME = N'C:\temp\FailedQueries.xel',
    METADATAFILE = N'C:\temp\FailedQueries.xem'


To make sure it is working, run a query that will definitely raise an exception:

SELECT 1/0 FROM sys.objects;

Check the session to make sure this event populated:

;WITH event_data AS 
  SELECT data = CONVERT(XML, event_data)
    FROM sys.fn_xe_file_target_read_file(
results AS
    [host]      = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(4000)'),
    [app]       = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(4000)'),
    [date/time] = data.value('(event/@timestamp)[1]','datetime2'),
    [query]     = data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)'),
    [error]     = data.value('(event/data[@name="error_number"]/value)[1]','int'),
    [state]     = data.value('(event/data[@name="state"]/value)[1]','tinyint'),
    [message]   = data.value('(event/data[@name="message"]/value)[1]','nvarchar(250)')
  FROM event_data
SELECT * FROM results
WHERE error NOT IN (5701,5702,5703)
ORDER BY [date/time] DESC;

Then you can periodically check the output of this query (maybe adding a filter like AND [message] LIKE N'%prepared statement%'). Hopefully that gives you some additional clues.

  • Great, thanks for this brief explanation (and sorry for the delay - I was on vacation). Unfortunately we didn't get an entry when the error in the application arises.
    – Heinrich
    Commented Aug 28, 2019 at 13:23
  • @Heinrich So if the error isn't happening in SQL Server, where are you seeing it? Perhaps you need to engage Dynamics support. Commented Aug 28, 2019 at 13:26
  • Today we were able to trace at the point in time, when the error occured. What we saw was: - a call to sp_prepexec at 12:29:23.247 (there we got handle 6376 as response) - then there were several successful calls to sp_execute with that handle (other executions took place, too) - the last successful execution with handle 6376 was at 12:29:23.377 - the error arises at 12:29:23.410 (SQL Trace EventClass = Exception) "Die vorbereitete Anweisung mit dem Handle 6376 wurde nicht gefunden." Maybe we have to trace more events in the Profiler Trace? Which would you recommend? Thanks Heinz
    – Heinrich
    Commented Sep 2, 2019 at 8:53
  • @Heinrich are the statements already collected not giving you any clue about the application that is submitting these statements? At some point you're going to have to investigate what the app is sending and why. It's easier to do that from the app than from SQL Server. Commented Sep 2, 2019 at 14:18
  • Unfortunately we don't have access to the sources of the application and were not able to see how the SQL-Queries were handled. This belongs to Microsoft and they will not help. I already asked them but the support for this Version has expired...
    – Heinrich
    Commented Sep 10, 2019 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.