SQL Server - La FAQConsultez toutes les FAQ

1
2
3
4
use MaBase 
GO 
SELECT * FROM ::fn_dblog(null, null) 
GO

DBCC LOG('MaBase')

dbcc opentran

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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
/********************************************************************  
* exemple basique du "service broker" (base de données distribuées) *  
* Frédéric Brouard - SQLPro - http://www.sqlspot.com - 2009-02-12   *  
*                                                                   *  
* un échange de message au sein de la même base pour montrer        *  
* l'aspect transactionnel et asynchrone de la chose                 *  
********************************************************************/  
  
/*******************************************************  
-- ETAPE n°0 : création de notre base d'essais  
*******************************************************/   
USE master;  
GO  
  
IF EXISTS (SELECT *   
           FROM   sys.databases  
           WHERE  name = 'TEST_SB_1')  
   DROP DATABASE TEST_SB_1;  
GO  
  
CREATE DATABASE TEST_SB_1;  
GO  
  
USE TEST_SB_1;  
GO  
  
/*******************************************************  
-- ETAPE n°1 : mise en place des objets de travail  
*******************************************************/  
  
-- création des files d'attentes  
CREATE QUEUE Q_1;  
CREATE QUEUE Q_2;  
  
-- création des services et initiation de la conversation  
CREATE SERVICE SRV_1 ON QUEUE Q_1 ([DEFAULT]);  
CREATE SERVICE SRV_2 ON QUEUE Q_2 ([DEFAULT]);  
  
-- quelques options :  
-- AUTHORIZATION owner_name :: permet de créer le service pour un autre user SQL  
-- ( contract_name | [DEFAULT] [ ,...n ] ) :: permet de définir des contrats associés au service  
  
  
/*********************************************  
-- ETAPE n°2 : expédition  
*********************************************/  
-- envoie d'un message identifié par un GUID  
DECLARE @UID UNIQUEIDENTIFIER;  
  
BEGIN DIALOG @UID  
FROM SERVICE SRV_1 TO SERVICE 'SRV_2';  
  
SEND ON CONVERSATION @UID ('Bonjour monde');  
-- message lancé  
  
-- interrogation de la file d'attente côté reception (en fait une table d'un type particulier)  
SELECT *   
FROM   Q_2;  
  
-- ? Q_2 est vide... pourquoi ?  
  
-- constatons que Q_1 est aussi vide  
SELECT * FROM Q_1;  
  
-- ou le message est-il bloqué ?  
SELECT *   
FROM sys.transmission_queue;  
  
-- le message est là, mais ...  
SELECT transmission_status   
FROM sys.transmission_queue;  
--=> Par défaut on ne peut pas transmettre en clair ! Il faut crypter  
  
-- création d'une clef de cryptage pour la base   
CREATE MASTER KEY  
ENCRYPTION BY PASSWORD = 'P@ssw0rd!';  
  
-- envoi d'un 2eme message  
DECLARE @UID UNIQUEIDENTIFIER;  
  
BEGIN DIALOG @UID  
FROM SERVICE SRV_1 TO SERVICE 'SRV_2';  
-- quelques options :  
-- LIFETIME = <durée en sec> par défaut 2 milliards de secondes (en fait maxint, soit 68 ans)  
-- WITH ENCRYPTION = { ON | OFF } par défaut ON  
  
SEND ON CONVERSATION @UID ('Bonjour monde 2');  
-- 2eme message envoyé  
  
-- pour mettre fin à la conversation  
-- END CONVERSATION @UID  
  
-- est-il dans la file ?  
SELECT *   
FROM   Q_2;  
  
/*********************************************  
* ETAPE n°3 : du côté du destinataire  
*********************************************/  
-- voyons le message  
DECLARE @MSG VARBINARY(MAX);  
DECLARE @HDL UNIQUEIDENTIFIER;  
  
-- lecture du message  
SELECT TOP(1) @MSG = message_body, @HDL = conversation_handle  
FROM   Q_2;  
  
-- utilisation du message  
PRINT 'Le message transmis est : ' + CAST (@MSG AS VARCHAR(MAX));  
  
-- le message est toujours dans la file. Il n'a pas été "dépilé"  
SELECT *   
FROM   Q_2;  
  
/*********************************************  
=> ETAPE n°4 : acquittement  
*********************************************/  
  
-- consommons le message  
DECLARE @HDL UNIQUEIDENTIFIER;  
  
-- lecture desctructrice  
RECEIVE TOP(1) @HDL = conversation_handle  
FROM    Q_2;  
--=> Receive : nouvel ordre Transact SQL comparable au SELECT, mais : LECTURE UNIQUE (DESTRUCTRICE) !  
-- en fait dépile le message de la pile consititué par la table Q_2 ou le place dans un statut "lu",  
-- en fonction des paramètres de rétention définit lors de la création de la queue  
  
-- le message n'est plus dans la file. Il a été "consommé"  
SELECT *   
FROM   Q_2;  
  
-- envoi de l'acquittement  
SEND ON CONVERSATION @HDL ('OK : Bien reçu !');  
  
-- arrêt de la conversation côté destinataire  
END CONVERSATION @HDL;  
  
-- Combien de messages dans Q1 ?  
SELECT *   
FROM   Q_1;  
  
--=> le message envoyé en retour et le message indiquant la fin du dialogue :  
--   message_type_name "EndDialog" (XML schéma du Service Broker MS)  
  
/*********************************************  
* ETAPE n°5 : réception de l'acquittement  
*********************************************/  
  
-- lecture du message d'acquittement  
DECLARE @MSG VARBINARY(max);  
  
DECLARE @HDL UNIQUEIDENTIFIER;  
  
RECEIVE TOP(1) @MSG = message_body, @HDL = conversation_handle  
FROM Q_1;  
-- le message est reçut et son "enveloppe" détruite  
  
-- affichage du message d'acuittement côté expéditeur  
PRINT 'Le message est : ' + CAST (@MSG AS VARCHAR (max));  
  
-- fin de la conversation côté expéditeur  
END CONVERSATION @HDL;  
  
  
/*********************************************  
* Le service broker est-il activé ?  
*********************************************/  
  
--voir l'état des bases pour   
SELECT name, service_broker_guid, is_broker_enabled  
FROM   sys.databases;  
  
-- modification du service broker pour la base   
ALTER DATABASE TEST_SB_1  
SET disable_broker;  
  
-- pour réactiver :  
ALTER DATABASE TEST_SB_1  
SET   enable_broker;