Scheduling A Data Compare

aashishpatelaashishpatel Posts: 2
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.

Comments

  • 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
    Robert Chipperfield
    Red Gate
  • 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
Sign In or Register to comment.