ajouter dans le eventlog

Version imprimable

09/05/2008, 14h40
amarige

ajouter dans le eventlog

Bonjour à tous,

Je veux créer une procédure stocké qui ajoute de nouveau event à l'eventlog de sql server ( par example au cas de changement du nom d'une bdd ou meme suppression).
donc avez vous une idée comment le faire

merci
12/05/2008, 10h01
SQLpro

Utilisez la procédure stockée étendue :

xp_logevent (error_number, 'message') [, 'severity']

AVEC...
error_number : Numéro d'erreur défini par l'utilisateur supérieur à 50 000, la valeur maximale étant 1 073 741 823 (230 - 1).

'message' : Chaîne de caractères de longueur inférieure à 8000 caractères.

'severity' : Une des trois chaînes de caractères : INFORMATIONAL, WARNING ou ERROR. severity est facultatif, avec INFORMATIONAL comme valeur par défaut.

A +

Bonjour,
oui c'est ce que j'ai essayé de faire mais j'arrive pas à avoir l'event dans l'eventwin.exe

code:

Code:

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
#USE ClassNorthwind
#GO
 
#/* Specify custom message for the event log */
#EXEC sp_addmessage @msgnum = 50018,
#                   @severity = 16,
#                   @msgtext = N'Supplier %d was inserted by %s',
#                   @lang = 'us_english',
#                   @with_log = 'true',
#                   @replace = 'replace'
#GO
#
#/* If the object already exists in the database, drop it. */
#IF OBJECT_ID('SupplierProductInsert') IS NOT NULL
#  DROP PROCEDURE SupplierProductInsert
#GO
#
#/* Create Procedure to INSERT new Record in SUPPLIER Table */
#CREATE PROCEDURE SupplierProductInsert
#        @CompanyName nvarchar (40) = NULL,
#        @ContactName nvarchar (40) = NULL,
#        @ContactTitle nvarchar (40)= NULL,
#        @Address nvarchar (60) = NULL,
#        @City nvarchar (15) = NULL,
#        @Region nvarchar (40) = NULL,
#        @PostalCode nvarchar (10) = NULL,
#        @Country nvarchar (15) = NULL,
#        @Phone nvarchar (24) = NULL,
#        @Fax nvarchar (24) = NULL,
#        @HomePage ntext = NULL,
#        @ProductName nvarchar (40) = NULL,
#        @CategoryID int = NULL,
#        @QuantityPerUnit nvarchar (20) = NULL,
#        @UnitPrice money = NULL,
#        @UnitsInStock smallint = NULL,
#        @UnitsOnOrder smallint = NULL,
#        @ReorderLevel smallint = NULL,
#        @Discontinued bit  = NULL
#AS
#    IF  @CompanyName     IS NULL OR
#        @ContactName     IS NULL OR
#        @Address         IS NULL OR
#        @City            IS NULL OR
#        @Region          IS NULL OR
#        @PostalCode      IS NULL OR
#        @Country         IS NULL OR
#        @Phone           IS NULL OR
#        @ProductName     IS NULL OR
#        @CategoryID      IS NULL OR
#        @QuantityPerUnit IS NULL OR
#        @Discontinued    IS NULL
#    BEGIN
#      PRINT 'You must provide Company Name, Contact Name, Address, City'
#      PRINT 'Region, Postal Code, Country, Phone, Product Name, and Discontinued'
#      PRINT '(Contact Title, Fax, Home Page, Unit Price, Units in Stock
#          Units on Order and Reorder Level can be null.)'
#      RETURN
#    END
#
#/* Store the login identification name for use in custom message */
#DECLARE @UserName nvarchar (60)
#SELECT @UserName = suser_sname()
#
#/* Start the INSERT */
#BEGIN TRANSACTION
#    INSERT Suppliers (
#        CompanyName,
#        ContactName,
#        Address,
#        City,
#        Region,
#        PostalCode,
#        Country,
#        Phone)
#    VALUES (
#        @CompanyName,
#        @ContactName,
#        @Address,
#        @City,
#        @Region,
#        @PostalCode,
#        @Country,
#        @Phone)
#        IF @@error <> 0
#            BEGIN
#                ROLLBACK TRAN
#                RETURN
##            END
#
#    /* Get just inserted @@identity value */
#    DECLARE @InsertSupplierID int
#    SELECT @InsertSupplierID=@@identity
#
#    /* Insert Record in PRODUCTS for this SupplierID */
#    INSERT Products (
#        ProductName,
#        SupplierID,
##        CategoryID,
#        QuantityPerUnit,
#        Discontinued)
#    VALUES (
#        @ProductName,
#        @InsertSupplierID,
#        @CategoryID,
#        @QuantityPerUnit,
#        @Discontinued)
#        IF @@error <> 0
#            BEGIN
#                ROLLBACK TRAN
#                RETURN
#            END
#
#  /* Send custom message to event log */
#  RAISERROR (50018, 16, 1, @InsertSupplierID, @UserName) With log
#
#COMMIT TRANSACTION
#GO
#
#/* Execute the Stored Procedure */
#EXEC SupplierProductInsert
#        @CompanyName = 'Akadia',
#        @ContactName = 'Martin Zahn',
#        @Address = 'Arvenweg 4',
#        @City = 'Thun',
#        @Region = 'Bern',
#        @PostalCode = '3604',
#        @Country = 'CH',
#        @Phone = '0333358620',
#        @ProductName = 'Transtec',
#        @CategoryID = '1',
#        @QuantityPerUnit = '1',
#        @UnitPrice = 1,
#        @Discontinued = 0
#
#

