This knowledge article describes an issue where multiple errors occur when using a DSN-less Database Connection in Strategy Web 9.4.x and newer. The error message displayed within Strategy Web will usually have multiple errors within, the first reading Object with ID ‘xxx’ and type 31 (DB Connection)…”. Following that error is typically another, with additional details.
That is typically surfaced within Strategy Web encountered is the error that should be used to determine why the report is failing.
For example, when a Freeform SQL report is executed to import data from a DSN-less Database connection in Strategy Web, users may experience an error message similar to the following:
“Object with ID ‘xxx’ and type 31 (DB Connection) is not found in metadata. It may have been deleted.Error in accessing information from the external data source (Database Instance: (DataImport)[0], Error: Execute Query failed. %0d%0aError type: Odbc error. Odbc operation attempted: SQLExecDirect. [S1000:20: on HSTMT][Simba][bigQuery] (20) Query execution failed: Parse Error))”
When creating a DSN-less connection in Strategy Web, a Database Connection object is not made in the metadata for this Database Instance. However, since a DSN-less connection is stored inside a Database Instance object, which expects a Database Connection object, this error is thrown. Standard Database Instance objects in Strategy include references to Database connections, Database Logins, which is why such an error message is initially thrown by Strategy.
Typically, the “Object with ID ‘xxx’ and type 31 (DB Connection)…” error is not surfaced to end users, as this error is suppressed by Strategy for DSN-less DB connections. However, when another error occurs, such as syntax, network or other errors, the above error will be thrown, along with the second error describing the actual problem that is occurring. In the instance above, the following piece of the error message refers to the true error:
“Error: Execute Query failed. %0d%0aError type: Odbc error. Odbc operation attempted: SQLExecDirect. [S1000:20: on HSTMT][Simba][bigQuery] (20) Query execution failed: Parse Error))”
To determine the root cause of the error you are encountering for your DSN-less database connection, disregard the first error mentioned above. Focus on the second error within the message as the actual problem. In the example error above, fixing the SQL syntax addressed the problem.
