I am calling a Stored procedure on sql server through a jdbc connection and get the followong exception

java.sql.SQLException: [LDNDCM05400V05A\CHM_MAIN1_LIVE]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

If i call the stored procedure directly on sql server it works fine but if i call it through my java process i get the exception.

Note: the folowing HAVE been set on the stored proc.
            SET ANSI_NULLS ON

Can anyone help?
Who is Participating?
Mayank SAssociate Director - Product EngineeringCommented:
Yes that's what I meant by execute some command. However, I did not know the native command for SQL Server syntax through JDBC
Mayank SAssociate Director - Product EngineeringCommented:
Well, I guess that's a DB specific setting. Not sure if you can set it in the connection-string itself. You might need to connect and then execute some command which sets it off.
Mayank SAssociate Director - Product EngineeringCommented:
Do you have linked server objects? There is a bug in SQL Server 2000 which Microsoft says:


Though I think you have set them on already: >> the folowing HAVE been set on the stored proc

Also try setting them off at the end of the stored procedure.
CHARMS1Author Commented:
I fixed it by putting the two settings at the start of my stored procedure call string . Im surprised it worked???

connection.prepareStatement("SET ANSI_NULLS ON  SET ANSI_WARNINGS ON exec HDD_sp_Sel_HA_TMS_CHARMS_Import ?, ?");

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.