title: "New request is not allowed to start because it should come with valid transaction descriptor" on Sql Server 2012
body: <p>EDIT: solution found, look down.</p>
  
  <p>We have a web application that calls a select on a view stored on a <strong>Sql Server 2012</strong> database. </p>
  
  <p>This query fails with the error </p>
  
  <pre><code>"New request is not allowed to start because it should come with valid transaction descriptor"
  </code></pre>
  
  <p><strong>This problem happens ONLY on a single customer database</strong>, the very same query executed on all the other customers schemas runs ok.</p>
  
  <p>The query executed on his own in SSMS on the affected schema runs ok, fails only in the application on that particular schema.</p>
  
  <p>The SELECT statement is this:</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>If we restore the schema locally on other Sql Server instances we can replicate the error.</p>
  
  <p>I searched a lot on the internet and all I found was an hotfix for Sql Server 2005 ( <a href="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" rel="nofollow noreferrer">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</a> ) and some posts related to distributed queries and index fragmentation.</p>
  
  <p>Given that I can replicate the issue what can I try to resolve it?</p>
  
  <p>The spring backend running the query is Java, a native sql query ran through Hibernate.</p>
  
  <p><strong><em>Java 8</em></strong></p>
  
  <p><strong><em>Spring 4.1.1</em></strong></p>
  
  <p><strong><em>Hibernate 4.3.5</em></strong></p>
  
  <p>This is the code:</p>
  
  <pre><code>@Transactional(readOnly=true, isolation=Isolation.READ_UNCOMMITTED)
      public List&lt;ControlloContratto&gt; caricaControlloContratti(FilterControlloContratti filter) {
          List&lt;ControlloContratto&gt; 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();
  
              }
  </code></pre>
  
  <p>I tried to remove the customized isolation level and the read only annotation, the result is the same.</p>
  
  <p><strong>EDIT: Adding the script for the creation of the called view</strong></p>
  
  <pre><code>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 &gt;= 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 &lt;&gt; ' '
              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 &lt;&gt; ' '
              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 &lt;&gt; ' '
              ) 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 &gt;= 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
  </code></pre>
  
  <p>Thanks </p>
  
  <p><strong>EDIT 2: SOLUTION</strong></p>
  
  <p>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:</p>
  
  <pre><code>dwhExtractionCCPQuery = caricaControlloContrattiQuery(filter);
  </code></pre>
  
  <p>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! </p>