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

/*******************************************************************************************************
Written by: Narayana Vyas Kondreddi
Date written: November 26th 2000
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
Email: answer_me@hotmail.com
*******************************************************************************************************/
 
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