You must define the connection string to the database you want to communicate to before you define the SQL.  Note that connection strings that have been entered into DMS for access to other databases are encrypted on the DMS table for security purposes.  To define a connection string, select Import | Import settings | SQL Connection Definitions.  

A window will appear with the current list of defined connection strings.  The example below shows one connection string named “DMS SQL Connection String “defined:

Valid commands are:

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

If Edit was selected the following window appears with the current settings displayed.  If New was selected, the same window appears without any information displayed.

The external database connection definition requires three entries:

1. Connection Name The connection name is a unique name used to identify the connection string.  The name can be up to 45 alphanumeric characters long.
2. Database Type  The database type is the type of database to connect to and can be either SQL Server or Oracle.
3. Connection String Definition The connection string is entered for the database you wish to connect to.
4. Click Save to save the new connection string.  Click Cancel to cancel the addition of the new connection string.

Example connection strings for the database you want to communicate with can be found on the internet.  The connection string required will need to be provided by your IT department.  To give you an idea of what a connection string looks like, the following is a connection string for Microsoft SQL Server to connect to a database named DMS on the server named DAVE-DEV\SQLEXPRESS:

Provider=SQLOLEDB;

Integrated Security=SSPI;

Persist Security Info=False;

Initial Catalog=DMS;

Data Source=DAVE-DEV\SQLEXPRESS;

Use Procedure for Prepare=1;

Auto Translate=True;

Packet Size=4096;

Workstation ID=GATEWAY;

Use Encryption for Data=False;

Tag with column collation when possible=False

The above connection string uses Windows authentication.  SSPI means "Security Support Provider Interface".  The SSPI allows an application to use any of the available security packages on a system without changing the interface to use security services. The SSPI does not establish logon credentials because that is generally a privileged operation handled by the operating system.  SSPI is equivalent to True and should be used instead of True.

To use a SQL username and password, the string would appear as:

Provider=SQLOLEDB;

User ID=My_Username;

Password=My_Password;

Initial Catalog=DMS;

Data Source=DAVE-DEV\SQLEXPRESS;

In the above connection string, note that connection string is much shorter relying on defaults for security settings.

An example of an Oracle connection string is as follows:

Provider=MSDAORA.1;

Password=dms;

User ID=system;

Data Source=Dave-Dev;

Persist Security Info=True