I am currently in the process of upgrading our Business Objects from XI 3.1 to BI 4.2, and have also migrated all the WebI reports across.
There are a number of WebI reports, which produce the following error message when refreshed: The following database error occurred: [Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect syntax near , For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Portal
These same reports work fine on XI3.1, but not on BI 4.2.
When I refresh the queries of these reports individually through the query panel they do refresh fine. Please note, that two out of the three queries are not returning any records. Once I select Apply changes and Close, then there are no rows returned for any queries, and all the report pages have therefore no records.
Any help on this problem will be greatly appreciated, because Ive not been able to get to the bottom of this error message for some time now.
Yes, the reports in question do have prompts. The queries do actually return records when different dates are selected while they are refreshed individually within the query panel.
After the Apply changes and Close option is chosen (when leaving the query panel) they do return rows, but the reports do not refresh.
When I try to refresh the reports I am getting the above mentioned error message.
How are your user preferences set for Webi, this can affect date input formats?
When you say user preferences; are you referring to the access levels, where the user belongs to? I have been getting this error message, when logged in as the administrator.
Does this happen with both Java, Interactive viewer and scheduler?
Yes, this error happens when the Web Intelligence modify options have been set to HTML, as well as, Applet. Im also getting this error message when Im trying to schedule this report.
Was there any changes to your SQL server prm file from version 3, you have not applied to the new 4 install?"
I have had a brief look at the sqlsrv.prm files (MS SQL database is being used by the universe) on both BOXI and BI, but couldnt find any particular issues.
How are the prompts defined, is it a certain prompt causing an issue?
I presume that the prompts are working fine, because the queries are running fine through the query panel, while the prompts are being used.
The user preferences are the Webi user preferences, where you can select the locales and time zone.
I have a feeling this could be to do with the wrong date format being passed to the database.
One other thing is if you schedule it, let it fail, then you edit the instance you may see something wrong in the SQL code that has actually being submitted (check the dates).
The next thing I would be doing is to try and build the same query from scratch and see what happens then.