Binaryworld - send NET SEND messages to all the connected SQL Server users in an NT ... [ SQL Server -> Stored Procedure ]

This stored procedure accepts a message as an input parameter and sends that message to all the currently logged in SQL Server users using NET SEND.

Click here to copy the following block

CREATE PROC notify_users
(
@notification VARCHAR(100) = 'SQL Server shutting down'
)
AS
BEGIN

/*******************************************************************************************************
Purpose: To send NET SEND messages to all the connected SQL Server users in an NT Local Arean Network
Input parameters: Message to be sent
Tested on: SQL Server Version 7.0, SQL Server 2000
*******************************************************************************************************/

SET NOCOUNT ON

DECLARE @msg VARCHAR(250)
DECLARE @hostname sysname

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

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

SET NOCOUNT OFF

END