How to use an argument file in the CLI

Applies to
ApexSQL Data Diff, ApexSQL Diff, ApexSQL Doc, ApexSQL Script, ApexSQL Log, ApexSQL Trigger, ApexSQL Clean

Summary
This article explains how to use an argument file in the Command Line Interface with ApexSQL tools.

Description
Instead of setting the value of switches directly in the CLI, setting up an argument file with the desired options saves time, because it allows reducing the number of switches that have to be specified in the CLI command.

An argument file is easier to read, especially when code in the CLI is too long and contains complex rules, for example switches for including, or excluding particular objects.

Quick tip icon

Quick tip:

Using an argument file is particularly recommendable for repeated processes which have the same setup of the options

Creating an argument file

An argument file contains the root element and the root sub-elements. The root element defines the beginning and the end of an argument file, and any term can be used for its name. The sub-elements define the switches with their names and values.

<ArgFile>
      <
switch1>switch1_value</switch1>
      <
switch2>switch2_value</switch2>
     

</ArgFile>

In the following example, we’ll create an argument file for ApexSQL Data Diff, but for other tools it is similar. The following command contains switches <d1>FruitImport2013</d1> and <d2>FruitImport2014</d2> for comparison of two SQL databases located on the local server, the switch <dso>dfkt dnui dcc</dso> that defines the data synchronization options, the switch <rts>m a</rts> that defines the synchronization record types, the switch <of>C:\test1\sync.sql</of> that defines the path of the synchronization script, the switch <v /> that defines displaying all messages on the processed operation, and the switch <f /> that defines overwriting of the existing file which has the same path as the output file of the process:

CD C:\Program Files \ApexSQL\ApexSQLDataDiff2013>ApexSQLDataDiff.com /d1:FruitImport2013 /d2:FruitImport2014 /dso:dfkt dnui dcc /rts:m a /of:C:\test1\sync.sql /v /f

An equivalent argument file for this command is:

<ArgFile>
      <
d1>FruitImport2013</d1>
      <
d2>FruitImport2014</d2>
      <
dso>dfkt dnui dcc</dso>
      <
rts>m a</rts>
      <
of>C:\test1\sync.sql</of>
      <v/>
      <
f/>
</
ArgFile>

To create an argument file, save the desired code as an xml file:

Using an argument file

After creating an argument file, run the Command Prompt as an Administrator, and set the path to the installation directory of the application, e.g. ApexSQL Data Diff. To load an argument file, use the “/af” switch:

CD C:\Program Files <x86>\ApexSQL\ApexSQLDataDiff2013>ApexSQLDataDiff.com /af:ArgFile.xml

If the argument file is saved outside of the application’s installation folder the whole path to the file needs to be specified:

CD C:\Program Files <x86>\ApexSQL\ApexSQLDataDiff2013>ApexSQLDataDiff.com /af:C:\test1\ArgFile.xml

In the image below execution of an argument file is shown:

Image illustrating the execution of an argument file int the CLI

Quick tip icon

Quick tip:

The “/af” switch cannot be combined with other switches

If any other switch is added after specifying an argument file, a warning message will appear:

Image showing the warning message appearing if any other switch is added after specifying an argument file

Examples

