DevPinoy.org
A Filipino Developers Community

>>> First two to make 3 wins! <<<

SP that sends notifications to connected users in an SQL Server

Wouldn't it be nice if you have a mechanism to notify users that are connected to your SQL Server? Here's a stored procedure that an help you do that task.

CREATE PROC spSendNetworkNotification
@Message VARCHAR(200)
AS
BEGIN
SET NOCOUNT ON

DECLARE @ShellCommand VARCHAR(350)
DECLARE @MessageSource SYSNAME

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

WHILE @MessageSource is not null
BEGIN
    SET @ShellCommand='EXEC master.dbo.xp_cmdshell "NET SEND ' + LTRIM(RTRIM(@MessageSource)) + ' ' + LTRIM(RTRIM(@Message)) + ' "'
EXEC (@ShellCommand)
    SELECT @MessageSource= min(RTRIM(@MessageSource))
    FROM master.dbo.sysprocesses (NOLOCK)
    WHERE @MessageSource <> '' and @MessageSource > @MessageSource
END

SET NOCOUNT OFF
END


Posted 06-29-2005 11:10 PM by keithrull

Copyright DevPinoy 2005-2008