SYMPTOM:
When configuring Strategy Intelligence Server, after selecting the ODBC connection to the metadata, the following error message appears when trying to authenticate user credentials:
User authentication failed: Database error <>: Execute Query Failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. Characters found after end of SQL statement.
Through the ODBC specified in the ODBC Data Source Name, the wizard creates a SQL statement to log into the database selected using the credentials specified to connect to the metadata repository. The SQL generated, however, fails to execute against the database.
CAUSE:
The Configuration Wizard logs into the metadata and reads the information of the table DSSMDSYSPROP to gather the type of Relational Data Base System (RDBMS) used as metadata. This information can be found in the row where column NAME = RDBMS and column PROP_VAL = Database Name. If the metadata is in Access 97, the value in this row under column PROP_VAL should be ACCESS97. If the metadata is in SQL Server 2000, the correct value would be SQLSERVER2000.
The reason for the error message is that the wizard is unable to connect to the metadata because after reading this value, it creates the wrong type of SQL statement due to the mismatch between the value stored and the type of metadata.
ACTION:
In order to avoid this kind of problem, the 'Object Manager' provided by Strategy should be used to export the metadata from one type of RDBMS to another. To fix the problem, the value stored in the table DSSMDSYSPROP under the column PROP_VAL for the column NAME with value RDBMS as specified above should be changed.
NOTE: Simply changing the value in the table DSSMDSYSPROP might solve the error message while setting up Strategy Intelligence Server, but there may still be system instability while using Strategy.
Metadata Modification:
WARNING:
Manually editing values in the Strategy Metadata incorrectly may cause serious, project-wide problems that may make your project unusable. Since these are user-initiated changes, they are not covered by any Strategy warranty. Users are strongly encouraged to backup Metadata prior to any alteration.
