SQL Server Performance Forum – Threads Archive

MS SQL 2005 :: Error calling Stored Procedures.

Hello, We are using MS SQL Server 2005 with its latest Driver (sqljdbc_1.0.809.102_enu.exe, released in January 2006). But somehow we are not able to connect to the Stored Procedure. It gives following Exception :: —————————————————— com.microsoft.sqlserver.jdbc.SQLServerException: A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteQuery(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source) ——————————————————– What could be the possible cause for this error? [?] Any Ideas??? Thanks !!
Khyati

---

1. Are you using more than 1 SELECT statement in your stored procedure?
2. Are you using "selectMethod=cursor" in the Java code?
3. Are you using clearParameters() in your Java code?
Any of the above have been known to cause this error. The last 2 require a fix from Microsoft (not yet released!).
Nathan H. Omukwenyi

---

(1)Our procedure is of this kind:: —————————————————-
ALTER PROCEDURE [SCHEMA_NAME].[PK_GENERATOR]
@p_tnameVARCHAR(30),
@p_numINT OUTPUT AS
BEGIN
SET NOCOUNT ON
DECLARE @adv_rowcount INT
DECLARE @adv_error INT
DECLARE @v_currentkey NUMERIC(11) SELECT @v_currentkey = currentKey FROM PKTBL(UPDLOCK) WHERE tablename = @p_tname SELECT @adv_error = @@ERROR, @[email protected]@ROWCOUNT
IF @adv_error != 0 OR @adv_rowcount = 0 OR @adv_rowcount > 1
GOTO Exception1 SELECT @v_currentkey = @v_currentkey + 1 UPDATE PKTBL SET currentKey = @v_currentkey WHERE tablename = @p_tname
SELECT @adv_error = @@ERROR
IF @adv_error != 0
GOTO Exception1 SELECT @p_num = @v_currentkey GOTO ExitLabel1 Exception1:
IF @adv_rowcount = 0 BEGIN
SELECT @p_num = – 1
ROLLBACK TRANSACTION
END
ELSE BEGIN
SELECT @p_num = – 2
ROLLBACK TRANSACTION
END ExitLabel1:
SET NOCOUNT OFF
END
—————————————————- (2) We are using "selectMethod=cursor" in JDBC, for performance reasons etc.. (3) No, we arent using "clearParameters()" anywhere. Why is the limitation of more than 1 SELECT statement in SP present?
Its the most basic thing which any Stored PROCEDURE is expected to contain. We are using Weblogic Server 8.1 SP2 & apparently all works fine if we use Weblogic in-built driver. But the issue occurs when we use the latest MS driver. We have tried a lot of tweaks, but all in vain.
Is there any work around for this? Thanks.
Khyati

---

I don’t know the direct solution, but have you tried downloading the latest jdbc drive fromhttp://www.microsoft.com/downloads/…3b-32ff-4474-a44a-22b6ae2c4e17&DisplayLang=en here. May be this will resolve the issues. http://www.sqlsummit.com/JDBCVend.htm too) Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Satya,
We are using the same driver as in above link i.e. sqljdbc_1.0.809.102_enu.exe released in January 2006, which seems to be the latest one. Khyati

---

I don’t have solution for you problem but I am curious. Why don’t you just use column identity property instead of general autonumber proc?

---

Sure we can do that. But this issue occurs with all the Stored Procedures which we have. So its not just specific to this one.

---

I am getting the following exceptions while using SQL Server 2005
com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction, desc: 37000000b7.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(Unknown Source) My SP have the following code DECLARE
@counter int,
@varXelUser int BEGIN TRAN PRINT(‘**********Adding Xellerate Users Organization *********’); SELECT @counter = count(*) FROM ACT WHERE act_name =’Xellerate Users’;
IF @counter = 0
BEGIN
PRINT(‘Inserting Xellerate Users…’);
INSERT INTO act (act_name, act_cust_type, act_status, act_upn,
act_update, act_updateby, act_create, act_createby, act_data_level,act_rowver)
VALUES (‘Xellerate Users’, ‘System’, ‘Active’, ‘Xellerate Users’,
GETDATE(), 99999,GETDATE(), 99999, 1,0×0)
END
ELSE PRINT(‘Organization Xellerate Users is already there.’) SELECT @varXelUser = ACT_KEY FROM ACT WHERE ACT_NAME =’Xellerate Users’; SELECT @COUNTER = count(*) FROM ord WHERE act_key = @varXelUser;
IF @counter = 0
BEGIN
PRINT(‘Inserting Xellerate Users INTO ord…’);
INSERT INTO ord (ACT_KEY, ORD_CUST_REQUEST,
ord_update, ord_updateby, ord_data_level,ord_rowver)
VALUES (@varXelUser,GETDATE(),GETDATE(), 99999, 1,0×0)
END
ELSE PRINT(‘ord for Xellerate Users is already there.’) COMMIT
go
I have tried both January as well as August release of JDBC Driver.
But getting the same set of exceptions.
Can somebody help me out. Thanks

---

kbahttp://support.microsoft.com/kb/919006 &http://forum.java.sun.com/thread.jspa?threadID=782889&messageID=4451381 fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Hi Khyati
were you able to fix this issue. I am getting the same exception when executing store procedures with the Microsoft driver.
regards
Vasanth

---

Vasanth
Have you referred to the links above that are specified in your case? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

Usually this problem is cause when you select 1 statement in your stored procedure. So check your those statements and correct them or may be your java code may creat a problem.

---

Another big no-no is to have a PRINT command in a sproc in a production database.
The PRINT command should be used for debugging purposes only, because it can throw off client applications.

---

]]>