Error trying to modify SQL in Data Provider

[Former Member]

I am attempting to modify the generated SQL in a DESKI 3.1 report and save with the Do Not Generate box checked.

If I attempt to modify the generated SQL and check the Do Not Generate box when I click on OK or Verify SQL button I get the following error:

Exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.State: 42000

Any ideas as to what could be the problem?

Thanks.

[Former Member]

Hi Emerson,

First do check integrity, then please go through the parameters and try to change the values of some parameters like ANSI92, BLOB COMPARISON and mainly BEGIN_SQL. I have just changed it and finally worked.

Hope it helps.

Regards,
Santhosh

[Former Member]

Hi Santosh,

Currently the values for the BLOB COMPARISON=No and mainly BEGIN_SQL= Nothing, you advised us to change the values, But can you please clarify,  what are the new values for these parameters

,

Thanks

Venu

[Former Member]

Hi,

I am also getting the same error, I have created a universe and also created a measure object using BO XI designer, there I got an error message when parsing the measure object. The error thrown is Parse failed:Exception: DBD, [Microsoft] [ODBC Microsoft Access Drier] Syntax error (missing operator) in query expression 'count(distinct) Client.client_ID)'. state:42000.

I am using MS Access database,

Please advise how solve this issue.

Thanks

Venu

[Former Member]

Found a work around not really answering question.

[Former Member]

Hi Emerson,

Following information might be helpful for you to resolve the issue.

Here is a general rule to follow when building your own case/when/then/end statements: you must prefix all of your column references with the table name. Consider this scenario:

Example 1:

case when col_A='A' then 0 when col_B='A' then 1 else null end

Example 2:

case when Table1.col_A='A' then 0 when Table2.col_B='A' then 1 else null end

Notice that example 2 is the correct form. I would suggest going back through your SQL statement and ensure that you've followed this convention. Though you may have already written a perfectly good (i.e. it syntaxes) SQL statement that you can drop into your favorite SQL tool and it works, you must follow Designer's rules when working with the universes.

Would request you to look into the SQL designed because some of the functions are not recognized by the ODBC driver API.

Regards,

Sarbhjeet Kaur

[Former Member]

The syntax of the SQL is correct and the ODBC driver being used did not have this problem with the SQL in the older version of Business Objects as the report did not change.

I have resolved my problem with the use of a derived table in the universe.

Not something I want to have to do with all the legacy reports that require modification in the coming year.

[Former Member]

Hi Emerson,

There are couple of reasons when users may get this type of error.

Cause 1:

You have a universe with a connection to Microsoft SQL Server 2005. If you modify some of the characters for the SQL in the object definition from lower case to upper case (or vice-versa), the following error message appears:

"Error: Parse Failed: Exception, DBD [Microsoft][ODBC SQL Server

Driver][SQL Server]Statement(s) could not be prepared. State: 42000."

Cause

The SQL statement is case sensitive in Business Objects.

Follow these steps to Resolve the Problem:

1. Test the connection in your universe and click Details.

2. From Details, copy and run the path for the PRM file used by your connection which will open the relevant PRM file in Notepad.

3. Modify the following parameter (or add to the PRM file if it

does not exist) as follows:

4. <Parameter Name="CASE_SENSITIVE">N</Parameter>

5. Save the PRM file.

6. Close and relaunch Designer.

Cause 2:

When building a universe in Designer, you are unable to establish an outer join to or from a view to a table. The following error message appears:

"Exception DBD [Microsoft][ODBC SQL Server Driver][SQL Server]Statements

could not be prepared 42000"

This occurs with BusinessObjects XI Release 2 Service Pack 2 installed.

Cause

The SQL generated must be in the ANSI92 format.

Perform the following steps:

1. In Designer, click Menu > File > Parameters.

2. Click the parameter tab.

3. Change the ANSI92 parameter value to u201CYesu201D and Default value to u201CNOu201D

Perfrom Integrity Check For Errors and Export the Universe.

I Hope this Helps...

Thanks...

Pratik

[Former Member]

I made the change you suggest in cause #1.

But I still receive the error.

Just make sure I am clear, I am receiving this error while in DESKI and not making modifications to the Universe.

[Former Member]

I also just tried suggestion #2 as well. Still the same error.

[Former Member]

Hello,I have a similar problem.

Create JOIN I get an error message:"Error: Parse Failed: Exception, DBD Microsoft[ODBC SQL ServerDriver]SQL ServerStatement(s) could not be prepared. State: 42000."

I work with a data base: sql server 2008

I have performed the same actions noted in the comment of mister Pratik Basapure The problem is not resolved.http://

Can you help me please?

[Former Member]

Hi Pratik,

Yes, its working but some times, as per my universe i must need to change Begin SQL and some other parameters values.

Thank you once again.!

Regards,
Santhosh