Create procedure netsend @dbname varchar(50) 
As

--Script Language and Platform: MS SQL 7.0 and MS SQL 2000
--Objecttive: Before restoreing,upgrading database,database administrator is responsible to 
--inform all the users in that database that they are going to be disconnected and remind 
--them to save their works.
--exec netsend 'xyz'--xyz is the database name
--Created by :Claire Hsu  2003/4/19
--Email:messageclaire@yahoo.com


create table table1000(msg varchar(500))
insert into table1000
select "net send "+ltrim(rtrim(y.hostname))+ ' "we will log you out in 2 min ,please save your work"' from master.dbo.sysprocesses y,master.dbo.sysdatabases x
where x.dbid = y.dbid and x.name = @dbname

declare @msgs varchar(500)
declare cur1 cursor for select msg from table1000
open cur1
fetch next from cur1 into @msgs
while @@fetch_status = 0
begin
exec master.dbo.xp_cmdshell @msgs
fetch next from cur1 into @msgs
end
close cur1
deallocate cur1
drop table table1000



--Usage
--exec netsend 'xyz'