Know Dot Net Article

Send a Message to Everyone Logged into a SQL Server

by William Ryan

If you've done any heavy lifting in Database programming, you quickly notice that languages like T-SQL and PL/SQL can do quite a bit for you.  While SQL certainly is powerful, it doesn't have many constructs that are a given in more modern programming languages.  Additionally, cursors can do a lot for you, but they aren't fast and ADO.NET doesn't play really well with them.

Anyway, in this snippet, I'm going to use a While Loop in T-SQL, dynamically create some SQL commands, and fire them off via xp_cmdshell.  Think about it for  a second, if you did this some other way, it would take a good amount of work to fire off Net Send Messages to everyone logged into your database (and would be next to impossible with a non Client/Server database like Access).  Well, this is pretty straightforward, you just query SYSPROCESSES, construct a SQL Statement, execute it, then requery SYSPROCESSES.  And the rest is history...

Check out the snippet below:

CREATE  PROC usp_notify_users
    @notification VARCHAR(100)

AS
BEGIN
    SET NOCOUNT ON

    DECLARE @Command VARCHAR(300)
    DECLARE @hostname SYSNAME

    SELECT @hostname= MIN(RTRIM(hostname))
    FROM
      master.dbo.sysprocesses (NOLOCK)
    WHERE hostname <> ''

WHILE @hostname is not null
    BEGIN
    SET @Command='exec master.dbo.xp_cmdshell "net send ' + RTRIM(@hostname) + ' ' + RTRIM(@notification) + ' "'
        EXEC (@Command)
      SELECT @hostname= MIN(RTRIM(hostname))
    FROM
         master.dbo.sysprocesses (NOLOCK)
    WHERE
         hostname <> ''
       and hostname > @hostname
    END

SET NOCOUNT OFF

END

This proc takes in a Param @Notification which is what you want to broadcast to everyone.  We declare another variable, @Command which is going to be used so we can dynamically build a T-SQL Statement and fire a command via xp_cmdshell..  Then we reset the values each pass through a while loop and NET SEND a message each pass through.