Misleading SQLServerException: The value is not set for the parameter number N

[cnsgithub]

Driver version

7.2.2.jre11 (also tested with 7.0.0.jre10)

SQL Server version

Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64) Jul 6 2018 18:24:36 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Client Operating System

Windows 10

JAVA/JVM version

JDK 11

Table schema

create database test_sp
go

use test_sp
go

create proc test @id int,@str varchar(max) as select @id,@str
go

create login reader with password='password_reader',default_database=test_sp
create user reader for login reader
alter role db_datareader add member reader
go

create login executor with password='password_executor',default_database=test_sp
create user executor for login executor
alter role db_datareader add member executor
grant execute on test to executor
go

Problem description

When calling a stored procedure with at least two parameters from JDBC under insufficient rights, the error message is misleading.

1. Expected behaviour: SQLServerException with message The EXECUTE permission was denied on the object 'test'
2. Actual behaviour: SQLServerException with message The value is not set for the parameter number 2.
3. Error message/stack trace:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 2.
        at com.microsoft//com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:226)
        at com.microsoft//com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildParamTypeDefinitions(SQLServerPreparedStatement.java:416)
        at com.microsoft//com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildPreparedStrings(SQLServerPreparedStatement.java:369)
        at com.microsoft//com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:540)
        at com.microsoft//com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:508)
        at com.microsoft//com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240)
        at com.microsoft//com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)
        at com.microsoft//com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243)
        at com.microsoft//com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218)
        at com.microsoft//com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:493)

4. Any other details that can be helpful:
   Similar issue: Misleading SQLServerException: Parameter was not defined for stored procedure #608.

JDBC trace logs

n/a

Reproduction code

I created a small github repo to reproduce the issue: https://github.com/cnsgithub/sqljdbc. Just clone the repo, setup the DB schema and execute the unit test using plain JDBC.

[rene-ye]

Hi @cnsgithub, thanks for making the team aware of the issue and providing a detailed reproduction. I've managed to replicate the issue described in this thread and will look into it.

[ulvii]

Fixed in #1064

[lewisMachilika]

What was the solution?

[Nikitomas]

@ulvii I get an Exception that says:
The value is not set for the parameter number 33.
But i dont have 33 parameters! I only have 32 and i set the 32.
Any ideas?

[Jeffery-Wasty]

Hi @Nikitomas,

What version of the driver are you using?

[Jeffery-Wasty]

@Nikitomas,

If you still have this issue, please feel free to open a new issue. We prefer having new issues opened as opposed to commenting on closed issues.

Thanks!