SQL Server Performance

MS SQL 2005 :: Error calling Stored Procedures.

Discussion in 'SQL Server 2005 CLR' started by Khyati, Mar 9, 2006.

  1. Khyati New Member

    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
  2. vbkenya New Member

    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
  3. Khyati New Member

    quote:Originally posted by vbkenya



    (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, @adv_rowcount=@@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
  4. satya Moderator

  5. Khyati New Member

  6. mmarovic Active Member

    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?
  7. Khyati New Member

    quote:Originally posted by mmarovic

    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.



  8. nikhil.goel New Member

    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,0x0)
    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,0x0)
    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


  9. satya Moderator

  10. vasanth New Member

    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
  11. satya Moderator

    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.
  12. amery buck New Member

    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.
  13. Adriaan New Member

    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.

Share This Page