Scheduling A Data Compare

aashishpatel

Hi All,

Can anybody please point me the documentation on how to schedule a data compare? I have scenario where I need to schedule a data compare every 10 minutes and insert new data,do the updates and delete the data which does not exists in the destination. Once I have written the script can I schedule it to run every 10 minutes ?

Thanks in advance.

Cheers,

Aash.

Robert C

Hi,

If you've got the Pro version of SQL Data Compare, you can use the command line version to run a compare and synchronisation. The documentation for that is here.

Once you've built up a command line command that'll run the job you want, I'd suggest putting it into a Windows batch file, and scheduling that using Windows' "Scheduled Tasks" feature in Control Panel.

Hope that helps,
Robert

negatyv

hello
Something i have written yesterday, may be helpfull. It checks DB against backup from current day (assuming that you are using default naming) and sending mail about effect and attachment with output of SQL Data compare.
br
Mike

Dim ArgObj,fileName,cmd,var2, ReturnCode, ReturnSubject
Dim today
Set ArgObj = WScript.Arguments
Set WshShell = WScript.CreateObject("WScript.Shell")

today = Date

fileName = "dbname_db_" & Year(today)

Year(today)
IF Month(today) < 10 then
fileName = fileName & "0" & Month(today)

else
fileName = fileName & Month(today)

end if
IF Day(today) < 10 then
fileName = fileName & "0" & Day(today)
else
fileName = fileName & Day(today)
end if
fileName = fileName & "0100.bak"

'MsgBox filename

cmd = """C:\Program Files\Red Gate\SQL Data Compare 7\sqldatacompare.exe"" /verbose /force /out report_dbname.txt /ignoreidentical /db1 dbname /b2:D:\BACKUP\dbname\" & fileName & " /allowidenticaldatabases"

ReturnCode = WshShell.Run(cmd, 1, True)

If ReturnCode = 0 Then
ReturnSubject = "Comparing Sucessful : " & Date
ReturnMessage = "Comparing of database with backup file was successful, see attached file for details."

Else
ReturnSubject = "Comparing Unsucessful : " & Date
ReturnMessage = "Comparing of database with backup file was unsuccessful, see attached file for details, final error code below" & chr(10) & "Error Code: " & ReturnCode & chr(10)
If ReturnCode = 1 Then
ElseIf ReturnCode = 1 Then
ReturnMessage = ReturnMessage & "General error code."
ElseIf ReturnCode = 3 Then
ReturnMessage = ReturnMessage & "Illegal argument duplication."
ElseIf ReturnCode = 8 Then
ReturnMessage = ReturnMessage & "Unsatisfied argument dependency or violated exclusion when user runs command line. "
ElseIf ReturnCode = 32 Then
ReturnMessage = ReturnMessage & "Value out of range."
ElseIf ReturnCode = 33 Then
ReturnMessage = ReturnMessage & "Value overflow."
ElseIf ReturnCode = 34 Then
ReturnMessage = ReturnMessage & "Invalid value."
ElseIf ReturnCode = 35 Then
ReturnMessage = ReturnMessage & "No / invalid software license or trial period has expired."
ElseIf ReturnCode = 62 Then
ReturnMessage = ReturnMessage & "No objects (tables or views) are selected for comparison."
ElseIf ReturnCode = 63 Then
ReturnMessage = ReturnMessage & "The databases being compared are identical with respect to the data included in the comparison."
ElseIf ReturnCode = 64 Then
ReturnMessage = ReturnMessage & "General command-line usage error."
ElseIf ReturnCode = 65 Then
ReturnMessage = ReturnMessage & "A database error occurred."
ElseIf ReturnCode = 69 Then
ReturnMessage = ReturnMessage & "A resource or service required to run the tool is unavailable."
ElseIf ReturnCode = 73 Then
ReturnMessage = ReturnMessage & "Failed to create report"
ElseIf ReturnCode = 74 Then
ReturnMessage = ReturnMessage & "IO error occurred."
ElseIf ReturnCode = 77 Then
ReturnMessage = ReturnMessage & "Action cannot be completed because the user does not have permission."
ElseIf ReturnCode = 126 Then
ReturnMessage = ReturnMessage & "Execution failed because of an error."
ElseIf ReturnCode = 130 Then
ReturnMessage = ReturnMessage & "Execution stopped because Ctrl+Break."
End If
ReturnMessage = ReturnMessage & chr(10) & "See SQL Data Compare Help for more details"

End If

'============================================================================================================ send mail

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = ReturnSubject
objMessage.From = "dname@example.com"
objMessage.To = "dbadmin@example.com"
objMessage.TextBody = ReturnMessage
objMessage.AddAttachment "D:\DataCompare\report_DBname.txt"


objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2


objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "xx.xx.xx.xx"


objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update


objMessage.Send