Tag Archives: SQLServerException

JDBC Issues & Solutions, SQLServer Issues & Solutions

[Solved] SQLServerException: The definition of object ‘XYZ’ has changed since it was compiled.

Leave a comment

JDBC/JPA throws this exception on runtime “com.microsoft.sqlserver.jdbc.SQLServerException: The definition of object ‘XYZ’ has changed since it was compiled.” when call the stored procedure from JAVA code. There can be multiple reason of this issue.

Reason of Exception

This issue occurred with SQL Server or DB objects because these pre-compiled and persist in DB. In your team member or other person opened any tool to modify the object, or opened the Object in any tool in editable mode then SQL Server maintain the state/versioning of changes but not save or executed. Now when your applications run then SQLServer is confused which version of SQL Object need to execute and throw this exception “SQLServerException: The definition of object ‘XYZ’ has changed since it was compiled“.

  • This issue can be occured by parameter sniffing also.
  • or Stored Procedure/ SQL Object opened in editable mode in any tool.

Solutions:

To solve this problem there are following ways:

Solution 1: You can execute the stored procedure/SQL object with sp_recompile from JDBC Java code as below:

call sp_recompile XYZ_SP

Solution 2: You can alter the Stored Procedure as below syntax with WITH RECOMPILE

Syntax:

create procedure <proc_name>
[<parameters]>
WITH RECOMPILE
AS 
<codes>

Solution 3: Recreate the stored procedure/ SQL Object with new name and change the reference of this SQL object new name on all the places.

Recommendation

The Solution 1 and Solution 2 both are having drawback as all the time before executing the stored procedure will re-compile the object. Solution 3 is good in case if you know all the references of uses of stored procedure/ SQL Object in your application/all applications.

Related Posts

Your Feedback Motivate Us

If our FacingIssuesOnIT Experts solutions guide you to resolve your issues and improve your knowledge. Please share your comments, like and subscribe to get notifications for our posts.

Happy Learning !!!

JDBC, SQLServer Issues & Solutions

[Solved] SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “No appropriate protocol (protocol is disabled or cipher suites are inappropriate)

Leave a comment

This issue “SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “No appropriate protocol (protocol is disabled or cipher suites are inappropriate)” was happening to me when upgrade from jDK 8_271 to JDK_8_341 and MSSQL driver.

Below are the stacktrace of the issue.

Stacktrace

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "No appropriate protocol (protocol is disabled or cipher suites are inappropriate)".
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1368)
	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1412)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1058)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:833)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:716)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:841)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:153)
	at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:144)
	at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:155)
	at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:120)
	at org.springframework.batch.item.database.AbstractCursorItemReader.initializeConnection(AbstractCursorItemReader.java:422)
	... 30 more
Caused by: javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate)
	at sun.security.ssl.HandshakeContext.<init>(HandshakeContext.java:171)
	at sun.security.ssl.ClientHandshakeContext.<init>(ClientHandshakeContext.java:106)
	at sun.security.ssl.TransportContext.kickstart(TransportContext.java:238)
	at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:410)
	at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:389)
	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1379)
	... 41 more

Solutions

SQL Server JDBC Driver versions are specific to JDK versions. Till JDK_8.271, JDBC driver versions supported as 4.1 however for the latest version JDK_8_341 use the SQL Server JDBC driver 8.4.1.

In my case this issue got resolved by updating the jar file as below:

Old Jar : mssql-jdbc4-2.0.jar

New Jar: mssql-jdbc-8.4.1.jre8.jar

Please refer below the complete SQLServer JDBC driver support matrix specific to Java/JDK versions.

https://learn.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-ver16#java-and-jdbc-specification-support

Please share your comments if this solutions help you to resolve this issue.

Happy Learning !!!

Spring Data JPA Issues & Solutions, SQLServer Issues & Solutions

[Solved] SQLServerException: The TCP/IP connection to the host ABCKXYZ356, port 1345 has failed

Leave a comment

This is most common exception when connecting with database through Spring boot application. It’s happen because of connection failed to the database.

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host ABCKXYZ356, port 1345 has failed. Error: "ABCKXYZ356. 
Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. 
Make sure that TCP connections to the port are not blocked by a firewall.".    
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) ~[mssql-jdbc-8.2.1.jre8.jar:na]    
at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:285) ~[mssql-jdbc-8.2.1.jre8.jar:na]    
at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2434) ~[mssql-jdbc-8.2.1.jre8.jar:na]    
at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:659) ~[mssql-jdbc-8.2.1.jre8.jar:na]    
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2546) ~[mssql-jdbc-8.2.1.jre8.jar:na]    
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2216) ~[mssql-jdbc-8.2.1.jre8.jar:na]    
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2067) ~[mssql-jdbc-8.2.1.jre8.jar:na]    
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1204) ~[mssql-jdbc-8.2.1.jre8.jar:na]    
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:825) ~[mssql-jdbc-8.2.1.jre8.jar:na]    
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-4.0.3.jar:na]    
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364) ~[HikariCP-4.0.3.jar:na]    
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-4.0.3.jar:na]    
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476) ~[HikariCP-4.0.3.jar:na]    
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561) ~[HikariCP-4.0.3.jar:na]    
at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) ~[HikariCP-4.0.3.jar:na]

