Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Alerts In C++ Builder

By Stas Vasiljev,

Sometimes we need receive alert on client side about some event on SQL server without request. For instance, data is changed and we want notify all client applications.It is possible to realize this option with extended stored procedure contained in dynamic-link library, that send broadcast packets through sockets via UDP. The creation of an extended stored procedure was made in С++ Builder 6, with use of ODS (Open Data Service) API for MS SQL Server 2000. To take into consideration that static library Opends60.lib with  this Borland IDE, which realize all services of  ODS API, has outdated version and support only MS SQL 7. You may get import library file there or create it self-dependent with  Implib utility. Also, to be noticed that UDP not ensure the message delivery, but don’t require a connection, as TCP. This is main reason why UDP was choosen.

    The simple example of using alerts is generation event from user audit table’s trigger after adding new record. The structure of “EVENT” table consist of unique record identifier, user login and message. The extended stored procedure "xp_event"  may has following input parameters:

  • <host name>
  • <port number>
  • <message text>
  • <user name>
  • <record identifier>
You may use broadcast address in host name argument. For instance, 223.1.2.255 (net-directed broadcast ) or 255.255.255.255 (limited broadcast address). Also you may use the local network computer’s name. Notice, that if your net has subnets, then router didn’t admit any broadcast packet without addition settings. The number of UDP port is optional, but you should avoid system ports that are using by OS. By default, the client listen 3338 port.

    The TSQLAlerter component has two methods: Start and Stop, which creates  new process for port listening and stop it correspondingly, i.e. the client has UDP server roles. The event OnGetMessage comes in the moment of notification delivery, and the pointer to TLabel visual component allows display a getting message at the form. The structure are using for sending broadcast messages has the following view:

typedef struct TDATASEND // The structure for sending alerts
{
char message[1024];
char login[1024];
long id;
} TDATASEND;

    The thread gets an alert and synchronize properties Message, RecordId and Login of TSQLAlerter  class object in AddMessage() method. The example of registration procedure and realization of delivery notification you may look in script.

/*TSQLAlerter.sql*/

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

USE master

-- If 'xp_event' already exist, drops an extended stored procedure.

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'xp_event' AND type = 'X')
EXEC sp_dropextendedproc 'xp_event'
GO

/* sp_addextendedproc adds entries to the sysobjects table, registering the name of the new extended
stored procedure with SQL Server. It also adds an entry in the syscomments table.
First argument is the name of the function to call within the dynamic-link library procedure,
second argument is the name of the DLL containing the function.*/

EXEC sp_addextendedproc xp_event, 'dll_event.dll'

-- If table 'EVENTS' already exist, drops table.

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'EVENTS' AND type = 'U')
DROP TABLE EVENTS
GO


/*Create 'EVENTS' table*/

CREATE TABLE dbo.EVENTS (
[ID] INT IDENTITY(1,1) NOT NULL,
[LOGIN] CHAR(255) NULL,
[MESSAGE] TEXT NULL
)
GO

-- If trigger 'events_trg' already exist, drops trigger.

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'events_trg' AND type = 'TR')
DROP TRIGGER events_trg
GO

/*Create 'events_trg' trigger*/

CREATE TRIGGER events_trg
ON EVENTS
FOR INSERT
AS
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
-- To execute an extended stored procedure from a database
-- other than master, qualify the name of the extended stored procedure with master.
-- 'xp_event' parameters: <host name or broadcast address>, <port number>,
-- <message text>, <user name>, <record identifier>.

EXEC master..xp_event '255.255.255.255',3338,@message,@login,@id

We used TSQLAlerter component in own bug-tracking system, for notifications programmers and quality assurance engineers when bug's status was close or changed.

Sheduler


Total article views: 4748 | Views in the last 30 days: 1
 
Related Articles
FORUM

Extended Procedure

Extended Procedure

FORUM

Extended stored procedure

Extended stored procedure

FORUM

extended stored procedure

extended stored procedure

BLOG

Basics QA on Extended Event

Basics QA on Extended Event     I am learning Extended Event so this is my first blog on extend...

BLOG

Extended Events Data

I’ve been working quite a bit over the last week or so with extended events in Denali. The sheer mag...

Tags
c++    
miscellaneous    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones