Posted on 2006-04-20
Last Modified: 2008-02-01
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?
Question by:CHARMS1
    LVL 30

    Expert Comment

    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.
    LVL 30

    Expert Comment

    Do you have linked server objects? There is a bug in SQL Server 2000 which Microsoft says:;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.

    Author Comment

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

    LVL 30

    Accepted Solution

    Yes that's what I meant by execute some command. However, I did not know the native command for SQL Server syntax through JDBC

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Suggested Solutions

    For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
    After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
    Video by: Michael
    Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
    This video teaches viewers about errors in exception handling.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now