KB233418: "Prepared statement 'S04000000' does not exist" error occurs during Enterprise Manager Warehouse initialization when using a Sybase ASE 15.5 database with parameterized queries enabled.

SUMMARY
This document outlines a database error message that is received when initializing the Enterprise Manager Warehouse in a Sybase ASE 15.5 database that has been configured to use parameterized queries.
SYMPTOM
In Strategy 9.x and 10.x, the following error is received when initializing the Enterprise Manager Warehouse on a Sybase ASE 15.5 database that has been configured to use parameterized queries.
Note: The use of parameterized queries is configured on the DSN level and not on Strategy Enterprise Manager. 
 

2/25/2015 3:13:08 PM CREATE TABLE DT_DAY (DAY_DATE date, DAY_ID int, DAY_SDESC varchar(20), DAY_LDESC varchar (30), WEEKDAY_ID smallint, WEEKDAY_LDESC varchar(20), WEEKDAY_SDESC varchar (5), MONTH_ID int, QUARTER_ID, smallint, YEAR_ID smallint, WEEKOFYEAR_ID int, WEEK_ID int)
2/25/2015 3:13:08 PM Transferring Table DT_DAY
2/25/2015 3:13:08 PM OdbcParameterizedQuery::ExecQuery failed.

Error type: Odbc error. Odbc operation attempted: SQLExecute. [HY000:590: on SQLHANDLE] [Strategy] [ODBC Sybase Wire Protocol Driver] [SQL Server] Dynamic SQL Error - Prepared statement 'S04000000' does not exist
INSERT INTO DT_DAY VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2/25/2015 3:13:08 PM OdbcParameterizedQuery::ExecQuery failed.

Error type: Odbc error. Odbc operation attempted: SQLExecute. [HY000:590: on SQLHANDLE] [Strategy] [ODBC Sybase Wire Protocol Driver] [SQL Server] Dynamic SQL Error - Prepared statement 'S04000000' does not exist
INSERT INTO DT_DAY VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2/25/2015 3:13:08 PM OdbcParameterizedQuery::ExecQuery failed.

Error type: Odbc error. Odbc operation attempted: SQLExecute. [HY000:590: on SQLHANDLE] [Strategy] [ODBC Sybase Wire Protocol Driver] [SQL Server] Dynamic SQL Error - Prepared statement 'S04000000' does not exist
INSERT INTO DT_DAY VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

 
Additional information on parameterized queries can be found in the following Strategy Knowledge Base documents:

• KB7242 : How to get the "Use Parameterized Queries" setting to work with Strategy ODBC Driver for Sybase Wire Protocol
• KB5544: What is a parameterized query in Strategy Query Engine 9.x and 10.x?

 
CAUSE
Enterprise Manager initialization process is not optimized for the usage of parameterized queries. This is per design.
 
The Enterprise Manager Initialization process will copy the Enterprise Manager Project and Enterprise Manager Warehouse tables from the out of the box access databases provided to the selected destinations. None of this processes requires the use of parameterized queries and will not really benefit from doing so.
 
ACTION
Turn off the usage of the parameterized queries in the ODBC DSN used for Strategy Enterprise Manager initialization process. If the ODBC DSN will be used later for reporting, datamarting or any other activity that could benefit from parameterized queries, the functionality to use parameterized queries can be enabled again. Refer to following Strategy Knowledge Base Documents for further details:
 
KB30290: How to optimize performance of database inserts for Multisource and Datamart reporting in Strategy 9.x and 10.x
KB7242: How to get the "Use Parameterized Queries" setting to work with Strategy ODBC Driver for Sybase Wire Protocol
 
ADDITIONAL INFORMATION:
ODBC Sybase ASE Driver allows to tune up the way the SQL query preparation works. This is done under the "Performance" tab by changing the "Prepare Method" setting.
 
As per the Sybase ASE Documentation, the "Prepare Method" setting determines whether stored procedures are created on the server for calls to SQLPrepare. The available options are described as follows:
 

• When set to 0, stored procedures are created for every call to SQLPrepare. This setting can result in decreased performance when processing statements that do not contain parameters.
• When set to 1 (the initial default), the driver creates stored procedures only if the statement contains parameters. Otherwise, the statement is cached and run directly at the time of SQLExecute.
• When set to 2, stored procedures are never created. The driver caches the statement, executes it directly at the time of SQLExecute, and reports any syntax or similar errors at the time of SQLExecute.
• When set to 3, stored procedures are never created. This is identical to value 2 except that any syntax or similar errors are returned at the time of SQLPrepare instead of SQLExecute. Use this setting only if you must have syntax errors reported at the time of SQLPrepare.

 
If the settings to enable parameterized queries referenced in Strategy Knowledge Base Document KB7242 cannot be changed, an alternative workaround is to temporarily set the "Prepare Method" to use "2-Full". Once the Enterprise Manager initialization process has been completed, "Prepare Method" can be set back to the default setting.