Citation:

master..xp_logevent 50018, 'Test Event', 'ERROR'

à l'evenwin il m'affiche seulement les events de la sysmessages à partir de l'erreur num 17050 sans ajouter cette dérnière

12/05/2008, 21h10
SQLpro

Merci d'indenter votre code et d'utiliser la balise CODE.

A +

Bonjour,
oui c'est ce que j'ai essayé de faire mais j'arrive pas à avoir l'event dans l'eventwin.exe

Code:

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
code:
USE ClassNorthwind
GO
/* Specify custom message for the event log */
EXEC sp_addmessage @msgnum = 50018,
                   @severity = 16,
                   @msgtext = N'Supplier %d was inserted by %s',
                   @lang = 'us_english',
                   @with_log = 'true',
                   @replace = 'replace'
GO
 
/* If the object already exists in the database, drop it. */
IF OBJECT_ID('SupplierProductInsert') IS NOT NULL
  DROP PROCEDURE SupplierProductInsert
GO
 
/* Create Procedure to INSERT new Record in SUPPLIER Table */
CREATE PROCEDURE SupplierProductInsert
        @CompanyName nvarchar (40) = NULL,
       @ContactName nvarchar (40) = NULL,
        @ContactTitle nvarchar (40)= NULL,
        @Address nvarchar (60) = NULL,
        @City nvarchar (15) = NULL,
        @Region nvarchar (40) = NULL,
        @PostalCode nvarchar (10) = NULL,
        @Country nvarchar (15) = NULL,
        @Phone nvarchar (24) = NULL,
        @Fax nvarchar (24) = NULL,
        @HomePage ntext = NULL,
        @ProductName nvarchar (40) = NULL,
        @CategoryID int = NULL,
        @QuantityPerUnit nvarchar (20) = NULL,
        @UnitPrice money = NULL,
        @UnitsInStock smallint = NULL,
        @UnitsOnOrder smallint = NULL,
        @ReorderLevel smallint = NULL,
        @Discontinued bit  = NULL
AS
    IF  @CompanyName     IS NULL OR
        @ContactName     IS NULL OR
        @Address         IS NULL OR
        @City            IS NULL OR
        @Region          IS NULL OR
        @PostalCode      IS NULL OR
        @Country         IS NULL OR
        @Phone           IS NULL OR
        @ProductName     IS NULL OR
        @CategoryID      IS NULL OR
        @QuantityPerUnit IS NULL OR
        @Discontinued    IS NULL
    BEGIN
      PRINT 'You must provide Company Name, Contact Name, Address, City'
      PRINT 'Region, Postal Code, Country, Phone, Product Name, and Discontinued'
      PRINT '(Contact Title, Fax, Home Page, Unit Price, Units in Stock
          Units on Order and Reorder Level can be null.)'
      RETURN
    END
 
/* Store the login identification name for use in custom message */
DECLARE @UserName nvarchar (60)
SELECT @UserName = suser_sname()
 
/* Start the INSERT */
BEGIN TRANSACTION
    INSERT Suppliers (
        CompanyName,
        ContactName,
        Address,
        City,
        Region,
        PostalCode,
        Country,
        Phone)
    VALUES (
        @CompanyName,
        @ContactName,
        @Address,
        @City,
        @Region,
        @PostalCode,
        @Country,
        @Phone)
        IF @@error <> 0
            BEGIN
                ROLLBACK TRAN
                RETURN
            END
 
    /* Get just inserted @@identity value */
    DECLARE @InsertSupplierID int
    SELECT @InsertSupplierID=@@identity
 
    /* Insert Record in PRODUCTS for this SupplierID */
    INSERT Products (
        ProductName,
        SupplierID,
        CategoryID,
        QuantityPerUnit,
        Discontinued)
    VALUES (
        @ProductName,
        @InsertSupplierID,
        @CategoryID,
        @QuantityPerUnit,
        @Discontinued)
        IF @@error <> 0
            BEGIN
                ROLLBACK TRAN
                RETURN
            END
 
master..EXEC xp_logevent 50018, 'Test Event', 'ERROR'
 
/* Send custom message to event log */
  RAISERROR (50018, 16, 1, @InsertSupplierID, @UserName) With log
 
COMMIT TRANSACTION
GO
 
/* Execute the Stored Procedure */
EXEC SupplierProductInsert
        @CompanyName = 'Akadia',
      @ContactName = 'Martin Zahn',
        @Address = 'Arvenweg 4',
        @City = 'Thun',
        @Region = 'Bern',
        @PostalCode = '3604',
        @Country = 'CH',
        @Phone = '0333358620',
      @ProductName = 'Transtec',
        @CategoryID = '1',
        @QuantityPerUnit = '1',
        @UnitPrice = 1,
        @Discontinued = 0

à l'evenwin il m'affiche seulement les events de la sysmessages à partir de l'erreur num 17050 sans ajouter cette dérnière