根據(jù)進(jìn)程號(hào)獲取該進(jìn)程所在客戶端的ip地址

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<>0
    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