I am running into a frustrating problem when using the JDBC Adapter to call a stored procedure.
I use the Stored Procedure with Signature template to call a stp on a SQL Server 2000 database. The procedure receives a datetime range and selects distincts values of a column based on it. Its body looks something like:
SELECT DISTINCT column_1 FROM table WHERE column_2 BETWEEN @minDatetime AND @maxDatetime
Both column_1 and column_2 are indexed on the server.
From time to time, a call to this procedure through the JDBC Adapter will take far too long to execute (up to 45 min!!! :eek:). At one time, I thought it was the table that was locked. But then I set the transaction isolation level to read uncommited and I still had the same problem.
If during the time where the stp is locked in the IS I call it with the same parameters from Query Analyser it takes about 30s to execute. So I’m confident this is not a problem with the stored procedure in itself.
I have noticed that this occurs mainly when the call for the adapter service is nested within other code. If I call it directly it’ll rarely block.
Connections are made on LOCAL_TRANSACTION mode. I use the startTransaction / commitTransaction services arround every call to the db. I am not stepping through my code.
I saw the http://wmusers.com/forum/showthread.php?t=9928 thread, that is somewhat related to my problem, and tried to apply the suggestion given there. But it had no effect for me.
I am using JDBC Adapter version 6.0.3 with sqljdbc1.1 and IS version 6.1 ( IS_6-1_FP2, IS_6-1_SP1, TNS_6-1_Fix15).
If someone could shed some light into this matter, I’d be immensly greatful.