SYMPTOM:
'SQLSetConnectAttr failed' errors appear in the DSSErrors.log and reports fail in Strategy Intelligence Server even though the Database Connection Monitor still shows a connection.
CAUSE:
When the database connection is broken from the database side, Strategy does not keep checking the state of its database connection. So cached connections remain in the database connection monitor, until the associated M8MulPrc_32 process closes. Cutting the connection from the database side does not cause M8MulPrc_32 to shut down immediately.
ACTION:
If the system is attended, the administrator can go to the Database Connection Monitor in Strategy Desktop, right-click the cached connection and choose 'Disconnect' as shown below to kill the cached connection from the Strategy end and the next report executed should re-establish the database connection automatically.
If the system is unattended, the administrator should avoid caching database connection or set the appropriate values for cached connections by adjusting the Connection lifetime and Connection Idle Timeout settings or implement the workaround below. Refer to the following Strategy Knowledge Base technical note for more information:
KB5598: What are the 'Connection lifetime' and 'Connection Idle Timeout' options under the Database Connection Caching section in Strategy Intelligence Server?
WORKAROUND:
Add a dummy report pre-statement (something that does nothing to affect the query results) and this should cause Strategy Intelligence Server to drop the database connection after the report execution is complete. The pre-statement can be added to the database instance's VLDB properties so that it will apply to all reports, as shown below:
