Create a New ODBC Data Source

This defines a DSN for the SQL Server Express ODBC driver. The RPS secondary JADE database will use this to connect to the SQL Server database.

  1. Start the ODBC Data Source Administrator.

    1. Bring up the Data Sources (ODBC) program in the Windows Administrative Tools.

    2. Select the User DSN sheet.

    This opens the dialog to allow a new ODBC data source to be added to your machine.

  2. Specify the data source.

    1. Click Add….

    2. Select the correct SQL Native Client driver version for your version of SQL Server.

    3. Click Finish.

    The data source will be SQL Server. This is the only RDB supported by RPS.

  3. Specify SQL Server details.

    1. Enter Name of SQLServerODBC.

    2. Enter Description of SQL Server.

    3. Enter Server of machine-name\SQLExpress.

    4. Click Next >.

    The name must match the name entered in the Default Connection String in the "Define RPS Mapping" section. If SQL Server is running locally, the server name must be specified without leading backslashes (\\). Include the backslashes if a DNS name is used.

  4. Specify the login details.

    1. Leave the default value of With Integrated Windows authentication.

    2. Click Next >.

    This defines the login details used when connecting to SQL Server. The Windows login id is used. When Next > is clicked, an attempt is made to connect to SQL Server. If this is unsuccessful, an error is displayed and the next dialog is not shown.

    If an error occurs, check that the server name has been entered correctly in the first configuration dialog and that SQL Server is installed and running. If you are still encountering problems, run SQL Server Management Studio. Note the Server Name on the logon window, as this should be the same as the server name entered in step 3 of this instruction. Check that you can log on to Management Studio using Windows Authentication. If this logon fails, this problem must be resolved before you can connect using the ODBC data source.

  5. Complete the DSN set up to meet your requirements. In general, default values should be sufficient; for example, the third sheet of the System DSN wizard has the Use ANSI quoted identifiers check box checked by default, which enables RPS to interrogate the RPS ODBC data source that it uses to determine if to quote SQL identifiers (that is, table, column, and procedure names).

  6. Finish defining the data source.

    1. Click Next >.

    2. Click Finish.

  7. Test the data source.

    • Click Test Data Source.

    This attempts to connect to SQL Server using the specified configuration. If this fails, check that the server name has been entered correctly in the first configuration dialog and that SQL Server is installed and running. If the test is successful, an SQL Server ODBC Data Source Test dialog is displayed.

  8. Exit from the ODBC Data Source Administrator.

SQLServerODBC is now defined as an ODBC data source.