Scheduling Procedures in sql server 2008 am i doing it the right way or there are issues?

Question

EDIT : Problem with the email I get mailbox unavailable exception! DBEmail.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Xml.Serialization;
using System.IO;
using System.Xml;
using System.Text;
using System.Net;
using System.Net.Mail;
using System.Xml.Linq;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static void DBEmail(string Sender, string SendTo, string Subject, string Body, string mailServer)
    {
        System.Net.Mail.MailMessage m = new System.Net.Mail.MailMessage();
        m.From = new System.Net.Mail.MailAddress(Sender);
        m.To.Add(new System.Net.Mail.MailAddress(SendTo));
        m.Subject = Subject;
        m.Body = Body;
        System.Net.Mail.SmtpClient client = null;
        client = new System.Net.Mail.SmtpClient();
        client.Host = mailServer;
        client.UseDefaultCredentials = false;
        client.DeliveryMethod = SmtpDeliveryMethod.Network;
        client.Credentials = new System.Net.NetworkCredential("[email protected]", "password");
        client.Send(m);
    }
}

I have to run some nightly stored procedures. So I have created a .sql file which has basically -

exec proc1
exec proc2...

So is it fine if i just run them like that or they need to wrapped in begin end or something else... Fruther I have a batch file which i will schedule to run-

sqlcmd -S myserver.myserver.com -i 
D:\Scripts\StartProcs.sql -U username -P password -o D:\Scripts\log.txt

Is this the above script correct ? I mean it works it tested running it...but i just need to make sure if i am not missing any other issues here ? Because I saw some other sqlcmd commands which where really long and took lot of parameters...I just want to make sure i am not missing any important parameters..

Please correct me above with either .sql file or batch file...if i have not taken in consideration any issues? ...Thanks

Answer 1

Multiple execs in a row is just fine. We've run nightly jobs that way for many years.

Instead of the Windows Task Scheduler, consider scheduling our jobs in SQL Agent. SQL Agent is a part of SQL Server, and it's where most DBA's would expect to find SQL jobs. SQL Agent comes with the ability to send error mails when something goes wrong, and has a built-in error logging system.

But a scheduled batch file will work fine too.

Answer 2

Another idea is to separate each exec with the GO batch command.

e.g.

exec proc1<
GO
exec proc2
GO...

This means that each exec will be treated on its own.

Answer 3

The only problem I see is that with this technique (unless you have something built into the procs) you won't be automatically notified if something goes wrong, or in that case if it completes successfully.

In SQL Server Express you can add a CLR Function that you can use to send you an email reporting the results of the sprocs above.

And here is how you would do it using all free tools.

First Make sure CLR Integration is enabled by executing this

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

You will also need to either sign the assembly or mark the DB as Trustworthy. To mark the DB Trustworthy make sure you are logged into SQL Server as a member of the SysAdmin role and Execute this

ALTER DATABASE YourDBName SET TRUSTWORTHY ON;

Next save the following as C:\Code\DBEmail.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static void DBEmail(string Sender, string SendTo, string Subject, string Body, string mailServer)
    {
        System.Net.Mail.MailMessage m = new System.Net.Mail.MailMessage();
        m.From = new System.Net.Mail.MailAddress(Sender);
        m.To.Add(new System.Net.Mail.MailAddress(SendTo));
        m.Subject = Subject;
        m.Body = Body;

        System.Net.Mail.SmtpClient client = null;
        client = new System.Net.Mail.SmtpClient();
        client.Host = mailServer;

        client.Send(m);
    }
}

Then find the C# Comand line compiler in the appropriate .Net Framework directory, in my case it is "C:\Windows\Microsoft.NET\Framework\v2.0.50727") cd to it and execute this.

csc.exe /target:library /out:C:\Code\DBEmail.dll C:\Code\DBEmail.cs

Then copy the resulting C:\Code\DBEmail.dll to the SQL Server if you are not on it already and in Management studio execute this.

CREATE ASSEMBLY [DBEmail]
AUTHORIZATION [dbo]
From 'C:\Code\DBEmail.dll'
WITH PERMISSION_SET = External_Access

Finally create a sproc to map to the CLR Code by executing this

CREATE PROCEDURE [dbo].[usp_DBEmail]
    @Sender [nvarchar](255),
    @SendTo [nvarchar](255),
    @Subject [nvarchar](255),
    @Body [nvarchar](max),
    @mailserver [nvarchar](55)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DBEmail].[StoredProcedures].[DBEmail]

Then you can just wrap the code inside your scheduled sprocs in Try Catch and if an error occurs send youself the error message in the email like this.

BEGIN TRY
  --Your SQL Stuff
END TRY
BEGIN CATCH
    Declare @error as nvarchar(max);
    SELECT @error = ERROR_NUMBER();
    Exec usp_DBEmail @Sender='[email protected]', @SendTo='[email protected]', @subject='Whoops', @Body=@error, @mailserver='smtp_ip'
END CATCH;

Hope this is helpful!