Script to email an SQLCOMPARE.exe report as an attachment

Forum for users of SQL Toolkit 3,4,and 5

Script to email an SQLCOMPARE.exe report as an attachment

Postby Brian Donahue » Wed Jul 13, 2005 10:30 am

Hello all,

I'd just done this script for a customer and thought there may be some interest in it. The script will compare two databases' schema using SQLCOMPARE.exe and email an HTML report as an attachment to an address you can specify.

You can use this for management purposes -- ie. put it on Windows Scheduled Tasks and have it automatically email a report to you about differences in the databases.

The command line for this would look something like this:
cscript emailreport.js /mailto:"user@example.com" /db1:problems /db2:problems_beta...

Basically you can specify any arguments to this script that SQLCOMPARE.exe accepts and they will be passed through to the program.

I hope someone finds this useful!
Code: Select all
/* generate a SQL Compare HTML report and email it to a user */
/* Req: WScript 5.6 (Win 2000, XP) */
/* Usage: /mailto:<address> /db1:<first database> /db2:<second database> /s1:<first server> /s2:<second server>
/u1:<first user> /u2:<second user> /p1:<first password> /p2:<second password>*/

/* Where is SQLCOMPARE.exe? */
var progdir="\\"c:\\\\program files\\\\red gate sql bundle\\\\";

/*What server can relay email?*/
var SMTPServer="localhost";

/*What email address shall we say this is from? */
var sentFrom="report@localhost";

/* -- nothing more configurable after this -- */

/* Get a location on a temporary file */

var obWsh=new ActiveXObject("WScript.Shell");
var obEnv=obWsh.Environment("PROCESS");
var tempDir=obEnv("TMP");
var tempFileName=tempDir+"\\\\SqlCompareReport.htm";

/* Apply specified args to SQLCOMPARE.exe */

var command=progdir+"sqlcompare.exe\\" /r:"+tempFileName+" ";
var i=0;
var obRawArgs=WScript.Arguments;

   for (i=0; i < obRawArgs.length; i++) {      
   if (obRawArgs.item(i).indexOf("/mailto:")==-1) command+=obRawArgs.item(i)+" ";
   }


/* run the command and wait for return */
   obWsh.Run(command, 0, 1);

var obFs=new ActiveXObject("Scripting.FileSystemObject");
var obMessage = new ActiveXObject("CDO.Message");
var obCDOConf = new ActiveXObject("CDO.Configuration");
var conffields = obCDOConf.Fields

   conffields("http://schemas.microsoft.com/cdo/configuration/sendusing")=2; /*cdoSendUsingPort*/
   conffields("http://schemas.microsoft.com/cdo/configuration/smtpserver")=SMTPServer;
   conffields("http://schemas.microsoft.com/cdo/configuration/smtpserverport")=25;
   conffields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")=0; /*cdoAnonymous*/

      obMessage.Configuration=obCDOConf;
      obMessage.Configuration.Fields.Update();
      obMessage.To = WScript.Arguments.Named("mailto");
      obMessage.From = sentFrom;
      obMessage.Subject = "SQL Compare Report";
      obMessage.TextBody = "Please see the attached SQL Compare report";

         if (obFs.FileExists(tempFileName)) {
            obMessage.AddAttachment(tempFileName);
            obMessage.Send();
         /* Cleanup: delete the old file */
            obFs.DeleteFile(tempFileName);
         }
         else {
            obMessage.TextBody="SQL Compare Report could not be generated.";
            obMessage.Send();
         }

      /* Cleanup */
      obFs=null;
      obMessage=null;
      obCDOConf=null;
      obWsh=null;

      /* End Script */
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Return to SQL Toolkit Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests