1

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? – Aaron Bertrand Jul 31 '19 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 Aug 1 '19 at 17:50
1

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

CREATE EVENT SESSION FailedQueries
ON SERVER
 ADD EVENT sqlserver.error_reported
 (
   ACTION 
   (
     sqlserver.client_app_name,
     sqlserver.client_hostname,
     sqlserver.nt_username,
     sqlserver.sql_text
    )
  )
  ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'C:\temp\FailedQueries.xel',
    METADATAFILE = N'C:\temp\FailedQueries.xem'
  );
GO

ALTER EVENT SESSION FailedQueries ON SERVER STATE = START;
GO

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(
   'C:\temp\FailedQueries*.xel', 
   'C:\temp\FailedQueries*.xem', 
   NULL, NULL
 )
),
results AS
(
  SELECT 
    [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.

|improve this answer|||||
  • 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 Aug 28 '19 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. – Aaron Bertrand Aug 28 '19 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 Sep 2 '19 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. – Aaron Bertrand Sep 2 '19 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 Sep 10 '19 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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