Advice on XML argfile tags

dakaratekid

Hello,

I am using the sqltools command line sqlcompare.exe. I am able to generate both snapshots, and a comparison file. When I pass it an xml argfile, most of my options are being ignored for both makesnapshot and for the report attributes. Specifically, identical information is reported, replication triggers are included, and many of my exclusions are included. Furthermore, the report is an 11 meg file, and it is nearly impossible for me to parse through it to debug, because it brings my workstation to a crawl. Also, the pipe "|" is used in my exclusions representing a logical 'or'. Is there a logical 'and' designator? The ampersand just throws errors.

Can anybody spot some obvious problems for me?

snapshot:

<?xml version="1.0"?>
<commandline>
    <database1>MyDB</database1>
    <server1>SLC-BCSQL01</server1>
    <includeIdentical>false</includeIdentical>
    <out>f:\Projects\database_versioning\compare\bc_snapshot_slc.txt</out>
    <makesnapshot>f:\Projects\database_versioning\compare\bc_snapshot_slc.snp</makesnapshot>
    <outputwidth>80</outputwidth>
    <options>IgnoreReplicationTriggers,IgnoreIdentityProperties,IgnoreConstraintNames,IgnoreWhiteSpace,IgnoreKeys,IgnoreComments</options>
</commandline>

compare:

<?xml version="1.0"?>
<commandline>
    <snapshot1>F:\Projects\database_versioning\snapshot\bc_snapshot_lax.snp</snapshot1>
    <snapshot2>F:\Projects\database_versioning\snapshot\bc_snapshot_slc.snp</snapshot2>
    <exclude>Table:conflict_.+|Deprecated_.+|sync_obj.+|sys.+|MS.+</exclude>
    <exclude>StoredProcedure:sp_MS.+|Deprecated_.+|sync_obj.+|sys.+|MS.+</exclude>
    <exclude>Trigger:sp_MS.+|trg_MS.+</exclude>
    <includeIdentical>false</includeIdentical>
    <reporttype>XML</reporttype>
    <report>f:\Projects\database_versioning\compare\bc_compare.xml</report>
    <out>f:\Projects\database_versioning\compare\bc_compare.txt</out>
    <outputwidth>80</outputwidth>
    <options>IgnoreReplicationTriggers,IgnoreIdentityProperties,IgnoreConstraintNames,IgnoreWhiteSpace,IgnoreKeys,IgnoreComments</options>
</commandline>

TIA,
dKK

dakaratekid

arghh...looks like I posted this in the wrong forum. Mods, can you move this to the SQLToolkits forum?

Thanks,
dKK

Eddie D

Hi Thank you for your post into the Forum.

Looking at your Arg File for making a snapshot, you have included a number of options that do not need to be there. For creating a snapshot file all you need is the following syntax in your arg file:

<?xml version="1.0"?>
<commandline>
<database1>database_name</database1>
<server1>Server_name</server1>
<makesnapshot>D:\DBSnapshots\DBSnapshot.snp</makesnapshot>
</commandline>

As for your second arg file to perform a comparison between two snapshots. The <includeIdentical>False</includeIdentical> option is ONLY for the report. Therefore the report file you have generated in the arg file should not contain identical objects. The <out> file will still shown what is identical, the out file is the replacement for the Results Summary view in the GUI, the GUI still displays the identical objects.

When using the <exclude> or <include> options and you specify the object you use to include or exclude is written as a regular expression. Therefore you must use the escape character '\' to escape fullstops and square brackets for example /include:table:\[dbo\]\.\[tablename\]. So including this into an arg file example syntax could be as follows:

<?xml version="1.0"?>
<commandline>
<snapshot1>D:\Snapshots\DB1Snapshot.snp</snapshot1>
<snapshot2>D:\Backup\DB2Snapshot.snp</snapshot2>
<exclude>Table:\[dbo\]\.\[table1]</exclude>
<exclude>StoredProcedure:\[dbo\]\.\[sp_procedure1\]</exclude>
<reporttype>Simple</reporttype>
<report>D:\Snapshots\CompareReport.htm</report>
<includeIdentical>True</includeIdentical>
<out>D:\Snapshot\CompareSummary.txt</out>
<force/>
<options>IgnoreReplicationTriggers,IgnoreIdentityProperties,IgnoreConstraintNames,IgnoreWhiteSpace,IgnoreKeys,IgnoreComments</options>
</commandline>

When running your command line task and call your arg file, you may want to add the /verbose switch. This option records the actions of the options into the <out> file. For example sqlcompare /argfile:'path to file.xml' /verbose.

I hope the above helps you set up your arg file.

Best Regards
Eddie
Red Gate Software
Technical Support

dakaratekid

Thanks Eddie,

Can you comment on my specific use of regular expressions from the OP? Specifically, the logical 'or' (|) seems out of place on an exclude. Is there a logical ‘and’ operator? The ‘&’ causes errors. I’m pretty well versed in regular expressions, but do I specifically have to schema/owner qualify my excludes with \[dbo\]\. ?? I understand the '.' has special meaning, i.e any character. Is it sufficient to write them as I have them? Could I instead write them as:

<exclude>Table:.+conflict_.+|.+Deprecated_.+|.+sync_obj.+|.+sys.+|.+MS.+</exclude>
    <exclude>StoredProcedure:.+sp_MS.+| .+Deprecated_.+|.+sync_obj.+|.+sys.+| .+MS.+</exclude>
    <exclude>Trigger:.+sp_MS.+|.+trg_MS.+</exclude>

Thanks again.