"New request is not allowed to start because it should come with valid transaction descriptor" on Sql Server 2012

Question

EDIT: solution found, look down.

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 descriptor"

This problem happens ONLY on a single customer database, the very same query executed on all the other customers schemas runs ok.

The query executed on his own in SSMS on the affected schema runs ok, fails only in the application on that particular schema.

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?

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.

EDIT: Adding the script for the creation of the called view

CREATE VIEW [dbo].[e3_dwh_view_clienti] AS
SELECT COALESCE(azienda.nome,'') azienda,
       cliente.id idCliente,
       cliente.numero numeroCliente,
       cliente.ragione_sociale ragioneSociale,
       COALESCE(cliente.partita_iva,'') partitaIva,
       COALESCE(cliente.codice_fiscale,'') codiceFiscale,
       natura.descrizione naturaCliente,
       COALESCE((CASE WHEN tipoVia.descrizione IS NOT NULL AND pntStrad.fk_e0_decod_viario IS NULL
            THEN tipoVia.descrizione 
             WHEN tipoVia.descrizione IS NULL AND pntStrad.fk_e0_decod_viario IS NULL
            THEN ''
             ELSE tipoVia2.descrizione 
         END),'') toponimo,
       COALESCE((CASE WHEN tipoVia.descrizione IS NOT NULL AND pntStrad.fk_e0_decod_viario IS NULL
            THEN pntStrad.indirizzo + ' ' + (CASE WHEN pntStrad.numero_civico IS NULL THEN '' ELSE pntStrad.numero_civico END)
              + (CASE WHEN pntStrad.esponente_civico IS NULL THEN '' ELSE '/'+pntStrad.esponente_civico END) 
             WHEN tipoVia.descrizione IS NULL AND pntStrad.fk_e0_decod_viario IS NULL
            THEN  pntStrad.indirizzo + ' ' + (CASE WHEN pntStrad.numero_civico IS NULL THEN '' ELSE pntStrad.numero_civico END)
              + (CASE WHEN pntStrad.esponente_civico IS NULL THEN '' ELSE '/'+pntStrad.esponente_civico END)
             ELSE viario.denominazione + ' ' + (CASE WHEN pntStrad.numero_civico IS NULL THEN '' ELSE pntStrad.numero_civico END)
              + (CASE WHEN pntStrad.esponente_civico IS NULL THEN '' ELSE '/'+pntStrad.esponente_civico END)
         END),'') indirizzo,
       COALESCE(cap.codice,COALESCE(comune.cap,'')) cap,
       COALESCE(comune.comune,'') comune, 
       COALESCE(comune.provincia,'') provincia,
       COALESCE((CASE WHEN tipoVia.descrizione IS NOT NULL AND pntStrad.fk_e0_decod_viario IS NULL
    THEN tipoVia.descrizione + ' ' + pntStrad.indirizzo + ' ' + (CASE WHEN pntStrad.numero_civico IS NULL THEN '' ELSE pntStrad.numero_civico END)
      + (CASE WHEN pntStrad.esponente_civico IS NULL THEN '' ELSE '/'+pntStrad.esponente_civico END) +  ', ' + 
        (CASE WHEN cap.codice IS NULL THEN '' ELSE cap.codice END)+ ' '+ (CASE WHEN comune.comune IS NULL THEN '' ELSE comune.comune END)
     WHEN tipoVia.descrizione IS NULL AND pntStrad.fk_e0_decod_viario IS NULL
    THEN  pntStrad.indirizzo + ' ' + (CASE WHEN pntStrad.numero_civico IS NULL THEN '' ELSE pntStrad.numero_civico END)
      + (CASE WHEN pntStrad.esponente_civico IS NULL THEN '' ELSE '/'+pntStrad.esponente_civico END) +  ', ' + 
        (CASE WHEN cap.codice IS NULL THEN '' ELSE cap.codice END)+ ' '+ (CASE WHEN comune.comune IS NULL THEN '' ELSE comune.comune END)
     ELSE tipoVia2.descrizione + ' ' + viario.denominazione + ' ' + (CASE WHEN pntStrad.numero_civico IS NULL THEN '' ELSE pntStrad.numero_civico END)
      + (CASE WHEN pntStrad.esponente_civico IS NULL THEN '' ELSE '/'+pntStrad.esponente_civico END) +  ', ' + 
        (CASE WHEN cap.codice IS NULL THEN '' ELSE cap.codice END)+ ' '+ (CASE WHEN comune.comune IS NULL THEN '' ELSE comune.comune END)
  END),'') SedeLegale,
        CAST(cliente.data_cliente_dal AS DATE) dataClienteDal,
        CAST(cliente.dt_inserimento AS DATE) dataInserimento,
        CAST(cliente.dt_modifica AS DATE) dataModifica,
       COALESCE(cliente.nome,'') nome,
       COALESCE(cliente.cognome,'') cognome,
       CAST(COALESCE((SELECT DISTINCT 1 FROM e2_com_contratti k
          JOIN e2_com_prestazioni p ON p.fk_e2_com_contratto  = k.id
         WHERE k.fk_e2_com_cliente = cliente.id 
           AND k.fk_e0_prof_nodo = cliDett.fk_e0_prof_nodo
           AND p.dt_scadenza >= GETDATE()),0) AS BIT) flagAttivo,
       CAST(cliente.fl_piva_duplicata AS BIT) flagPartIvaDupl,
       CAST(cliente.fl_spacement_firma AS BIT) flagSpacement,
       CAST(cliente.fl_importante AS BIT) flagImportante,
       CAST(cliente.fl_mail_fattura AS BIT) flagMail,
       CAST(COALESCE(STUFF((SELECT '; '+ COALESCE(mail.email,'') 
        FROM (
            SELECT DISTINCT c.id,n.id nodo,c.mail_cliente  email FROM e2_com_clienti c
            JOIN e0_prof_nodi n ON n.fk_e0_prof_tipo_nodo = 1
            WHERE c.mail_cliente <> ' '
            UNION
            SELECT DISTINCT c.id,d.fk_e0_prof_nodo nodo,d.mail_fattura email FROM e2_com_clienti c
            JOIN e2_com_contab_clienti_dett d ON d.fk_e2_com_cliente = c.id
            WHERE d.mail_fattura <> ' '
            UNION
            SELECT DISTINCT c.id,w.fk_e0_prof_nodo nodo,w.indirizzo_mail email FROM e2_com_clienti c
            JOIN e2_amm_wizard_fatture w ON w.fk_e2_com_clienti = c.id 
            WHERE w.indirizzo_mail <> ' '
            ) mail
       WHERE mail.id = cliente.id
         AND mail.nodo = cliDett.fk_e0_prof_nodo
       FOR XML PATH ('')),1,2,''),'') AS VARCHAR(MAX)) mail,
       CAST(cliente.fl_fax_fattura AS BIT) flagFax,
       COALESCE(cliente.fax_fattura,'') fax,
       CAST(cliente.fl_postalizzazione AS BIT) flagPosta,
       CAST(cliente.fl_sogg_ritenuta AS BIT) flagRitenuta,
       COALESCE(ritenuta.descrizione,'') ritenAcconto,
       COALESCE(cliDett.conto_cliente,'') contoCliente,
       COALESCE(cliDett.codice_contabilita,'') codiceContabilita,
       CAST(COALESCE(cliDett.fl_dichiarazione_intenti,0) AS BIT) flagDicIntenti,
       COALESCE(cliDett.numero_dic_int,'') numDicIntenti,
        CAST(cliDett.dt_rilascio_dic_int AS DATE) dataRilascioDicIntenti,
        CAST(cliDett.dt_decorrenza_dic_int AS DATE) dataDecorDicIntenti,
        CAST(cliDett.dt_scadenza_dic_int AS DATE) dataScadDicIntenti,
       CAST(COALESCE(cliDett.fl_importante_fatt,0) AS BIT) flagImportanteFatt,
       CAST(COALESCE(cliDett.fl_autocertif_f24,0) AS BIT) flagAutoCertF24,
       CAST(COALESCE(cliDett.fl_cliente_contabilizzato,0) AS BIT) flagCliContab,
       COALESCE(canale.descrizione,'') canale,
       COALESCE(grInd.descrizione,'') gruppoInd,
       COALESCE(segComm.descrizione,'') segComm,
       COALESCE(ateco.descrizione,'') categMerceologica,
       COALESCE(cliente.note,'') noteCliente,
       CAST(COALESCE(STUFF((SELECT DISTINCT '; ' + COALESCE(g.nome,'')
                FROM e2_com_gestori_clienti gc
                JOIN e0_decod_gestori g ON g.id = gc.fk_e2_com_gestore
               WHERE gc.fk_e2_com_cliente = cliente.id
               FOR XML PATH ('')),1,2,''),'') AS VARCHAR(MAX)) gestoreCliente,
       CAST(COALESCE(STUFF((SELECT DISTINCT '; ' + COALESCE(ic.codice,'') + ' ('+COALESCE(ic.codice_mandato,'')+')'
                FROM e2_com_iban_cliente_dett ic
               WHERE ic.fk_e2_com_cont_cliente = cliDett.id
               FOR XML PATH ('')),1,2,''),'') AS VARCHAR(MAX)) ibanCliente,
       CAST(COALESCE(STUFF((SELECT DISTINCT '; ' + cc.nome + ' - ' + tcc.recapito
                FROM e2_com_contatti_clienti cc
                JOIN e2_com_tipo_contatti_cliente tcc ON tcc.fk_e2_com_contatto_cliente = cc.id
               WHERE cc.fk_e2_com_cliente = cliente.id
               FOR XML PATH ('')),1,2,''),'') AS VARCHAR(MAX))riferimentiCliente,
        CAST(COALESCE(STUFF((SELECT '; '+ modiPag.descrizione 
                FROM (
                    SELECT DISTINCT c.id,w.fk_e0_prof_nodo,mp.descrizione FROM e2_com_clienti c
                    JOIN e2_amm_wizard_fatture w ON w.fk_e2_com_clienti = c.id 
                    JOIN e0_decod_modi_pagamento mp ON mp.id = w.fk_e0_decod_modo_pagamento
                    JOIN e2_com_contratti k ON k.fk_e2_com_cliente = c.id AND k.fk_e0_prof_nodo = w.fk_e0_prof_nodo
                    JOIN e2_com_prestazioni p ON p.fk_e2_com_contratto = k.id AND p.codiceAggregazione = w.codice_aggregazione
                    WHERE p.dt_scadenza >= GETDATE()) modiPag
               WHERE modiPag.id = cliente.id
                 AND modiPag.fk_e0_prof_nodo = cliDett.fk_e0_prof_nodo
               FOR XML PATH ('')),1,2,''),'') AS VARCHAR(MAX)) modoPagamento,
       COALESCE(statoCredCliente.descrizione,'') statoCreditoCliente,
       COALESCE(iva.descrizione,'') aliquotaIva,
       CAST(COALESCE(STUFF((SELECT '; '+ COALESCE(codPA.codUnivoco,'')
        FROM (
            SELECT c.id,k.fk_e0_prof_nodo,k.cod_destinatario  codUnivoco FROM e2_com_clienti c
            JOIN e2_com_contratti k ON k.fk_e2_com_cliente = c.id
            WHERE k.fk_e0_decod_stato_contratto = '1'
            UNION
            SELECT c.id,d.fk_e0_prof_nodo,d.cod_destinatario codUnivoco FROM e2_com_clienti c
            JOIN e2_com_contab_clienti_dett d ON d.fk_e2_com_cliente = c.id
            UNION
            SELECT c.id,w.fk_e0_prof_nodo,w.cod_destinatario codUnivoco FROM e2_com_clienti c
            JOIN e2_amm_wizard_fatture w ON w.fk_e2_com_clienti = c.id 
            ) codPA
       WHERE codPA.id = cliente.id
         AND codPA.fk_e0_prof_nodo = cliDett.fk_e0_prof_nodo
       FOR XML PATH ('')),1,2,''),'') AS VARCHAR(MAX)) codiceUnivocoPA
  FROM e2_com_clienti cliente 
  JOIN e0_conf_natura_clienti natura ON natura.id = cliente.fk_e0_natura_cliente
    LEFT JOIN e2_com_contab_clienti_dett cliDett ON cliDett.fk_e2_com_cliente = cliente.id AND cliDett.fl_cliente = 1
    LEFT JOIN e0_prof_nodi azienda ON azienda.id = cliDett.fk_e0_prof_nodo
    LEFT JOIN e0_decod_ritenuta ritenuta ON ritenuta.id = cliDett.fk_e0_decod_ritenuta
    LEFT JOIN e0_decod_tipi_cliente canale ON canale.id = cliente.fk_e0_decod_tipo_cliente
    LEFT JOIN e0_decod_gr_ind_com grInd ON grInd.id = cliente.fk_e0_decod_gp_ind_com
    LEFT JOIN e0_decod_segmento_com segComm ON segComm.id = cliente.fk_e0_decod_seg_com
    LEFT JOIN e0_decod_codici_ateco ateco ON ateco.id = cliente.fk_e0_decod_codice_ateco
    LEFT JOIN e2_com_ubicazioni_clienti sede ON sede.fk_e2_com_cliente = cliente.id AND sede.fl_primario = 1
    LEFT JOIN e2_com_punti_stradario pntStrad ON sede.fk_e2_com_pt_stradario = pntStrad.id
    LEFT JOIN e0_conf_tipi_via tipoVia ON pntStrad.fk_e0_decod_tipo_via = tipoVia.id
    LEFT JOIN e0_conf_comuni_cap cap ON pntStrad.fk_e0_conf_cap_comune = cap.id
    LEFT JOIN e0_conf_comuni comune ON cap.fk_e0_conf_comune = comune.codice_catastale
    LEFT JOIN e0_conf_viario viario ON pntStrad.fk_e0_decod_viario = viario.id
    LEFT JOIN e0_conf_tipi_via tipoVia2 ON viario.fk_e0_decod_tipo_via = tipoVia2.id
    LEFT JOIN e0_decod_stati_clienti statoCredCliente ON statoCredCliente.id = cliDett.fk_e0_decod_stato_cliente
    LEFT JOIN e0_decod_iva iva ON iva.id = cliDett.fk_e0_decod_iva
  WHERE cliente.fl_cliente = 1

Thanks

EDIT 2: SOLUTION

After several tries we understood that the problem arised not from the select on the view, but from another query that was executed in the same transaction, the one called after this method:

dwhExtractionCCPQuery = caricaControlloContrattiQuery(filter);

This method called a stored procedure that was bugged ONLY on this particular schema, in short it had a misplaced insert at the end. Removing the insert solved the problem!

Answer 1

After several tries we understood that the problem arised not from the select on the view, but from another query that was executed in the same transaction, the one called after this method:

dwhExtractionCCPQuery = caricaControlloContrattiQuery(filter);

This method called a stored procedure that was bugged ONLY on this particular schema, in short it had a misplaced insert at the end of the stored procedure.

We found the solution in steps:

1 - first we identified that in the same @transactional method there where 2 different queries, the first was the EXEC of a stored procedure and the second the select on the view

2 - we divided the queries in 2 separate @transactional methods

3 - found out it was the first query that was crashing

4 - analyzed the stored procedure to find the differences from the other working schemas

5 - found the misplaced insert

Removing the insert solved the problem!