| Author |
Message |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6341
Location: Red Gate Software
|
 Posted: Wed Jul 13, 2005 10:30 am Post subject: Script to email an SQLCOMPARE.exe report as an attachment |
 |
|
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 */
|
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
 |
|
|
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