Reason of Exception

These are main reason of this Exception:

  • This issue can occurred because of wrong properties configured for database connection.
  • This issue can also occurred if the database is not running.
  • This issue can also occurred if database only allow to access through vpn.

Solutions

These are some most common solution to resolve this issue:

  • Verify the connection properties.
  • Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port.
  • Make sure that TCP connections to the port are not blocked by a firewall.
  • Check the vpn connection if DB access allow over the vpn.

Related Posts

Your Feedback Motivate Us

If our FacingIssuesOnIT Experts solutions guide you to resolve your issues and improve your knowledge. Please share your comments, like and subscribe to get notifications for our posts.

Happy Learning !!!

SQLServer Issues & Solutions

[Solved] SQLServerException: Arithmetic overflow error converting float to data type numeric :scientific value

Leave a comment

This SQLServerException is common with the applications using the MSSQL database. Once it occurs it generate the below stackTrace.

Exception Stack Trace

com.microsoft.sqlserver.jdbc.SQLServerException: Arithmetic overflow error converting float to data type numeric.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

Reason of Exception

This issue occurred when your precision and scale argument is set two small for decimal column. For Example: decimal(3,2) or float(3,2) . It will float allow maximum value as 9.99

Basically the first argument (precision) is the max number of digits (in this case 3) and the second argument (scale) is the number of digits to the right of the decimal point, which always takes away from the number of digits you can have to the left of the decimal point. So in this case, 3-2 = 1 digit allowed to the left of the decimal point, which is why allow max value can only be 9.99.

if you will try numeric value more than this size Ex : 10.9 then SQL server will throw exception as “SQLServerException: Arithmetic overflow error converting float to data type numeric” because size is more than the defined precision and scale of decimal point.

Note : This issue can also be occurred when you are trying to insert big values in form of scientific notation Ex: 1.5e100 because it’s precision value is high so throw exception.

Solutions

You can follow any of these processes to resolve this issue:

  • Increase the precision and scale of decimal of your column based on your business requirement.
  • Convert the column type as float value so that accept values as per the set precision and scale.
  • You can also use try_cast also to truncate and change the type of passing value.

Hope these processes resolved this issue. Please share your response in comments.

Happy Learning !!!

SQLServer Issues & Solutions

[Solved] SQLServerException: Arithmetic overflow error converting float to data type numeric

Leave a comment

This SQLServerException is common with the applications using the MSSQL database. Once it occurs it generate the below stackTrace.

Exception Stack Trace

com.microsoft.sqlserver.jdbc.SQLServerException: Arithmetic overflow error converting float to data type numeric.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

Reason of Exception

This issue occurred when your precision and scale argument is set two small for decimal column. For Example: NUMERIC(3,2) or DECIMAL(3,2) . It will allow maximum value as 9.99

Basically the first argument (precision) is the max number of digits (in this case 3) and the second argument (scale) is the number of digits to the right of the decimal point, which always takes away from the number of digits you can have to the left of the decimal point. So in this case, 3-2 = 1 digit allowed to the left of the decimal point, which is why allow max value can only be 9.99.

if you will try numeric value more than this size Ex : 10.9 then SQL server will throw exception as “SQLServerException: Arithmetic overflow error converting float to data type numeric” because size is more than the defined precision and scale of decimal point.

Solutions

You can follow any of these processes to resolve this issue:

  • Increase the precision and scale of decimal of your column based on your business requirement.
  • Convert the column type as float value so that accept values as per the set precision and scale.

Hope these processes resolved this issue. Please share your response in comments.

Happy Learning !!!

JDBC, RDBMS, SQLServer Issues & Solutions

[Solved] SQLServerException: String or binary data would be truncated

Leave a comment

This SQLServerException is common with the applications using the MSSQL database. Once it occurs it generate the below stackTrace.

Exception Stack Trace

com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

Reason of Exception

This exception occurred when you are trying to insert text in a column of type varchar which is more than the size of defined column size then SQL server through this exception “SQLServerException: String or binary data would be truncated“.

Solutions

You can follow any of these processes to resolve this issue:

  • Apply validation for text length on the source frontend/client where you in insert the values. It should be less than or equal to size of column.
  • Apply truncation on text before inserting to the database and it should be less than the column size.
  • Increase the sufficient size of the column based on you requirement to resolve this issue.

Hope these processes resolved this issue. Please share your response in comments.

Happy Learning !!!

Java, JDBC

[Solved] com.microsoft.sqlserver.jdbc. SQLServerException: Invalid object name ‘dual’

Leave a comment

Below com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘dual’ exception example is for SQLServer for IBATIS and Hibernate but same can occurred in JDBC also.

Example



      select 1 from dual

Exception Stack

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'dual'.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)

Issue

SQLServer does not support dual table.

Solution

In SQLServer there is no dual table. If you want to use that then there is no need from keyword. We can use it as below


select 1

Note : In MySQL and Oracle need a FROM clause to use where clause while in SQL Server allow WHERE clause directly with out FROM clause.

For Example:
Oracle
SELECT 123 FROM DUAL WHERE 1<2
SQL Server
SELECT 123 WHERE 1<2

More Issues Solution

For more other JAVA/JDBC issues solution follow link JAVA/JDBC Issues.