c# - 我如何从 SQL Server Express Ping 网络设备并通过电子邮件发送查询结果？

Question

我如何从 SQL Server Express Ping 网络设备并通过电子邮件发送查询结果？

  USE Retail2018
    GO
        Declare @IP as nchar(100), @enter code hereEmailPerson nchar(40), @result int, 
    @CmdTxt varchar(100), @Comment varchar(1000)
        Create table #
    Temp(Data varchar(1000))
        DECLARE ShopDevices  CURSOR FOR 
    SELECT  IP
    FROM ShopDevices11 left join Site  on ShopDevices11.SiteId=Site.SiteId
    where AllowLogin=1 
        OPEN ShopDevices  
    FETCH NEXT FROM ShopDevices INTO  @IP
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
                set @CmdTxt = 'ping ' + @IP
            EXEC @result = xp_cmdshell @CmdTxt, no_output
              insert into #Temp  EXEC @result = xp_cmdshell @CmdTxt, no_output
                  IF (@result = 1)
              begin
    Set @Comment = 'ShopDevices ' + char(34) + ltrim(rtrim(@IP)) + char(34) + 
    ' - Contact failed, posibil dispozitiv neconectat'

Print @Comment
declare EmailList cursor for
select EmailAddress from EmailAddresses 
open EmailList
fetch next from EmailList
into @EmailPerson
WHILE @@FETCH_STATUS = 0
BEGIN
--SQL Profile Name
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TestMail',
@recipients = @EmailPerson,
@body = @Comment,
@subject = @Comment      
fetch next from EmailList
into @EmailPerson      
end
CLOSE EmailList
DEALLOCATE EmailList
end

FETCH NEXT FROM ShopDevices
into @IP
END
    CLOSE ShopDevices
DEALLOCATE ShopDevices
  select * from #Temp 
drop table #Temp
GO

标签： c#sql-server