Sending automatic alert email via sql server

Question

Hi all members Howdo i make sql server sends automatically alert email to admin when for example( expire_date > 10/15/2010) any help would be so much appreciated Best Regards.

Daniel Krainas · Answer

The link in my first reply will explain how to deploy the code to a SQL Server and how to call the function. To learn how to set up a task check out this site: http://msdn.microsoft.com/en-us/library/bb756979.aspx Finally to understand how to use the sqlcmd utility: http://msdn.microsoft.com/en-us/library/ms162773.aspx

dina · Answer

thanks for your reply how can i write this code in T-SQL and make sql server checks it at regular times regardless of the user opens a session or not

Daniel Krainas · Answer

Here is a very rough implementation of a SQL Function that will email the admin when called. This assumes that the checks are done before this function is called in either another application or in an SQL query: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Net; using System.Net.Mail; public partial class UserDefinedFunctions { [SqlFunction] public static void AlertAdmin() { MailMessage m = new MailMessage(sqlServer@tempuri.org admin@tempuri.org Alert This message was sent by the SQL Server); try { SmtpClient sc = new SmtpClient(MySmptServerAddress); sc.send(m); } catch(Exception ex) { //error handling code here. } } } Next the easiest method would be to use window's task scheduler and run a batch file that calls the sqlcmd to perform a SQL query.

dina · Answer

Thanks for your reply but I don't know how to write code subroutine to send alert email if condtion is true another question how can i configure sql server executes this code subroutine at regular times (every hour or every day) Thanks in Advance

Daniel Krainas · Answer

Maybe a managed user defined function could work? You make one just for emailing after doing the check in T-SQL or have it do the check then email if required. Tutorial: http://www.c-sharpcorner.com/UploadFile/pk_khuman/ManagedUserDefinedFunctionUsingCSharp02212007015548AM/ManagedUserDefinedFunctionUsingCSharp.aspx