Create/Call local temporary procedures

timk

unread,

Nov 17, 2006, 5:27:01 PM11/17/06

to

Statement statement = conn.createStatement();
statement.execute("CREATE PROCEDURE #ONE AS BEGIN SELECT 'ONE' END");
statement = conn.createStatement();
statement.execute("{CALL #ONE}");

Throws this exception
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Could not find stored procedure '#ONE'.
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.TDSExecuteRequest.processReplyToken(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at
com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeInternal(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.execute(Unknown Source)
---------------------------------------------------------------------------------------------
I can create a global temporary procedure easily. (A global temporary
procedure uses two number signs ##.)

Jaaved Mohammed

unread,

Nov 18, 2006, 5:45:52 AM11/18/06

to

Hi Timk,

Does the problem also repro the Microsoft SQL Server 2005 JDBC Driver v1.1?
You can download it from http://msdn.microsoft.com/data/ref/jdbc/. It is
compatible with both SQL Server 2000 and 2005.

Regards,
Jaaved Mohammed

Robert Klemme

unread,

Nov 21, 2006, 3:11:29 AM11/21/06

to

An additional hint: you're not closing the first statement properly. In
fact, you do not even need to create a new Statement in order to execute
the call.

Cheers

robert

timk

unread,

Nov 21, 2006, 11:32:01 AM11/21/06

to

"Robert Klemme" wrote:

Thanks robert, That's a good idea to close the statements. I failed to
mention that the problem only shows up if there are multiple statements.

My real procedure has input and output parameters, so the create is a
Statement object and the call is a separate Callable object.

I could not find a way to create a single Statement/Callable object that
worked with the old driver. I thought it was a problem with parameters, but
it turned out to be a problem with multiple statements

joe.we...@gmail.com

unread,

Nov 21, 2006, 11:45:40 AM11/21/06

to

On Nov 21, 8:32 am, timk <t...@discussions.microsoft.com> wrote:
> Thanks robert, That's a good idea to close the statements. I failed to
> mention that the problem only shows up if there are multiple statements.
>
> My real procedure has input and output parameters, so the create is a
> Statement object and the call is a separate Callable object.
>
> I could not find a way to create a single Statement/Callable object that
> worked with the old driver. I thought it was a problem with parameters, but
> it turned out to be a problem with multiple statements

This will probably go away if you upgrade to the latest driver from MS.
In the meantime, if you add the connection property
selectMethod=cursor,
the problem will also go away. The reason is that under the covers, the
old driver will sometimes make parallel connections, just to make
concurrent queries with multiple statements! These different
connections
are different DBMS sessions so they can't see each others' temp tables.

Joe Weinstein at BEA Systems