Send a Message to Everyone Logged into a SQL Server

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.

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.