Re: JDBC and SQL server Stored procedure exception

From: Carb Simien [MSFT] (CarbinoS_at_online.microsoft.com)
Date: 04/27/04

  • Next message: Joseph Horowitz: "RE: Unexpected token type: ERROR" 
    Date: Tue, 27 Apr 2004 17:50:32 GMT

    --------------------
    | From: alin@earthling.net (Alin Sinpalean)
    | Newsgroups: microsoft.public.sqlserver.jdbcdriver
    | Subject: Re: JDBC and SQL server Stored procedure exception
    | Date: 23 Apr 2004 05:33:09 -0700
    | Organization: http://groups.google.com
    | Lines: 53
    | Message-ID: <a14fec3f.0404230433.502f67a0@posting.google.com>
    | References: <F9159980-EA5B-4BC8-8B87-4B42F93DEDBF@microsoft.com>
    <4086BE5F.9070801@bea.com>
    | NNTP-Posting-Host: 80.86.123.4
    | Content-Type: text/plain; charset=ISO-8859-1
    | Content-Transfer-Encoding: 8bit
    | X-Trace: posting.google.com 1082723590 30682 127.0.0.1 (23 Apr 2004
    12:33:10 GMT)
    | X-Complaints-To: groups-abuse@google.com
    | NNTP-Posting-Date: Fri, 23 Apr 2004 12:33:10 +0000 (UTC)
    | Path:
    cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.s
    ul.t-online.de!t-online.de!news.glorb.com!postnews1.google.com!not-for-mail
    | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:5989
    | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
    |
    | Joe Weinstein <joeNOSPAM@bea.com> wrote in message
    news:<4086BE5F.9070801@bea.com>...
    | > Steve Qian wrote:
    | >
    | > > Hi,
    | > >
    | > > We use Java (microsoft JDBC) to call a stored procedure in the SQL
    server 2000.
    | > >
    | > > In the stored procedure, we will build a sql statement, and call
    sp_executesql.
    | > > Sometime we will have a @@error coming back, and depends on the
    error, we will keep going or return an error code to the calling SP.
    | > >
    | > > So basically, we do the error handling without using RAISERROR.
    | > >
    | > > The interesting thing is the JAVA code called this stored procedure
    finishes fine, but when we trying to get the out parameter --
    Myproc.getString( 9 ), it errors out:
    | > >
    | > > 2004-04-21 11:51:19,381 FATAL
    [ours.server.datalayer.access.DataAccessExecutionObjectAdapter] Unable to
    execute "PUT" due to execution error.
    | > > java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
    JDBC][SQLServer]Invalid column name 'ADDRESS_LINE1'.
    | > > at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
    Source)
    | > > at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown
    Source)
    | > > at
    com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown
    Source)
    | > > at
    com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown
    Source)
    | > > at
    com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown
    Source)
    | > > at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown
    Source)
    | > > at
    com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknow
    n Source)
    | > > at com.microsoft.jdbc.base.BaseStatement.getNextResultType(Unknown
    Source)
    | > > at
    com.microsoft.jdbc.base.BaseCallableStatement.getAndValidateOutParameter(Unk
    nown Source)
    | > > at com.microsoft.jdbc.base.BaseCallableStatement.getString(Unknown
    Source)
    | > > at
    ours.server.datalayer.access.ExecutionObjectPutImpl.performExecution(Executi
    onObjectPutImpl.java:149)
    | > >
    | > > Is there any property we can set for this connection or statement to
    ignore these sql errors?
    | >
    | > Hi. This is odd. There's a bug somewhere here. You should not be
    getting any such exception
    | > from that getString() call. If I can see the JDBC code that handles the
    returns from the
    | > procedure, I can probebly know if you're doing it correctly to handle
    all the possible returns
    | > from the procedure for the different execute paths. You need to handle
    all the results and
    | > update counts before trying to get output parameter values. Then you
    should have no problem.
    | > Does your procedure always set a value to the output parameter?
    | >
    | > Joe Weinstein at BEA
    | >
    | > >
    | > > thanks a lot.
    | > >
    | > > Steve
    | > >
    |
    | The way I see it, the error is generated by SQL Server, not the
    | driver. The reason why the procedure seems to go through fine is that
    | the error is thrown probably at the end of the procedure, after the
    | result sets and update counts have been returned. Try running the
    | stored procedure from Query Analyzer with the same parameters; you
    | should get the same error message.
    |
    | Alin.
    |

    True, the error message is being thrown by SQL Server based on the syntax
    of the error. If running the stored procedure directly in Query Analyzer
    with the same ODBC call syntax and parameters throws the error, then the
    problem is not in the JDBC driver itself but rather in SQL Server.
    Otherwise, the driver is responsible and and we'll investigate the issue
    further. Please post your JDBC code, stored proc, and relevant table
    structure.

    Carb Simien, MCSE MCDBA MCAD
    Microsoft Developer Support - Web Data

    Please reply only to the newsgroups.
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Are you secure? For information about the Strategic Technology Protection
    Program and to order your FREE Security Tool Kit, please visit
    http://www.microsoft.com/security.

  • Next message: Joseph Horowitz: "RE: Unexpected token type: ERROR"

    Relevant Pages

    • Re: How to get list of EventClasses in MSSQLServer2000
      ... statement inside a stored procedure has completed.') ... SQL Server statement or stored procedure.') ... Plan','Displays the plan tree of the Transact-SQL statement executed.') ... Login','Occurs when a SQL Server login is added or removed; ...
      (microsoft.public.sqlserver.security)
    • Re: Accessing FoxPro Free Table
      ... which the SQL Server service is running. ... account, ... > If you are creating a stored procedure and you want> to make sure that the procedure definition cannot be> viewed by other users, you can use the WITH ENCRYPTION> clause. ... The procedure definition is then stored in an> unreadable form. ...
      (microsoft.public.data.odbc)
    • Re: Problem with the Legacy ASP files and the Sql Server Express
      ... table, if another inserting occurs at the nearly exactly the same moment, ... SQL Server will pass it a default value. ... assume that you do have a connection that can reach SQL Server Express. ... unless your stored procedure has some thing that only works ...
      (microsoft.public.dotnet.framework.aspnet)
    • Stored procedure does not complete until result set is retrieved from ODBC
      ... I have a SQL Server Stored procedure that I am executing via ODBC. ... -- Start Code without cursor ...
      (microsoft.public.sqlserver.odbc)
    • Re: Adding date to parameter
      ... SqlClient provider which is specifically written for SQL Server. ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... I made the assumption that the stored procedure went by the ...
      (microsoft.public.dotnet.framework.adonet)