Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Situation
A (Tomcat) Java web application using jTDS to connect to a MSSQL 2008 database. This Java application for 99% executes MSSQL stored procedures using user input.

Problem
The jTDS driver replies sometimes with error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

We can avoid this by adding prepareSQL=0 to the jTDS connection string. Then the error goes away, but with all other settings of prepareSQL, the error stays. I don't know how many stored procedure nesting levels jTDS adds, but apparently it's too much for our application.

Questions
1) With only stored procedures to execute, of course using Prepared Statements in the Java code, how much effect does prepareSQL=3 (or prepareSQL=0) have for us? In other words: on every website I find people say "Never use prepareSQL=0 in production environments", is that also applicable to this situation?
2) If prepareSQL=0 is not a recommended solution, a security issue, etc., we should maybe look for another driver. jTDS has not been updated the past 2 years and Microsoft has a driver for JDBC 4.0. I can't find any benchmarks or comparisons between jTDS and Microsoft's JDBC 4.0 driver though. With Microsoft's 2.0 and 3.0 drivers, the general opinion seemed to be that jTDS is faster, better, more efficient. Is that still the case with JDBC 4.0 or has Microsoft passed its competitor in this?

This is a duplicate of question http://stackoverflow.com/questions/10426610/jtds-stored-procedures-preparesql-nesting-level-error but probably it fits better here... I just don't have the rep yet to attach a bounty here..

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.