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
| DECLARE @errnum int,
@errmess nvarchar(4000),
@ret int,
@NbBaseTheo int,
@NbBaseTrt int,
@NbBaseErr int,
@IamThePrimary Int,
@sqlorder nvarchar(max),
@DBNAME sysname;
DECLARE CUR_DB CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY --> important pour pas bloquer !!!
FOR
select name
from sys.databases
where name not in ('master', 'msdb', 'tempdb', 'model', 'ANNUAIRE_ZEPHYR')
and name not like '%OFFLINE%' and name not like 'META%' and name not like 'LOADGEN%'
order by name;
BEGIN -- corps
SET @IamThePrimary = 0
SELECT @IamThePrimary = 1
FROM [master].[sys].[dm_hadr_availability_replica_states] as DMHA
inner join [master].[sys].[availability_replicas] as SYAR
ON DMHA.replica_id = SYAR.replica_id
WHERE DMHA.[role] = 1
AND SYAR.[replica_server_name] = @@SERVERNAME;
IF @IamThePrimary = 1
BEGIN -- condition
SELECT @NbBaseTheo=count(name)
FROM sys.databases
WHERE name not in ('master', 'msdb', 'tempdb', 'model', 'ANNUAIRE_ZEPHYR')
and name not like '%OFFLINE%' and name not like 'META%' and name not like 'LOADGEN%';
SELECT @NbBaseTrt = 0, @NbBaseErr = 0;
PRINT 'Debut traitement ' + cast(@NbBaseTheo as varchar) +' bases a traiter';--###
OPEN CUR_DB;
FETCH CUR_DB INTO @DBNAME;
WHILE @@FETCH_STATUS = 0
BEGIN -- boucle while
BEGIN TRY
SET @sqlorder = N'IF EXISTS (SELECT * FROM ' + @DBNAME + N'.sys.objects WHERE object_id = OBJECT_ID(N''['
+ @DBNAME + N'].[data].[PS_Refresh_Ofr_Cde_EnCours]'') AND type in (N''P'', N''PC'')) BEGIN exec ['
+ @DBNAME + N'].[data].[PS_Refresh_Ofr_Cde_EnCours] END'
SET @ret = 0;
EXEC @ret = sp_executesql @sqlorder;
IF @ret =0
BEGIN -- if
PRINT @sqlorder+' ==> OK' --###
SET @NbBaseTrt = @NbBaseTrt +1
FETCH NEXT FROM CUR_DB INTO @DBNAME
END -- if
IF @ret <> 0
BEGIN -- if
PRINT @sqlorder+' ==> KO' --###
SELECT @errnum = ERROR_NUMBER(), @errmess = ERROR_MESSAGE();
PRINT @errmess; --###
THROW @errnum, @errmess, 1;
END -- if
END TRY
BEGIN CATCH
PRINT @sqlorder + ' KO';
SELECT @NbBaseErr = @NbBaseErr +1,
@errnum = ERROR_NUMBER(),
@errmess = ERROR_MESSAGE() + N' Base : '+ @DBNAME;
RAISERROR(60001,17,1, @errnum, @errmess) ;
END CATCH
FETCH NEXT FROM CUR_DB INTO @DBNAME
END; -- boucle while
CLOSE CUR_DB;
DEALLOCATE CUR_DB;
PRINT 'Etat du Fetch_Status = ' + cast(@@FETCH_STATUS as nvarchar); --###
PRINT 'Fin traitement ' + cast(@NbBaseTrt as varchar) +' bases traitées, ' + cast(@NbBaseErr as varchar) + ' bases en erreur'; --###
SET @errmess = 'Le nombre de base a traiter etait de ' + cast(@NbBaseTheo as varchar) +' mais seul '+ cast(@NbBaseTrt+@NbBaseErr as varchar)+' l''ont été traitées'
IF @NbBaseTrt+@NbBaseErr <> @NbBaseTheo
RAISERROR(60001,17,1, 60001, @errmess) ;
END; -- condition
END; -- corps |
@@ FETCH_STATUS = -2
Répondre avec citation
Partager