SQL Server extended stored procedure to send UDP messages

• Download source files - 13.1 Kb

Introduction

I wanted to be able to announce SQL Server table updates so I modified the Microsoft sample extended stored procedure xp_hello. The source for this article will produce a DLL that can be placed in the \Program Files\Microsoft SQL Server\MSSQL\Binn directory and called from a regular stored procedure to send a UDP message. I chose UDP so there would be minimal execution time added (no TCP connection issues).

Be aware that this extended stored procedure is executed in SQL Server’s space so any errors/exceptions could crash SQL Server. This happened to me during development when I mistakenly called WSACleanup after sending an announcement, causing SQL Server to lose its network connections. In short, be very careful what you do within the DLL as it can affect SQL Server.

A sample SQL Server stored procedure that calls SQLAnnounceProc is:

CREATE PROCEDURE getTestTable
AS
BEGIN SELECT * FROM TEST_TABLE
declare @status int
declare @ret varchar(513)
declare @temp as varchar(128)
SET @temp = 'got ' + CAST( @@ROWCOUNT as varchar(16) ) + ' rows'
declare @ip varchar(32)
SET @ip = '127.0.0.1'
declare @port varchar(16)
SET @port = '15555'
exec @status = master.dbo.SQLAnnounceProc @ret OUTPUT, @ip, @port, @temp
END
GO

Of course, you will need to create a table named TEST_TABLE for testing purposes and call it from the Query Analyser using:

use TEST {call getTestTable}

Notes:

• You can view the UDP messages by running the Perl script announceListen.pl.
• Add directory \Program Files\Microsoft SQL Server\80\Tools\DevTools\Include to VC++ include dirs.
• Add directory \Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib to VC++ lib dirs.

This is a new version with the following changes:

1. Bug fix. There was a 255 char limit on the UDP message text because I used srv_paramdata(), which has a 255 char limit. I changed the code in SQLAnnounceProc.c to use srv_paraminfo() instead. The rest of the params still have the 255 char limit.
2. I converted the project to VS 2003.
3. Test before use!