Script to email an SQLCOMPARE.exe report as an attachment

Brian Donahue

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!

/* 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 */