Hello people,
Good night!
In this post I will demonstrate how to validate by SQL Server if a server is responding to the network using CLR (C #) and the PING class, which simulates a ping request that you make at the DOS prompt in Windows or in the Unix Shell.
In everyday life, I use this function a lot to check if a file-sharing server, for example, is available to write database routine files. Otherwise, I can have my routine trigger an email or even write these files to another directory. We can use this function to create server monitoring as well. Anyway, the possibilities are many, and vary according to your need and creativity.
CLR Function Source Code (C #):
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | using System; using System.Data.SqlTypes; using System.Net.NetworkInformation; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlBoolean fncMaquina_Ligada(SqlString Ds_Hostname) { try { var ping = new Ping(); var reply = ping.Send(Ds_Hostname.Value, 5); //timeout em segundos return (reply != null) && (reply.Status == IPStatus.Success); } catch (Exception e) { return false; } } } |
Once compiled into the database, we can use the function like this:
And that's it folks!
Big hug and until the next post.
SQL Server - How to check if the machine is connected check computer server is online ping
SQL Server - How to check if the machine is connected check computer server is online ping
Good morning Dirceu, I need to try to use this function but I get the following error: “System.Security.SecurityException: Failed request for permission type 'System.Net.NetworkInformation.NetworkInformationPermission, System, Version=4.0.0.0, Culture=neutral , PublicKeyToken=b77a5c561934e089'.”
If you can help me understand what's going on, I'd really appreciate it!
Hi Thiago. Have you seen if it's not the permission of the assembly? To ping, you need to publish as external access or unsafe. The default (safe) mode will not work.
It is exactly the Dirceu permission! But when I change it to unsafe or external access I can't even publish because it gives the following error: "The assembly is trusted when one of the following conditions is true: The assembly is signed with an asymmetric or certificate key that has a login corresponding to the UNSAFE ASSEMBLY permission ”