Click here to Skip to main content
12,632,090 members (26,336 online)
Click here to Skip to main content
Add your own
alternative version

Stats

63.2K views
822 downloads
27 bookmarked
Posted

SQL Server extended stored procedure to send UDP messages

, 25 Apr 2005
Rate this:
Please Sign up or sign in to vote.
SQL Server extended stored procedure to send UDP messages.

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!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Rod VanAmburgh
Web Developer
United States United States
I enjoy working with C#, Java, Perl and C++

You may also be interested in...

Comments and Discussions

 
General64-bit version Pin
Mark Nadelson17-May-07 11:57
memberMark Nadelson17-May-07 11:57 
QuestionBug with some buffer ? Pin
Pablo7531-Aug-06 6:21
memberPablo7531-Aug-06 6:21 
AnswerRe: Bug with some buffer ? Pin
Pablo7531-Aug-06 9:06
memberPablo7531-Aug-06 9:06 
GeneralRe: Bug with some buffer ? Pin
Rod VanAmburgh31-Aug-06 18:34
memberRod VanAmburgh31-Aug-06 18:34 
AnswerRe: Bug with some buffer ? Pin
Rod VanAmburgh31-Aug-06 18:29
memberRod VanAmburgh31-Aug-06 18:29 
GeneralMS-SQL server Self join Pin
Amit Malik25-Dec-05 20:08
memberAmit Malik25-Dec-05 20:08 
QuestionWorks great, 256 character limit? Pin
bertszoghy22-Apr-05 14:01
memberbertszoghy22-Apr-05 14:01 
AnswerRe: Works great, 256 character limit? Pin
Rod VanAmburgh22-Apr-05 17:54
memberRod VanAmburgh22-Apr-05 17:54 
GeneralRe: Works great, 256 character limit? Pin
bertszoghy23-Apr-05 8:03
memberbertszoghy23-Apr-05 8:03 
GeneralRe: Works great, 256 character limit? Pin
bertszoghy25-Apr-05 3:49
memberbertszoghy25-Apr-05 3:49 
GeneralRe: Works great, 256 character limit? Pin
Rod VanAmburgh25-Apr-05 16:52
memberRod VanAmburgh25-Apr-05 16:52 
GeneralRe: Works great, 256 character limit? Pin
Rod VanAmburgh25-Apr-05 18:31
memberRod VanAmburgh25-Apr-05 18:31 
GeneralRe: Works great, 256 character limit? Pin
bertszoghy26-Apr-05 6:35
memberbertszoghy26-Apr-05 6:35 
GeneralRe: Works great, 256 character limit? Pin
Rod VanAmburgh26-Apr-05 15:37
memberRod VanAmburgh26-Apr-05 15:37 
GeneralRe: Works great, 256 character limit? Pin
bertszoghy27-Apr-05 7:34
memberbertszoghy27-Apr-05 7:34 
GeneralCool Pin
JorgeCordero3-Mar-05 18:36
memberJorgeCordero3-Mar-05 18:36 
GeneralElaborate Pin
perlmunger7-Dec-04 6:07
memberperlmunger7-Dec-04 6:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.161208.2 | Last Updated 25 Apr 2005
Article Copyright 2004 by Rod VanAmburgh
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid