create procedure dbo.sp_spidtoip @spid int as -- spid to mac -- lj declare @mac as varchar(12) select @mac = net_address from master..sysprocesses where spid = @spid -- mac to ip declare @macdisplay as varchar(18) declare @ip as varchar(15) create table #temp (output varchar(255) null) set nocount on insert into #temp exec master..xp_cmdshell 'arp -a' if @@error begin raiserror ('the level for job_id:%d should be between %d and %d.', 16, 1) --rollback transaction end select @macdisplay = left(@mac, 2) + '-' + substring(@mac, 3, 2) + '-' + substring(@mac, 5, 2) + '-' + substring(@mac, 7, 2) + '-' + substring(@mac, 9, 2) + '-' + substring(@mac, 11, 2)select @ip = substring(output, 3, 15) from #temp where output like '%' + @macdisplay + '%' -- resolve the ip --declare @cmd as varchar(100) --select @cmd = 'master..xp_cmdshell "ping -a ' + @ip + '"' --exec (@cmd) drop table #temp set nocount off go
