Red Gate forums :: View topic - Script to email an SQLCOMPARE.exe report as an attachment
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Toolkit Previous Versions
SQL Toolkit Previous Versions forum

Script to email an SQLCOMPARE.exe report as an attachment

Search in SQL Toolkit Previous Versions forum
Post new topic   This topic is locked: you cannot edit posts or make replies.
Jump to:  
Author Message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6677

PostPosted: Wed Jul 13, 2005 10:30 am    Post subject: Script to email an SQLCOMPARE.exe report as an attachment Reply with quote

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:

/* 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 */
Back to top
View user's profile Send private message
Display posts from previous:   
This topic is locked: you cannot edit posts or make replies. All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group