Sorry if I'm late to the party on this.. But we have run into this, too. My solution was to change the "mode" of the stored proc using: sp_procxmode storedprocname, anymode Which allows the stored procedure to run both while in a transaction and not in a transaction. Hope this helps!
________________________________ From: Ejaz X Mohammed [mailto:ejaz.moham...@jpmorgan.com] Sent: Thursday, May 28, 2009 12:05 PM To: user-java@ibatis.apache.org Subject: RE: DB Error I noticed this problem with straight SQL too (insert / update) ... If I open connection with autocommit(false), and then try to commit / rollback; I get the same error Ejaz From: Glenn Barnard [mailto:barnardgl...@gmail.com] Sent: Thursday, May 28, 2009 10:54 AM To: user-java@ibatis.apache.org Subject: Re: DB Error There are a number of articles on this problem with SP's. However, I'm using straight SQL. I'm not sure this is the issue, but I am using the N+1 technique to add child records to the master. On Thu, May 28, 2009 at 11:48 AM, Ejaz X Mohammed <ejaz.moham...@jpmorgan.com> wrote: I get same error with Sybase 12.5, but when I call a stored proc which has begin tran / end tran when I execute same stored proc directly via jdbc, I don't get "SET CHAINED ..." error Should be something with driver ...jconn2.jar Ejaz From: Glenn Barnard [mailto:barnardgl...@gmail.com] Sent: Thursday, May 28, 2009 10:44 AM To: user-java@ibatis.apache.org Subject: DB Error Hi, all...I've been using Ibatis for several months now to build an internal web application. We're using connection pooling and so far so good. So now I'm trying to use the same connection pools for batch applications that are scheduled to run via a chron type function from the same webserver. Those jobs run fine on our development boxes without connection pooling (i.e., as Junit processes). But when I try to run them from the webserver, I get a few of these errors and then all the connections become useless quickly and I have to restart the webserver. The errors start with the message: Unable to set AutoCommit property.SET CHAINED command not allowed within multi-statement transaction. Then as the connections become useless, the following messages start coming out. This message I would expect after the pool is exhausted. Error in allocating a connection. Cause: In-use connections equal max-pool-size and expired max-wait-time. Cannot allocate more connections What's really perplexing me is that this happens on SELECT statements, and not just INSERT or UPDATE statements. I've Googled this error and am not getting anywhere. I've also reviewed the connection pool settings and no amount of tweaking improves the situation. Any help would be most appreciated. Our environment consists of: Java 1.6 Spring 2.5 Ibatis Glassfish/ Sun Java System Application Server 9.1 (build b58g-fcs) (webserver) Sybase 12.5 ________________________________ This email is confidential and subject to important disclaimers and conditions including on offers for the purchase or sale of securities, accuracy and completeness of information, viruses, confidentiality, legal privilege, and legal entity disclaimers, available at http://www.jpmorgan.com/pages/disclosures/email. ________________________________ This email is confidential and subject to important disclaimers and conditions including on offers for the purchase or sale of securities, accuracy and completeness of information, viruses, confidentiality, legal privilege, and legal entity disclaimers, available at http://www.jpmorgan.com/pages/disclosures/email.
