Automating A Comparison Project

lc6529

Ok, I have a project setup like I want and all seems to work quite well.

I would now like to automate this project using task scheduler or some other utility.

Can anyone tell me how to do this?

Ben Pinchen

You can automate the project using the Command Line Interface (you will need to have the Pro version of the software).

There is a help file for the Command Line Interface which includes a number of examples. This can be found by going to Start > All Programs > Red Gate Bundle 5 > The Command Line Interface.

In it’s simplest form you’d save the command below as a batch file and then use Windows scheduler to automate it.

sqldatacompare /project:"C:\Documents and Settings\username\Local Settings\Application Data\Red Gate\SQL Data Compare 5\project.sdc"

To see the possible options for using it, open a command prompt, navigate to the installation folder and type sqldatacompare /?

lc6529

Ok, I understand the command line option and have tried to follow the examples as shown in the documentation.

IE: SQLDATACOMPARE /PROJECT:C:\"Project Name"

However, I could not find an SDC file anywhere! S I opened the Data Compare program and see projects listed. I right click on one of the projects and see an option "LOCATE ON DISK"

I chose this option and find project files but they have names such as
usmafrcadev.BoseLabels v usmafrcadev.Labelvision_Master 23-04-2007.14-24-26.6176.sdc

I tried putting this name in EXACTLY, I even COPIED the name and the path the file was located on and put it in my test.bat file.

I get the error Invalid File: [Path and FileName]

So, I then copied the file to the root folder of my C drive and gave it a shorter name - IE: Test.SDC, I tried SQLDATACOMPARE /PROJECT:"C:\TEST.SDC" and I still get

INVALID FILE C:\TEST.SDC

I tried burning CD's to the software Gods but cannot get this thing to work.

Do you have any other suggestions?

lc6529

Doh!

I found the problem, I am ashamed to admit it but I will post the resolution to help others.

My batchfile said

SQLCOMPARE /PROJECT:C:\"Project Name"

not

SQLDATACOMPARE /PROJECT:C:\"Project Name"

Once I made the change all worked perfectly.

I'm off to the eye doctor now!

DBNewbie_2007

Ok... I setup a .BAT file to process a previously created SQL Data Compare project,

"C:\Program Files\Red Gate\SQL Data Compare 5\sqldatacompare" /project:"C:\Documents and Settings\username\Local Settings\Application Data\Red Gate\SQL Data Compare 5\projectA.sdc" /out:"D:\RedGate\ProjectASyncSummary.txt"

I plan on setting this up using Windows Server 2003 Scheduler. However, I have multiple compares/syncs I need to perform weekly. Can I setup all of them in one batch file?

\sqlcompare /project:... projectA.sdc /out:...
\sqlcompare /project:... projectB.sdc /out:...
\sqlcompare /project:... projectC.sdc /out:...

Seems much more efficient than trying to setup a data ompare/sync for each DB and scheduling them all individually.

If I have to schedule them all individually, can they run concurrently (multithreaded)?

Thanks!

JR

richardjm

Running them in a .BAT file should work - at least I can't see why it wouldn't.

As to running them all concurrently that should also work although bear in mind the load that this will place on the server would slow down each individual synch.

HTH

Brian Donahue

Hi,

If you want to construct a batch file that runs a series of commands asynchronously (doesn't wait for the previous command to complete before going on to the next), you'll want to use the START command in the batch files like this:

START sqlcompare /pr:myproject.scp

DBNewbie_2007

Ok... thanks everyone for your responses! I setup one large batch file and everything seemed to work well (setup on Wednesday, ran on Friday when I was on vacation).

I found one "issue" with setting up the batch file:
"C:\Program Files\Red Gate\SQL Data Compare 5\sqldatacompare" /project:"C:\Documents and Settings\username\Local Settings\Application Data\Red Gate\SQL Data Compare 5\projectA.sdc" /out:"D:\RedGate\ProjectASyncSummary.txt"

If the output file "ProjectASyncSummary.txt" already existed (since this is going to be a scheduled process", the application complained that the file existed and then stopped. My solution was a script to rename the file after it was "created" and "written to", put in the batch file immediately after the sqldatacompare command listed above:

REM Rename File Original Script by Mark Yocom,
for /f "delims=/ tokens=1-3" %%a in ("%DATE:~4%") do (
for /f "delims=:. tokens=1-4" %%m in ("%TIME: =0%") do (
rename ProjectASyncSummary.txt ProjectASyncSummary-%%c-%%b-%%a-%%m%%n%%o%%p.txt
)
)

This will rename the file and attach a date/time stamp. Worked very well!

I would also like to see one enhancement... some sort of "sync complete" comment in the output file would be nice.