Below are examples of an argument file used with ApexSQL Data Diff tool. The command line versions of the examples (using switches) are also provided.

  1. Compare the data in all tables in two local databases

    Using the CLI:

    CD C:\Program Files <x86>\ApexSQL\ApexSQLDataDiff2013>ApexSQLDataDiff.com /d1:FruitImport2013 /s1:SourceServer /d2:FruitImport2014 /s2:DestinationServer

    Using an argument file:

    <ArgFile>
         <
    d1>FruitImport2013</d1>
         <
    d2>FruitImport2014</d2>
    </
    ArgFile>

  2. Compare the data in all tables in two databases on different servers

    Using the CLI:

    CD C:\Program Files <x86>\ApexSQL\ApexSQLDataDiff2013>ApexSQLDataDiff.com /d1:FruitImport2013 /s1:SourceServer /d2:FruitImport2014 /s2:DestinationServer

    Using an argument file:

    <ArgFile>
         <
    d1>FruitImport2013</d1>
         <
    s1>SourceServer</s1>
         <
    d2>FruitImport2014</d2>
         <
    s2>DestinationServer</s2>
    </
    ArgFile>

  3. Compare the data in all tables in two databases on different servers using SQL Server authentication

    Using the CLI:

    CD C:\Program Files <x86>\ApexSQL\ApexSQLDataDiff2013>ApexSQLDataDiff.com /s1:SourceServer /d1:FruitImport2013 /u1:SourceMasterFruit /p1:pacman01 /s2:DestinationServerFruit /d2:FruitImport2014 /u2:DestinationFruit01 /p2:bananaman1

    Using an argument file:

    <ArgFile>
         <
    s1>SourceServer </s1>
         <
    d1>FruitImport2013</d1>
         <
    u1>SourceMasterFruit</u1>
         <
    p1>pacman01 </p1>
         <
    s2>DestinationServer</s2>
         <
    d2>FruitImport2014</d2>
         <
    u2>DestinationFruit01</u2>
         <
    p2>bananaman1</p2>
    </
    ArgFile>

  4. Compare the data only in tables which are named “Quarter1” on different servers

    Using the CLI:

    CD C:\Program Files <x86>\ApexSQL\ApexSQLDataDiff2013>ApexSQLDataDiff.com /d1:FruitImport2013 /s1:SourceServer /d2:FruitImport2014 /s2:DestinationServer /inc:8:”^Quarter1$”

    Using an argument file:

    <ArgFile>
         <
    d1>FruitImport2013</d1>
         <
    s1>SourceServer</s1>
         <
    d2>FruitImport2014</d2>
         <
    s2>DestinationServer</s2>
         <
    inc>8:^Quarter1$</inc>
    </
    ArgFile>

  5. Compare the data in all tables in two databases on different servers using SQL Server authentication, set data synchronization options, set synchronization record types, display all messages on the processed operation, set the path of the synchronization script, and overwrites the existing synchronization script

    Using the CLI:

    CD C:\Program Files <x86>\ApexSQL\ApexSQLDataDiff2013>ApexSQLDataDiff.com /s1:SourceServer /d1:FruitImport2013 /u1:SourceMasterFruit /p1:pacman01 /s2:DestinationServerFruit /d2:FruitImport2014 /u2:DestinationFruit01 /p2:bananaman1

    Using an argument file:

    <ArgFile>
         <
    s1>SourceServer </s1>
         <
    d1>FruitImport2013</d1>
         <
    u1>SourceMasterFruit</u1>
         <
    p1>pacman01 </p1>
         <
    s2>DestinationServer</s2>
         <
    d2>FruitImport2014</d2>
         <
    u2>DestinationFruit01</u2>
         <
    p2>bananaman1</p2>
         <
    dso>dfkt dnui dcc</dso>
         <
    rts>m a</rts>
         <
    of>C:\test1\sync.sql</of>
         <v/>
         <
    f/>
    </
    ArgFile>

Explaining expressions in an argument file

<s1>SourceServer </s1>The name of the source SQL instance
<d1>FruitImport2013</d1>The name of the source database
<u1>SourceMasterFruit</u1>Specifies the login for the source SQL instance
<p1>pacman01</p1>Specifies the password for the login for the source SQL instance
<s2>DestinationServer</s2>The name of the destination SQL instance
<d2>FruitImport2014</d2>The name of the destination database
<u2>DestinationFruit01</u2>Specifies the login for the destination SQL instance
<p2>bananaman1</p2>Specifies the password for the login for the destination SQL instance
<inc>8:^Quarter1$<inc>Specifies certain objects for comparison
<dso>dfkt dnui dcc</dso>Data synchronization options: dfkt – Disable foreign keys and triggers, dnui – Drop not unique indexes, dcc – Disable check constraints
<rts>m a</rts>Synchronization record types for: m – Missing, a – Additional, d – Different
<of>C:\test1\sync.sql</of>The path of the synchronization script or the comparison report
<v/>Display all messages on the processed operation
<f/>Overwrites the existing file which has the same path as the output file of the process