Ignoring Dynamic SQL When Comparing SQL

Dynamic SQL generates SQL statements that are partially created at the time of execution. Dynamic SQL may be generated differently in the base project and in the target project, so it can cause reports to be flagged as Not Matched even if the report SQL is otherwise identical.

You can configure Integrity Manager to ignore the dynamic SQL in its comparison. To do this, make changes in two places: a report's VLDB properties and in Integrity Manager in the Advanced Options dialog box. This dialog box can be accessed from the Select Processing Options page of the Integrity Manager Wizard.

To Ignore Dynamic SQL in SQL Comparison

1. Enclose the dynamic SQL in identifying SQL comments. Enter the comments in the VLDB properties Pre/Post statements.
   • For example, before each section of dynamic SQL, include a beginning comment line, such as:
     /* BEGIN DYNAMIC SQL */
   • At the end of each section of dynamic SQL, include an ending comment line, such as:
     /* END DYNAMIC SQL */

   For information on these statements, dynamic SQL syntax and parameters, and for steps on changing VLDB properties, see the System Administration Guide.
   

2. Create a comparative integrity test.
3. On the Select Processing Options page, select the SQL check box.
4. Click Advanced Options, select the SQL/MDX category.
5. In the Dynamic SQL Start field, input the text you entered in the VLDB properties to indicate the beginning of the dynamic SQL. For this example, type /* BEGIN DYNAMIC SQL */
6. In the End field, input the text you entered in the VLDB properties to indicate the end of the dynamic SQL. For this example, type /* END DYNAMIC SQL */
7. Click OK. When the test is executed, all SQL between the text in the Dynamic SQL Start and End fields are ignored in the comparison.

Related Topics

Advanced Options: SQL/MDX

Select Processing Options Page

Matching Equivalent SQL Strings

Tests that can be Performed in Integrity Manager