SQL Data Compare Project

trevornews

We have quite a lot of databases and at any time 2 of these databases need to be compared but based on different settings because the business requirement needs that only some tables across databases are in sync at any given time.

What I am trying to achieve is to let the DBA configure Projects as and how they like and then use this project file to run as a scheduled job. The ouput of this process will be saved to text files for now and then I would either send SMS/EMAIL/Net Send alerts to the DBA to manually SYNC these tables based on the scripts.

This will enable us to isolate and find repliaction problems before Production goes down due to missing records or incompatible updates.

Questions
1) When I save the project and open the Project in VB.NET, I do not see the connection settings. It always defaults to Integrated Security and the Server is always local. Is there some way to get to this saved information about the connection settings.

2) Is there some automated way in which I could run this job and then based on the ouput decide what to do. If you have a sample or something then let me know. I will send you an offline message.

Regards,

Trevor Benedict R
Western Digital

Brian Donahue

Hi Trevor,

I can see two ways of doing this scheduling. One is to write your own application in Visual Studio .net which can set the server connection information to be different before each compare and trap any exceptions during the compare and act accordingly.

It would probably be a bit easier to use the command-line version of Data Compare. If you're licensed for it as part of the developer bundle, you can use SQLDataCompare.exe as part of a batch file. You can specify the server and database names on the command line, run a comparison, then check the result returned from SQLDataCompare.exe by examining the ERRORLEVEL in the batch file to determine what to do next. For example, you could change the NET SEND message depending on the return code set by the program. Here are a list of return codes:

0 Success.
1 General error code.
3 Illegal argument duplication. Some arguments may not appear more than once in a command-line. If such arguments appear more than once this exit code will be returned.
32 Value out of range. Numeric value supplied for an argument that is outside the range of valid values for that argument.
33 Value overflow. The magnitude of a value supplied for an argument is too large and causes an overflow.
34 Invalid value. The value supplied for an argument is invalid.
35 No / invalid software license or trial period has expired.
62 No objects (tables and / or views) are selected for comparison.
63 The databases being compared are identical with respect to the data included in the comparison.
64 General command-line usage error.
65 Indicates that a database error occurred. This probably indicates that a constraint violation or other problem occurred whilst executing the synchronization script. You should check your schema to ensure that you are migrating data in such a way as to not violate any constraints in the schema. You should either modify the subset of data you are migrating, or tweak the SQL Data Compare command-line options to overcome the problem.
65 Data error. Some input data required by the tool is invalid or corrupt.
69 A resource or service required to run the tool is unavailable.
74 IO error occurred. Generally returned if the program attempts to write to a file that already exists without the user having specified the /force option.
77 Action cannot be completed because the user does not have permission.
126 Execution failed because of an error.