As a SQL Server developer, you may come across situations where you need to notify client applications about events happening on the server without any explicit request. For example, when data is changed, you might want to notify all connected clients. In this article, we will explore a method to achieve this using extended stored procedures and UDP sockets.

Extended stored procedures are dynamic-link libraries (DLLs) that can be used to extend the functionality of SQL Server. In this case, we will create an extended stored procedure using C++ Builder 6 and the Open Data Service (ODS) API for MS SQL Server 2000. Please note that the static library Opends60.lib provided with this Borland IDE only supports MS SQL 7. You can obtain the import library file or create a self-dependent library using the Implib utility.

UDP (User Datagram Protocol) is a connectionless protocol that allows for the transmission of data packets without establishing a connection. Unlike TCP (Transmission Control Protocol), UDP does not guarantee message delivery. However, it is suitable for our purpose as it does not require a connection. We will use UDP to send broadcast packets to notify client applications.

Let’s consider a simple example of using alerts. Suppose we have a user audit table with a trigger that generates an event after adding a new record. The structure of the “EVENT” table consists of a unique record identifier, user login, and message. We can create an extended stored procedure called “xp_event” with the following input parameters: , , , , and . The host name argument can be a broadcast address (e.g., 223.1.2.255 or 255.255.255.255) or the name of a local network computer. If your network has subnets, additional settings may be required for the router to allow broadcast packets.

The UDP port number is optional, but it is recommended to avoid system ports that are already in use by the operating system. By default, the client application listens on port 3338. To handle the reception of alerts on the client side, we can use a component called TSQLAlerter. It provides two methods: Start and Stop, which create a new process for port listening and stop it, respectively. The OnGetMessage event is triggered when a notification is received, and we can use a visual component like TLabel to display the message on the form.

Here is an example of the structure used for sending broadcast messages:

typedef struct TDATASEND
{
    char message[1024];
    char login[1024];
    long id;
} TDATASEND;

In our bug-tracking system, we utilized the TSQLAlerter component to notify programmers and quality assurance engineers when the status of a bug was closed or changed.

To implement this solution, we need to register the extended stored procedure and create the necessary database objects. Here is an example script:

/*TSQLAlerter.sql*/

-- Only add an extended stored procedure to the master database.
USE master

-- If 'xp_event' already exists, drop the extended stored procedure.
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'xp_event' AND type = 'X')
    EXEC sp_dropextendedproc 'xp_event'

-- Add the extended stored procedure to the master database.
EXEC sp_addextendedproc xp_event, 'dll_event.dll'

-- If 'EVENTS' table already exists, drop the table.
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'EVENTS' AND type = 'U')
    DROP TABLE EVENTS

-- Create the 'EVENTS' table.
CREATE TABLE dbo.EVENTS
(
    [ID] INT IDENTITY (1,1) NOT NULL,
    [LOGIN] CHAR (255) NULL,
    [MESSAGE] TEXT NULL
)

-- If 'events_trg' trigger already exists, drop the trigger.
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'events_trg' AND type = 'TR')
    DROP TRIGGER events_trg

-- Create the 'events_trg' trigger.
CREATE TRIGGER events_trg ON EVENTS FOR INSERT AS
BEGIN
    DECLARE @login VARCHAR(20)
    DECLARE @message VARCHAR(2000)
    DECLARE @id INT

    SELECT @login = RTRIM(LOGIN), @id = ID FROM inserted
    SELECT @message = EVENTS.MESSAGE FROM inserted JOIN EVENTS ON EVENTS.ID = inserted.ID

    -- Execute the extended stored procedure 'xp_event' to send the notification.
    EXEC master..xp_event '255.255.255.255', 3338, @message, @login, @id
END

By following these steps, you can implement a mechanism to send alerts from SQL Server to client applications using extended stored procedures and UDP sockets. This can be useful in scenarios where real-time notifications are required for events happening on the server.