"Could not find stored procedure" exception

[Chris Y Chen]

Hi All,

I've been struggling with trying to call a stored procedure on MSSQL server. Everytime I use CallableStatement to call the stored procedure, I get:

com.microsoft.sqlserver.jdbc.SQLServerException: Could not find stored procedure 'TESTDB.dbo.PROCESSING'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(Unknown Source)
...

I am using Microsoft SQL Server 2005 driver "sqljdbc_1.2".

What puzzles me is that I can successfully invoke the stored procedure using MyEclipse Database Explorer, which uses the same connection driver and connection string.

Does anyone know what can be the cause of the exception?

Any suggestion is appreciated!

Thanks,
Chris

Here's the code:

Connection connection = null;
CallableStatement statement = null;
    try{
        Context ctx = new InitialContext();
        if(ctx == null ) 
            throw new Exception("No Context");
 
        DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/MSSQL");
 
        if (ds != null) {
            connection = ds.getConnection();
        }
    }catch(Exception e) {
        e.printStackTrace();
    }
    try {
        statement = connection.prepareCall("{call TESTDB.dbo.PROCESSING(?,?)}");
        statement.setString(1,"1234567890");
        statement.setString(2, "TEST"); 
        statement.registerOutParameter(1, java.sql.Types.VARCHAR);
        statement.registerOutParameter(2, java.sql.Types.VARCHAR);
        statement.execute();
        System.out.println(statement.getString(1));
        System.out.println(statement.getString(2));
    }catch( SQLException e ) {
        e.printStackTrace();
    }catch(Exception e){
        e.printStackTrace();
    }

[Sathi Krishna]

Just try with

statement = connection.prepareCall("{call TESTDB.dbo.PROCESSING(?,?)}");
or
statement = connection.prepareCall("{call dbo.PROCESSING(?,?)}");
or
statement = connection.prepareCall("{call PROCESSING(?,?)}");

May be your storedprocedure resides in different schema.

[Chris Y Chen]

Thanks for your suggestion.
I've tried different combinations just like you suggested but have gotten the same exception.

statement = connection.prepareCall("{call TESTDB.dbo.PROCESSING(?,?)}");
or
statement = connection.prepareCall("{call dbo.PROCESSING(?,?)}");
or
statement = connection.prepareCall("{call PROCESSING(?,?)}");

I was able to run
Execute TESTDB.dbo.PROCESSING '1234567890' 'TEST';
Execute dbo.PROCESSING '1234567890' 'TEST';
Execute PROCESSING '1234567890' 'TEST';
on MyEclipse Database Explorer and they all produce the right result.

I still can't get the CallableStatement to work...