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