Service of events in the SQL-server

Immersing in a problematics

Quite often enough developers of client-server applications have necessity to organize the certain mechanism allowing on event on the sql-server to notify this or that client. Even more often it is rozovo-dreamboat of the customer that the developer realized such mechanism. For example, at excess of limits of shipment to any consumer, the managers working with this consumer should be immediately notified. Some customers of systems demand (and all customers without exception) that at change of any data, at other users of system this information was automatically updated, and immediately dream of it. Here the expediency of such requirement (it has many bases for criticism) will not be discussed, only ways of the decision here will be discussed. microsoft the sql-server has regular means for the organizations of notices - alerts, but this means has rather limited application which is by and large not giving an opportunity to create on his{its} basis is guaranteed the working mechanism. And that is why: Communication{Connection} with the client program can be carried out by a parcel{sending} e-mail or emulation of a parcel{sending} " net send ". Both that, and another is inconvenient for reception of the notice.

Means e-mail is inconvenient for the reasons:

a) There is no guarantee of delivery, mail can be lost.

b) Mail can "get stuck" on intermediate sites.

c) Presence of the report tcp/ip is required necessarily

d) Presence of the smtp-server and adjustment of a post structure is required.

e) Special adjustment of the sql-server that he could send letters is required.

f) Presence is required from each client expecting for event, a mail box.

g) In the client program the post client is required to organize.

The parcel{sending} by " net send " is inconvenient for the following reasons:

a) There is no guarantee of delivery as it is organized through means mailslot, not having such guarantee.

b) Presence of a correct name resolution netbios in a network is required.

c) Presence on the client " the Client for networks Microsoft " is required.

d) It is involved standard mailslot, it can have crossing with other programs.

And as a whole means alerts inconveniently necessity of registration of each client as the operator and corresponding adjustment. I.e. to the elementary cases alerts to apply it is possible. But for the majority of cases it is inapplicable.

Known realizations and concepts

The wide public some variants of realization of the mechanism of the notice are known for the server of the client. It:

1. Creation of object (extended stored procedure or activex) by means of which the sql-server notifies the client through sockets tcp/ip. Thus on the client it is organized proslushka, i.e. the client program became tcp/ip server.

Lacks of this method:

a) Binding to the report tcp/ip. In a network where it is used only ipx, netbeui or appletalk, such mechanism to not apply.

b) No asinkhronnosti. If this event is generated from the trigger, there will be problems of productivity.

2. Creation of object (extended stored procedure or activex) by means of which the sql-server notifies the client through named pipes or mailslots. Thus on the client it is organized proslushka this or that.

Lacks of this method:

a) Presence of a correct name resolution netbios in a network is required.

b) Presence on the client " the Client for networks Microsoft " is required.

c) In case of use mailslot there is no guarantee of delivery.

d) In case of use named pipes, it cannot be applied on client computers with operational system windows 95/98/me as named pipe it is possible to create only in operational system on architecture nt.

e) No asinkhronnosti. If this event is generated from the trigger, there will be problems of productivity.

3. Periodic polling the sql-server by the client (periodic reading of the special tablet eventov). It is very simple way, but, nevertheless, free from the majority of the set forth above lacks. Unfortunately, this method has the specific 2 lacks: a) reception of the notice can be detained on size tajmauta polling and b) at small tajmaute there is an essential traffic. Nevertheless, at small kol-ve sessions, this method is quite suitable and unduly bypassed by attention.