JDBC call synonymous with Microsoft SQL Server stored procedure

Question

Using the JDBC driver provided by Microsoft (sqljdbc4.jar), I cannot call the stored procedure using the synonym defined for it. That is, for a synonym defined as:

CREATE SYNONYM dbo.synonym_name for dbo.procedure_name 

when starting the called statement created:

 CallableStatement callStmt = conn.prepareCall("{ call [dbo].[synonym_name] (?,?,?,?,?,?) }"); 

I get an exception:

 Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Parameter param_name was not defined for stored procedure [dbo].[synonym_name]. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171) at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.findColumn(SQLServerCallableStatement.java:1217) at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.setString(SQLServerCallableStatement.java:1563) at testmssql.main(testmssql.java:53) 

Even if the parameters are set correctly (if I call the procedure directly (bypassing the synonym), everything works fine).

Also, if I replaced the Microsoft JTDS driver, everything will be fine.

How to run CallableStatement using a synonym for a stored procedure with a Microsoft SQL Server JDBC driver?

Answer 1

SQL Server synonyms do not have query metadata. Judging by mistake, JDBC is trying to confirm that the parameters declared in the Java code correspond to the parameters declared in the stored procedure. This fails due to missing metadata.

The only way to create a backup stored procedure instead of a synonym. Therefore, if you have this procedure:

 CREATE PROCEDURE dbo.RealProcedure @p1 INT, @p2 INT AS BEGIN RAISERROR('TODO: implement me',16,10); END 

And you have this synonym:

 CREATE SYNONYM dbo.myProcedure FOR dbo.realProcedure; 

Drop the synonym and create this procedure instead:

 CREATE PROCEDURE dbo.myProcedure @p1 INT, @p2 INT AS BEGIN EXEC dbo.realProcedure @p1,@p2; END 

There is a similar problem described here: http://social.msdn.microsoft.com/Forums/en-us/sqldataaccess/thread/dcdfee17-a926-4b57-8641-ed86fec989f2