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. 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.
|
