Exception while inserting records in SQL Server (Error message id : 586)

Question

I am getting an exception when doing a batch insert operation using a prepared statement from Java.

java.sql.BatchUpdateException: The prepared statement handle 5 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.

While analyzing this error message all I could find out was its error message id : 586. I could not gather any more information about this issue.

Can anyone help me with the reason for this exception, what it means and how to resolve it ?

Machine Details :

SQL Server Version - SQL Server 2014

jTDS JAR Version - 1.2.4

PS : This exception only occurs on a particular remote machine. We cant reproduce this issue locally. Any tips on how to reproduce this issue would also be helpful.

• John K. N.: When you talk about remote machine, do you mean you are connecting from machine A and inserting into machine B?
• suraj1291993: I mean, the setup is present on a machine which I don't have access to. But as you said, my application is running on machine-a connecting to SQL Server on machine-b.
• T.H.: I'm totally guessing here, but is it possible your app//framework uses connection pooling and doesn't correctly reset the connection on reuse? That could possibly explain the unexpected set options found.
• McNets: Can you post the sql command?
• suraj1291993: @McNets We are inserting bulk of rows over different tables using multiple prepared statement. I exactly dont know over what table I am getting this exception.

Answer 1

It seems that this server has been configured in a different way than the others.

Using SSMS, right click over server instance in some of the servers where the job is running and check that ANSI NULLS and quoted identifier have the same value on this server.

enter image description here

If you prefer to use an script, have a look at:

SET ANSI NULLS
SET QUOTED_IDENTIFIER