Bonjour à tous,

Depuis un fichier excel j'interroge une base sql server (2017) , mais je rencontre une erreur "la transaction a été bloquée sur les ressources verrou - MS ..."

voici la requete:

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
SELECT 
societe,
magasin, 
marque,
Case Muerto WHEN 1 then '2-Stock Mort' else '1-Stock Vivant' End As Muerto ,
Case Rango 
WHEN 1 then '1-Mois 6 mois'
WHEN 2 then '2-Mois 6-12 mois'
WHEN 3 then '3-Mois 12-24 mois'
WHEN 4 then '4-Mois + 24 mois' End as Rango ,
SUM(CantidadStock) as qte,
SUM((PCPMAlmacen)* CantidadStock) as "Pamp",
SUM((taHistorico_PrecioVenta)* CantidadStock) as "PVP",
SUM((PrecioCompra)* CantidadStock) as "PA",
SUM((PrecioCosteMedio )* CantidadStock) as "Pamp2"
 
FROM (
SELECT tgMarca.Descrip as "marque",
       tgEmpresa.Razon as "societe",
       tgAlma.Descrip as "magasin",
 
                taArticuloAlma.PrecioCosteMedio AS PCPMAlmacen,
 
                taHistorico.PrecioCosteMedio AS PrecioCosteMedio,
 
                CASE
                    WHEN COALESCE(taHistorico.PrecioVentaTarifa, 0.00) = 0.00 THEN CASE
                                                                                       WHEN COALESCE(taHistorico.PrecioVenta, 0.00) = 0.00 THEN CASE
                                                                                                                                                    WHEN taArticuloPrecio.Precioventa > 0 THEN taArticuloPrecio.Precioventa
                                                                                                                                                    ELSE taArticulo.PrecioVenta
                                                                                                                                                END
                                                                                       ELSE taHistorico.PrecioVenta
                                                                                   END
                    ELSE taHistorico.PrecioVentaTarifa
                END AS taHistorico_PrecioVenta,
                CASE
                    WHEN COALESCE(taHistorico.PrecioCompraTarifa, 0.00) = 0.00 THEN CASE
                                                                                        WHEN COALESCE(taHistorico.PrecioCompra, 0.00) = 0.00 THEN COALESCE(
                                                                                                                                                             (SELECT PrecioCoste
                                                                                                                                                              FROM taArticuloProv
                                                                                                                                                              WHERE taArticuloProv.NumInterno = taArticulo.NumInterno
                                                                                                                                                                AND taArticuloProv.Proveedor = taArticulo.Proveedor),0.00)
                                                                                        ELSE taHistorico.PrecioCompra
                                                                                    END
                    ELSE taHistorico.PrecioCompraTarifa
                END AS PrecioCompra,
                taHistorico.CategoriaStock AS CategoriaStock,
                taHistorico.StockFinal AS CantidadStock,
                CASE
                    WHEN taHistorico.StockFinal <= 0 THEN 0.00
                    ELSE COALESCE(taHistorico.StockFinal, 0.00)
                END AS CdadStock,
 
                CASE
                    WHEN dbo.fn_ICarDMS_dateDiff ('MONTH2',(COALESCE (dbo.fn_ICarDMS_GetLastHistMovAlm ('001', taArticuloAlma.Almacen, taArticulo.NumInterno, '27-04-2020 00:00:00.000', 1), taArticuloalma.FechaAlta)) ,'27-04-2020 00:00:00.000') < 6 THEN 1
                    WHEN dbo.fn_ICarDMS_dateDiff ('MONTH2',(COALESCE (dbo.fn_ICarDMS_GetLastHistMovAlm ('001', taArticuloAlma.Almacen, taArticulo.NumInterno, '27-04-2020 00:00:00.000', 1), taArticuloalma.FechaAlta)) ,'27-04-2020 00:00:00.000') < 12 THEN 2
                    WHEN dbo.fn_ICarDMS_dateDiff ('MONTH2',(COALESCE (dbo.fn_ICarDMS_GetLastHistMovAlm ('001', taArticuloAlma.Almacen, taArticulo.NumInterno, '27-04-2020 00:00:00.000', 1), taArticuloalma.FechaAlta)) ,'27-04-2020 00:00:00.000') < 24 THEN 3
                    ELSE 4
                END AS Rango,
                CASE
                    WHEN dbo.fn_ICarDMS_dateDiff ('MONTH2',(COALESCE (dbo.fn_ICarDMS_GetLastHistMovAlm ('001', taArticuloAlma.Almacen, taArticulo.NumInterno, '27-04-2020 00:00:00.000', 1), taArticuloalma.FechaAlta)) ,'27-04-2020 00:00:00.000') < 12 THEN 0
                    ELSE 1
                END AS Muerto
 
 
FROM taHistorico
LEFT OUTER JOIN tgGrupoCont ON taHistorico.GrupoCont = tgGrupoCont.GrupoCont
LEFT OUTER JOIN taFamProveedor ON taHistorico.Marca = taFamProveedor.Marca
AND taHistorico.FamiliaProv = taFamProveedor.Codigo
LEFT OUTER JOIN taFamInterna ON taHistorico.Marca = taFamInterna.Marca
AND taHistorico.FamiliaInt = taFamInterna.Codigo
LEFT OUTER JOIN taCategoriaStock ON taHistorico.Emp = taCategoriaStock.Emp
AND taHistorico.Marca = taCategoriaStock.Marca
AND taHistorico.CategoriaStock = taCategoriaStock.Codigo
LEFT OUTER JOIN taCategoriaPieza ON taHistorico.Marca = taCategoriaPieza.Marca
AND taHistorico.CodigoCategoria = taCategoriaPieza.Codigo,
    taArticuloAlma
LEFT OUTER JOIN taArticuloPrecio ON (taArticuloAlma.NumInterno = taArticuloPrecio.NumInterno
                                     AND taArticuloAlma.Emp = taArticuloPrecio.Emp
                                     AND taArticuloAlma.Almacen = taArticuloPrecio.Almacen), taArticulo
LEFT OUTER JOIN taDctoCompra ON taArticulo.Marca = taDctoCompra.Marca
AND taArticulo.Proveedor = taDctoCompra.Proveedor
AND taArticulo.GrupoDctoCompra = taDctoCompra.GrupDctoComp
AND taDctoCompra.TipoPedido IN
  (SELECT tipopedido
   FROM tgalma
   WHERE emp = '001'
     AND almacen = '10' ), tgEmpresa,
                           tgAlma,
                           tgMarca
WHERE taArticuloAlma.Emp = '001'
  AND taArticuloAlma.Almacen = '10'
  AND taHistorico.Emp = taArticuloAlma.Emp
  AND taHistorico.Almacen = taArticuloAlma.Almacen
  AND taHistorico.NumIntArticulo = taArticuloAlma.NumInterno
  AND taHistorico.NumIntHistorico = dbo.fn_ICarDMS_LastHistArt ('001', '10', taArticuloAlma.NumInterno, '27-04-2020 00:00:00.000')
  AND taHistorico.StockFinal <> 0
  AND taArticuloAlma.NumInterno = taArticulo.NumInterno
  AND taArticulo.EsConsigna = 0
  AND taArticulo.NoAlmacenada = 0
  AND taArticuloAlma.Emp = tgEmpresa.Emp
  AND taArticuloAlma.Emp = tgAlma.Emp
  AND taArticuloAlma.Almacen = tgAlma.Almacen
  AND taArticulo.Marca = tgMarca.Marca) as requete1
GROUP BY societe,
magasin, 
marque,
Muerto,Rango
ORDER BY societe,
magasin, 
marque,
Muerto,Rango
Si j’exécute la requête directement dans sql management studio, je n'ai pas erreur la requête prend 1.30 min

Pourtant erreur est bien une reponse du serveur sql,

est-ce qu'il y a probleme dans la requete?

Merci d'avance pour vos conseils et retour expérience


guigui69