Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

SQLException

Hi,
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
            SET ANSI_WARNINGS ON

Can anyone help?
Thanks.
0
CHARMS1
Asked:
CHARMS1
  • 3
1 Solution
 
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.
0
 
Mayank SAssociate Director - Product EngineeringCommented:
Do you have linked server objects? There is a bug in SQL Server 2000 which Microsoft says:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q296769&

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.
0
 
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 ?, ?");

cheers..       
0
 
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now