Create And Edit External Data Queries

• You will need an OLE DB connection string and user account credentials for the external data source. You might need to consult with the data source's system administrator to obtain this information.

• Users cannot add new entries to an external data source from the Meridian client applications. The data can only be edited by the data source's native application or by VBScript. To edit external data using VBScript, construct SQL INSERT or SQL UPDATE query statements and run them with the Vault.ExecSQL method.

• If the data source does not reside on the Meridian server, ensure that the account under which the AutoManager EDM Server service is run can access that location. If necessary, change the Accruent EDM Server service account.

  • If you use SQL Server, see Configure the SQL Server Account Used By Meridian.

  • If you use Oracle, see Configure the Oracle Account Used By Meridian.

• If SQL Server or Oracle is used for the database of the Meridian vault, do not attempt to store the tables used by these queries in the same database as the Meridian vault. Instead, create a new database in SQL Server or Oracle for the tables. Accruent does not support storing query tables in the same database as the Meridian vault.

To create an external data query:

1. In Configurator, expand Tables and Queries in the configuration tree to display the existing tables and queries.

2. On the Edit menu, select New Table or Query.

   The Select Data Source Type dialog box appears.

3. Select Query and click OK.

   A new query is added to the configuration tree and its property pages appear in the right pane.

4. On the General page, type a name for the query in Display Name.

   This name will not be visible to users. A default internal name is calculated in Name. Accept the default in most cases.

5. Click the Query page to define the query.

   The query's parameter options appear.

6. Type a valid OLE DB connection string in Connection string or click the Connection String hyperlink to build a connection string.

   The Data Link Properties dialog box appears. For assistance in creating a connection, click the Help button.

   Note:

   The user name and password typed in the Data Link Properties dialog are not shown in the Connection string text box for increased security.

7. Type the name of an existing table or view in the data source in From.

   A query may also be typed. To calculate the table name, view name, or query from existing vault properties, enable the VBScript option and click the Meridian Enterprise Script Editor button to build a VBScript expression. For information on using VBScript for configuration expressions including their limitations, see Configuration Expressions.

   Note:

   For more information on valid values for From, see Using the FROM Clause on the Microsoft Developer Network (MSDN) SQL Server Developer Center website.

8. Click the Test button to preview the results, which will appear in the data grid.

9. Click OK to save the query's definition.

   You may now create a VBScript function to retrieve data from the query.

To edit an existing query:

1. In Configurator, expand Tables and Queries in the configuration tree to display the existing tables and queries.

2. Select the query.

3. Click Edit.

4. Click the Query tab.

   The existing query parameters are shown.

5. If necessary, edit the existing value for Connection string or click the Connection String hyperlink to build a connection string. The Data Link Properties dialog box appears. For assistance in creating a connection, click the Help button.

6. If necessary, edit the existing value for From:

   • If the Use Enterprise Server lookup table option is selected, select the table from the list.

     This setting queries the external lookup list in Meridian Portal using the Cloud API for lookup lists.

   • If the VBScript option is enabled, click the Meridian Enterprise Script Editor button to edit the VBScript expression. For information on using VBScript for configuration expressions including their limitations, see Configuration Expressions.

   Note:

   For more information on valid values for From, click the From hyperlink, which will display reference information from the Microsoft Developer Network (MSDN) SQL Server Developer Center website.

7. Click the Test button to preview the results.

To delete a query:

1. In Configurator, expand Tables and Queries in the configuration tree to display the existing tables and queries.

2. Select the query.

3. On the Edit menu, select Delete.