Dismiss Notice

Register now to be one of the first members of this SharePoint Community! Click here it just takes seconds!

Dismiss Notice
Welcome Guest from Country Flag

Troubleshoot 586 error message id with state 5 – The prepared statement handle 1 is not...

Discussion in 'Official Microsoft News' started by Vikas Rana, Mar 31, 2017.

Thread Status:
Not open for further replies.
  1. Vikas Rana

    Vikas Rana Guest

    Blog Posts:
    0
    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

    Continue reading...
     
Thread Status:
Not open for further replies.

Share This Page

LiveZilla Live Chat Software