Print ''
Print '----------------------------------------------------'
Print ' JFTSql_20_PublicProceduresTSQL-TOOLS-JF Scripts    '
Print ' FirstRevision: 19.03.2006'
Print ' CurrRevision : 13.02.2007'
Print ' Autor        : J.Feuerstein'
Print ' '
Print ' Log file:      #DB_ldf_DPNE#'
Print '----------------------------------------------------'
GO

use [#DB_N#]
GO

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO 

-- To disallow advanced options to be changed.
EXEC sp_configure 'show advanced options', 0
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO


Print '-- [notify_users] --------------------------------------'
If Exists (select Name from sysobjects where Name = "notify_users" and type = "P")
Drop Procedure notify_users
GO


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 /DOMAIN:' + 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

Print ''
Print '---------------------------------------------------------'
Print ' JFTSql_20_PublicProceduresTSQL-TOOLS-JF Scripts Imported'
Print '---------------------------------------------------------'
GO
