March 24, 2017 at 5:15 am
We have a web application that calls a select on a view stored on a Sql Server 2012 database. This query fails with the error "
New request is not allowed to start because it should come with valid transaction descriptorThis problem happens ONLY on a single customer database, the very same query executed on all the other customers schemas runs ok.
The SELECT statement is this:select distinct clienti.numeroCliente,clienti.ragioneSociale,clienti.partitaIva,clienti.codiceFiscale,
clienti.SedeLegale,
clienti.mail,clienti.codiceContabilita,clienti.riferimentiCliente
from dbo.view_table clienti
where clienti.azienda = 'Company_name'
If we restore the schema locally on other Sql Server instances we can replicate the error.
I searched a lot on the internet and all I found was an hotfix for Sql Server 2005 ( https://support.microsoft.com/en-us/help/939285/fix-error-message-when-you-run-a-stored-procedure-that-starts-a-transaction-that-contains-a-transact-sql-statement-in-sql-server-2005-new-request-is-not-allowed-to-start-because-it-should-come-with-valid-transaction-descriptor ) and some posts related to distributed queries and index fragmentation.
Given that I can replicate the issue what can I try to resolve it?
Thanks
March 24, 2017 at 6:36 am
Does your state run OK if you run the SQL on it's own and not inside the application? I'm not sure that's a SQL error, but rather a programming error. C# perhaps?
If the SQL runs fine in SSMS, the problem isn't the SQL, but how your application is running it, so you'd need your web dev to look into it from their side.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 24, 2017 at 7:49 am
Thom A - Friday, March 24, 2017 6:36 AMDoes your state run OK if you run the SQL on it's own and not inside the application? I'm not sure that's a SQL error, but rather a programming error. C# perhaps?If the SQL runs fine in SSMS, the problem isn't the SQL, but how your application is running it, so you'd need your web dev to look into it from their side.
Yes, sorry, totally forgot that part.
The query runs fine in SSMS, and fails only in the application side. The spring backend running the query is Java, a native sql query ran through Hibernate.
Java 8
Spring 4.1.1
Hibernate 4.3.5
This is the code:
@Transactional(readOnly=true, isolation=Isolation.READ_UNCOMMITTED)
public List<ControlloContratto> caricaControlloContratti(FilterControlloContratti filter) {
List<ControlloContratto> lstCtrlContratti = null;
String dwhExtractionCCPQuery = "";
Session session = sessionManager.getFilteredSession(FilterType.NONE);
dwhExtractionCCPQuery = caricaControlloContrattiQuery(filter);
Query queryCallSP = session.createSQLQuery(dwhExtractionCCPQuery);
queryCallSP.setResultTransformer(Transformers.aliasToBean(ControlloContratto.class));
lstCtrlContratti = queryCallSP.setReadOnly(true).list();
}
I tried to remove the customized isolation level and the read only annotation, the result is the same.
Thanks
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply