1

PROBLEM: I am having a problem regarding the database under the server in SQL Server which has Server Property > Connection > NOCOUNT ON (is ticked). The table has no trigger, absolutely nothing FYI.

What I am trying to do it just to do a simple insert using repository.save(entity) or entityManager.persist(entity) but I got an error:

Unexpected row count: -1; expected: 1.

I cannot get an entity out and edit it also => there will be an error

org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect)

I have tried to make a custom Dialect to insert a SET NOCOUNT OFF before every query but it didn't work.

The custom dialect:

package com.ata2.art22;

import org.hibernate.dialect.SQLServer2012Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;

public class CustomSQLServerDialect extends SQLServer2012Dialect {
    
    public CustomSQLServerDialect() {
        super();
        registerFunction("concat", new StandardSQLFunction("concat", StandardBasicTypes.STRING));
    }

    @Override
    public String getSequenceNextValString(String sequenceName) {
        return "SET NOCOUNT OFF;\n" + super.getSequenceNextValString(sequenceName);
    }
}

The property file setting:

spring.jpa.properties.hibernate.dialect=com.ata2.art22.CustomSQLServerDialect

QUESTION: Is there any other ways or changes I need to do to be able to use methods from repository or entityManager WITHOUT turning off the NOCOUNT ON at the Database Server configuration or using native query?

Dale K
  • 25,246
  • 15
  • 42
  • 71
infD
  • 43
  • 7
  • Have you tried using Extended Events or the SQL Profiler tool to capture the actual T-SQL that Spring is submitting to SQL Server? If you tried executing that captured code in SSMS you might get insights into what the real error is, because it sounds like something to do with the ORM mapping (identity columns, composite keys, etc.) not being setup correctly. – AlwaysLearning Apr 01 '23 at 03:17
  • I haven't tried Extended Events or the SQL Profiler tool yet but if I turn off NOCOUNT setting at Server Database Level then everything works normally. So I personally don't think it's something related to ORM mapping – infD Apr 01 '23 at 04:34
  • 1
    The `NOCOUNT ON` property in your question is not necessarily the server property. It is the initial value for your SQL Server Management Studio client connections, which will reflect the server configuration if set to a non-default value. The server-level default configuration is bit 512 of `user options` (`SELECT CASE WHEN CAST(value AS int) & 512 = 512 THEN 'NOCOUNT ON' ELSE 'NOCOUNT OFF' END FROM sys.configurations WHERE name = 'user options';`). – Dan Guzman Apr 01 '23 at 10:43
  • Thanks @DanGuzman I don't really know how to describe it "professionally" but is there any ways to deal with my problem? – infD Apr 01 '23 at 13:15
  • @infD, as already suggested, use a trace to capture the actual requests generated by the ORM. – Dan Guzman Apr 01 '23 at 13:23
  • @DanGuzman Could you please go into a bit more detail? I'm sorry I don't really understand what you're saying – infD Apr 01 '23 at 13:46
  • @infD, [these instructions](https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-ssms-xe-profiler) describe how to run a TSQL xEvent Profiler session from SSMS. – Dan Guzman Apr 01 '23 at 13:54
  • Aw thank you, you were using another account I believe. – infD Apr 01 '23 at 13:55
  • @DanGuzman nothing fishy about the ORM mapping as I observed, but thank you for your suggestion – infD Apr 01 '23 at 14:14

0 Answers0