SYMPTOM:
When executing a report in Strategy against a warehouse residing on a Microsoft SQL Server database, the following error occurs:
Error: SQL Generation Complete
QueryEngine encountered error: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [37000:170: on HSTMT] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'.
Error in Process method of Component: QueryEngineServer, Project The Onimusha Project, Job 616, Error Code= -2147212544.
The DSSError logs report the following:
2007-08-24 19:07:16.132-05:00 [HOST:WAS-MSTR][PID:3064][THR:3868][Database Classes][Error] Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [37000:170: on HSTMT] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'. Connection String: DSN=xxxx;UID=xxxx;APP=Strategy 8.0 ® Common Files;WSID=xxxx;DATABASE=xxxx;. SQL Statement: select CHOOSE a11.YEAR_ID YEAR_ID,
sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) PROFIT
from YR_CATEGORY_SLS a11
where a11.YEAR_ID in (2005)
group by a11.YEAR_ID.
2007-08-24 19:07:16.163-05:00 [HOST:WAS-MSTR][PID:3064][THR:3868][Query Engine][Error][0x80042300] QueryEngine encountered error: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [37000:170: on HSTMT] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'.
When the report SQL is checked it appears as normal, sometimes an empty SQL pass is seen, and running the SQL in ODBC Test Tool results in correct results.
CAUSE:
This error is caused by a user defined SQL statement containing an empty line.
In this example, the report had an empty Post SQL statement:
Blank entry in 'Report Post Statement 1'
ACTION:
Check all VLDB properties for SQL statements which could contain a blank line. This can occur at the following three levels:
