The Job Updates SQL Scripts are where you define the SQL to import job updates from an external database into DMS.  Before you add the SQL scripts, one or more connection strings must be defined.  Any number of SQL scripts can be defined.  For help writing SQL scripts in DMS, please see Hints and Tips Writing SQL Scripts.  For details on the SQL columns required for the import, please see SQL Job Updates Columns Required.

To define job updates SQL scripts, select Import | Import settings | Job Updates SQL Scripts.

A window will appear with the current list of defined job import SQL scripts.  The example below shows the list of Job Updates SQL Scripts:

Valid commands are:

1. New To add a new job updates SQL script, select File | New or click on the new icon.
2. Edit To edit an existing job updates SQL script, highlight the job updates SQL script in the grid and select File | Edit or click on the edit icon.
3. Delete To delete an existing job updates SQL script, highlight the job updates SQL script in the grid and select File | Delete or click on the delete icon.  DMS will confirm the delete.  Click OK to delete the job updates SQL script, or click Cancel to cancel the delete.

If New was selected, the following window appears.  Note that if an Edit was selected, the current job updates SQL script appears in the window:

Note that the SQL script has an SQL Order By OrderNumber clause to ensure the data is imported in an orderly fashion.

The tab requires three entries:

1. Script Name The script name is a unique name used to identify the job updates script.  The name must be unique and can be up to 45 alphanumeric characters long.
2. Connection Name The connection string name is entered for the database you wish to connect to.  Valid connection strings are displayed in the drop down menu.
3. Job Updates SQL Script Enter the SQL script to extract the necessary job update information from the external database to add information to DMS. 
4. Click Save to save the new script.  Click Cancel to cancel the addition of the new script.

In order to help you build the SQL script DMS provides a button that adds the necessary information for you to add your custom information to.  Click on the button, DMS will ask you to verify that you want to add the required SQL fields to the script.  If you select yes, DMS adds the SQL script as in the example below:

To the above script you would add the parameters required to complete the SQL.  For example, for each value after the “as” enter the name of the column or calculated field from the source database.  You also need to add the table(s) to the “From” statement, any joins, and any required information to the “where” clause.  Note that the yellow line after the lines numbers indicates lines that have been edited.  Once the save button has been selected, the yellow line will disappear.

Once a script has been entered, you can test the script by using the button. This button will run the script and access the external database as in the example below:

Depending on the number of rows selected, the test may take a few minutes to execute.  After the test, press the close button to close the test window.

If an error occurs, such as a missing column of required information, the test will display the error as in:

Close the test window and make the changes to the script as required.