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.
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
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.
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.