Sending messages to all users connect to SQL Serve

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Sending messages to all users connect to SQL Serve

Author

Topic

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-21 : 04:08:33

CREATE PROCEDURE [dbo].[NetSend2Users] @NotificationMsg VARCHAR(100) = NULL
AS
BEGIN
/*******************************************************************************************************
To send NET SEND messages to all the connected SQL Server users in an NT LAN, no 95, 98 ME machine...
Input is Message to be sent
*******************************************************************************************************/
DECLARE @DefMsg varchar (100)
Set @DefMsg = 'SQL Server Shutting Down !!! Please Logout from the system'

IF @NotificationMsg IS NULL
Set @NotificationMsg = @DefMsg

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(@NotificationMsg) + ' "'
EXEC (@msg)
SELECT @hostname= min(RTRIM(hostname)) FROM master.dbo.sysprocesses (NOLOCK)
WHERE hostname <> '' and hostname > @hostname
END

SET NOCOUNT OFF

END
GO

Kapil Arya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources