Double quotes around derived table name in the universe

[Former Member]

Hi Gurus,

I have used a derived table in a universe create using Information Designer Tool(UNX). When i run the report using the thick client, Web Intelligence Tool, it works fine.

However when i run the same report through the thin client, Launch pad, i get an error related to ODBC connection (i have forgotten the error message, will update the post with error message, tomorrow morning)

I am using SQL Server 2008 as the database which gets connected to the Universe using an ODBC DSN, SQL Server Native Client 10.0

When i checked the query, it has the double quotes around Derived table name. Could this be causing a problem?

While setting up the ODBC connection, i saw a setting as "Use ANSI quoted identifier" could this be of help?

Appreciate any ideas to discuss and understand the problem.

Thanks,

NagaVaibhav

[Former Member · Accepted solution]

Here is the snapshot of the error i see.

[Former Member · Accepted solution]

Hello Vaibhav,

Is your environment AIX?

I found a relevant KBA: 1718937

http://service.sap.com/sap/support/notes/1718937

Hope it helps.

Regards,

Madhumitha

[Former Member · Accepted solution]

Hi Madhumitha,

The BO Server is on a Windows machine.

I cannot access the notes. Could you please share the details about the note.

Regards

Nagavaibhav

[Former Member · Accepted solution]

Hello Vaibhav,

The note is specific to AIX environment. But you can still follow the workaround.

Following is the content of the note:

Symptom

• Database error: SAP Business Objects ODBC SQL Server Native Wire Protocol driver Microsoft SQL Server Incorrect syntax near '<table name>'.. (IES 10901) (WIS 10901)
• Creating Refreshing Web Intelligence Report gives error WIS 10901 and IES 10901
• Refreshing existing Web Intelligence Report gives error IES 10901 and WIS 10901

Environment

• AIX
• SAP BusinessObjects Business Intelligence Platform 4.0
• Web Intelligence
• SQL Server
• UNX universe

Reproducing the Issue

1. Configure SQL server ODBC connection using the Data Direct ODBC drivers for MSSQL provided by SAP Business Objects.
   1. Stop all business Objects servers ./stopservers
   2. Add the following environment variables
      • export ODBCINI=<SAP BO installation directory>/sap_bobj/enterprise_xi40/odbc.ini
      • export LIBPATH=$LIBPATH:/<SAP BO installation directory>/sap_bobj/enterprise_xi40/aix_rs6000_x64/odbc/lib
      • export LIBPATH32=$LIBPATH32:/<SAP BO installation directory>/sap_bobj/enterprise_xi40/aix_rs6000/odbc/lib
   3. Modify the odbc.ini file with the required parameters (minimum parameters below for unicode support)
      • Unicode_SQL2008_ODBC=DataDirect 6.0 SQL Server Native Wire Protocol
      • [Unicode_SQL2008_ODBC]
        Driver=/mnt/home3/i049568/dewdfxap00016/bi403/sap_bobj/enterprise_xi40/aix_rs6000_64/odbc/lib/CRsqls24.so
        Description=DataDirect 6.0 SQL Server Native Wire Protocol
        Database=<Database name>
        GSSClient=native
        HostName=<Ip address of the server
        LoginTimeout=15
        LogonID=
        Password=
        PortNumber=<port number>
        stmt=SET CHARACTER SET utf8
   4. Start Business Objects Servers (./startservers)
2. Create a UNX universe with Same name connection name as the parameter for the odbc.ini file (in my example Unicode_SQL2008_ODBC).
3. Publish the UNX to the repository
4. Create a Webi report based on the UNX Universe
5. Add some objects and Refresh the report
6. The Error "Database error: [SAP Business Objects][ODBC SQL Server Native Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near 'UNICODE_DATA'.. (IES 10901) (WIS 10901)" is generated.
7. Check the SQL in the query and notice that column and field names are surrounded by double quotes (").
   • Example of statement with the problem
     • SELECT dbo."UNICODE_DATA"."LANGUAGE" FROM dbo."UNICODE_DATA"

CauseThis error is specific on AIX machine. The Double quotes are not recognized.

Resolution

1. Import the UNX universe in IDT
2. Edit the Data Foundation
3. Select all the tables in the Data Foundation from the server
4. Right click on one of the tables
5. Select ‘Set Case To’> ‘Lower Case (Table and Columns)’
6. Save the Data Foundation
7. Save the Universe
8. Publish the Universe to the Repository

Note: Existing reports will need to be opened and the SQL will need to be re-validated. Edit the SQL and change to custom SQL and back to Default.

Regards,

Madhumitha

[Former Member · Accepted solution]

Thanks Madhumitha, This solution worked fine. The note although says it is for AIX environment, could it be changed to Unix too, since this works fine for Unix BO Server too

From what i could understand, it is more of the Universe Issue. I'm not sure why it has to be OS Specific Note

[Former Member · Accepted solution]

Hello Vaibhav,

Am glad that the solution works fine for you

Regards,

Madhumitha

[sachin_v_joshi]

We are having similar issue but our db is oracle and for the derived table , webi reports are failing .. Any pointers...

Have posted new thread bt there is no response .. http://scn.sap.com/thread/3584190

[Former Member]

Hi All,

This issue is back to haunt me.

In the previous universe created, table names were in upper case, which were set to lower case to resolve the issue.

A new universe i am creating, uses tables, which has upper case column names like "FUEL", "M01" etc.

When i create objects using these columns, the column names get appended with double quotes. After manually removing by going to SQL editor, the double quotes reappear.

I could not find any option in IDT which can set column names to lower case.

Due to the double quotes, i am facing the same error as posted earlier.

Please let me know if you guys have faced similar issue.

Regards

Vaibhav

[Henry_Banks]

Hi,

i know that for some RDBMS in the associated .prm file there is a setting called :

<Parameter Name="IDENTIFIER_DELIMITER">"</Parameter>   -  you may need to maintain the double quote there.

(similar to note 1466590 - How to avoid the error "Exception: DBD, [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token ` was not valid" when using derived table with ODBC connection to DB2 iseries? )

it also makes a mention that tables benefit from being in lower case.

Regards,

H

[Former Member]

For the moment we have renamed the column names in the view definitions. Now universe objects do not have double quotes.

As you have mentioned about the parameter file, I should explore this option. Let us see if this resolves the issue forever. Thanks Henry.

Regards

Naga

[tienie_nothnagel]

Thanks Henry

Your reference to note 1466590 worked 100%

[Former Member]

The double quotesin the derived table names is not an issue. Please check if the syntax is correct for any @prompts that has been used. Validate the sql. Otherwise copy the sql and run it in the backend and check for any syntax errors.

[Former Member]

Thanks Santhosh. In my case the above solution provided by Madhumitha worked.

[Former Member]

Hi NagaVaibhav,

In Universe the derived table name always referred within "Double Quotes" only and in my opnion the universe is fine. Also it is working in thick client.

Please check the universe connection in BOE server for this universe connection. Also if the error code is 10901 it is an error related to universe connection not with derived table.

Regards,

Kamal.