OBIEE 11g Invalid Syntax Error When Connecting to SQL Server

I recently ran across this issue when I was connecting an OBIEE connection pool to a MS SQL database. I was receiving the following errors when I ran reports. Additionally when trying to update row count in the OBIEE Admin tool, an error dialog appears and the tool generates an error or crashes.

ODBC error state: 10103 code: 102 message: [DataDirect][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘OTHER_LOC_AMT’

OBIEE SQL Server invalid connection syntax error

There are a couple of things that need to be done to connect to a SQL Server database.

Step 1.

  • Open the repository (RPD) in OBIEE AdminTool.
  • Under the Physical Layer, open the properties of relevant SQL Server connection object.
  • Click on Features tab and remove the double-quote (“) under the “value” column in the IDENTIFIER_QUOTE_CHAR feature (modified value will show as below).

OBIEE SQL Server IDENTIFIER_QUOTE_CHAR feature

Step 2.

  • Open the repository (RPD) in OBIEE AdminTool.
  • Under the Physical Layer, open the properties of the SQL Server Connection Pool.
  • Check the box that says ‘Require Fully Qualified Names’

OBIEE SQL Server Connection Pool

After making the above changes save the repository and then Stop and restart the BI Servers. Retest the dashboards and the connection issues should be solved.

Source: https://support.oracle.com/epmos/faces/SearchDocDisplay?_afrLoop=234246843395840&_afrWindowMode=0&_adf.ctrl-state=w8kz20k48_4

Have more questions? We can help: