EDIT: solution found, look down.
编辑：找到解决方案，往下看。
We have a web application that calls a select on a view stored on a Sql Server 2012 database.
我们有一个Web应用程序，它调用存储在Sql Server 2012数据库上的视图上的select。
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.
在受影响的模式上在SSMS中自己执行的查询运行正常，仅在该特定模式的应用程序中失败。
The SELECT statement is this:
SELECT语句是这样的：
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.
如果我们在其他Sql Server实例上本地恢复架构，我们可以复制错误。
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.
我在互联网上搜索了很多，我找到的只是Sql Server 2005的修补程序（https://support.microsoft.com/en-us/help/939285/fix-error-message-when-you-run-a -stored - 程序 - 即-开始-A-交易是-包含-A-的Transact-SQL语句，在-SQL服务器2005年新请求时，不被允许到启动，因为，它-should-come-with-valid-transaction-descriptor）和一些与分布式查询和索引碎片相关的帖子。
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.
运行查询的spring后端是Java，一个通过Hibernate运行的本机sql查询。
Java 8
Java 8
Spring 4.1.1
春季4.1.1
Hibernate 4.3.5
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
编辑2：解决方案
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!
这个方法调用了一个存储过程，该存储过程仅在此特定模式上被窃听，简而言之，它在最后有一个错位的插入。卸下插件解决了这个问题！
1 个解决方案
#1
2
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
1 - 首先我们确定在同一个@transactional方法中有2个不同的查询，第一个是存储过程的EXEC，第二个是视图上的select
2 - we divided the queries in 2 separate @transactional methods
2 - 我们将查询分为2个独立的@transactional方法
3 - found out it was the first query that was crashing
3 - 发现它是第一个崩溃的查询
4 - analyzed the stored procedure to find the differences from the other working schemas
4 - 分析存储过程以找出与其他工作模式的差异
5 - found the misplaced insert
5 - 发现错位的插入物
Removing the insert solved the problem!
卸下插件解决了这个问题！
#1
2
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
1 - 首先我们确定在同一个@transactional方法中有2个不同的查询，第一个是存储过程的EXEC，第二个是视图上的select
2 - we divided the queries in 2 separate @transactional methods
2 - 我们将查询分为2个独立的@transactional方法
3 - found out it was the first query that was crashing
3 - 发现它是第一个崩溃的查询
4 - analyzed the stored procedure to find the differences from the other working schemas
4 - 分析存储过程以找出与其他工作模式的差异
5 - found the misplaced insert
5 - 发现错位的插入物
Removing the insert solved the problem!
卸下插件解决了这个问题！