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.. pP_cmdshell 'ARP -A'
IF @@ Error <> 0
Begin
Raiserror ('the level for job_id:% D shop becom_id% 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
