use model go drop proc send_message go create proc send_message as begin create table #tspid( spid int null, ecid int null, status varchar(20) null, loginname varchar(20) null, hostname varchar(20) null, blk bit null, dbname varchar(20) null, cmd varchar(20) )insert into #tspid(spid,ecid,status,loginname,hostname,blk,dbname,cmd) exec sp_who create table #userip(id int identity,txt varchar(1000))--可以得到hostName declare @cmdStr varchar(100), @hostName varchar(30), @userip varchar(20), @sendstr varchar(100)declare tspid cursor for select hostname from #tspid where spid>50 open tspid fetch next from tspid into @hostname WHILE @@FETCH_STATUS = 0 begin select @cmdStr='ping '+@hostName insert into #table(txt) exec master..xp_cmdshell @cmdStr select @userip=substring(txt,charindex('[',txt)+1,charindex(']',txt)-charindex('[',txt)-1) from #table where id=2 --得到IP地址,也可以不直接用hostName select @sendstr='net send '+@userip+'Messages!' exec master..xp_cmdshell @sendstr fetch next from tspid into @hostname end drop table #tspid drop table #userip endgo
go
drop proc send_message
go
create proc send_message as begin
create table #tspid(
spid int null,
ecid int null,
status varchar(20) null,
loginname varchar(20) null,
hostname varchar(20) null,
blk bit null,
dbname varchar(20) null,
cmd varchar(20)
)insert into #tspid(spid,ecid,status,loginname,hostname,blk,dbname,cmd) exec sp_who create table #userip(id int identity,txt varchar(1000))--可以得到hostName
declare @cmdStr varchar(100), @hostName varchar(30), @userip varchar(20), @sendstr varchar(100)declare tspid cursor for select hostname from #tspid where spid>50
open tspid fetch next from tspid into @hostname
WHILE @@FETCH_STATUS = 0
begin
select @cmdStr='ping '+@hostName
insert into #table(txt) exec master..xp_cmdshell @cmdStr select @userip=substring(txt,charindex('[',txt)+1,charindex(']',txt)-charindex('[',txt)-1) from #table where id=2 --得到IP地址,也可以不直接用hostName
select @sendstr='net send '+@userip+'Messages!'
exec master..xp_cmdshell @sendstr
fetch next from tspid into @hostname
end drop table #tspid
drop table #userip
endgo