Troubleshoot 586 error message id with state 5 - The prepared statement handle 1 is not valid in this

Recently I worked with one of customer where in jTDS application getting an error 586 and in Profiler Trace we could see an exception with error message id : 586 State 5 with below text:

The prepared statement handle 1 is not valid in this
context.  Please verify that
current database, user default schema, and ANSI_NULLS and QUOTED_IDENTIFIER set
options are not changed since the handle is prepared.

I was able to reproduce this issue and could see following error message in SSMS:

Msg 586,Level 16, State 5, Procedure sp_execute, Line 1 [Batch Start Line2]

The prepared statement handle 1 is not valid in this context.  Please verify that current database, user default schema, and ANSI_NULLS and QUOTED_IDENTIFIER set
options are not changed since the handle is prepared.

 Here is the script I used to reproduce this issue:

use tempdb
go
declare @p1 int
exec sp_prepare @p1 output,N'@pp varchar(255)',N'SELECT @pp'
select @p1 as PrepHandle
exec sp_execute @p1, @pp='This is 1st Execute'
use master
exec sp_execute @p1, @pp='This is 2nd Execute'
go

 

use tempdb
go
declare @p1 int
exec sp_prepare @p1 output,N'@pp varchar(255)',N'SELECT @pp'
select @p1 as PrepHandle
exec sp_execute @p1, @pp='This is 1st Execute'
SET ANSI_NULLS OFF
exec sp_execute @p1, @pp='This is 2nd Execute'
go

 

So whenever you are tracking 586 error message with state 5 please check in Profiler Trace if application changing the database context or changing ANSI_NULLS. Happy Learning!!!

  

Vikas Rana (@vikasrana_dba)

Support Escalation Engineer
Microsoft India GTSC