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’
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).
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’
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.
Have more questions? We can help:
- Contact us using the form here, or call us at 952.947.9300.
- Drowning in data but parched for predictive insights? Arrange a free half-day analytics health check.
- Talk to our experts face to face at one of our upcoming industry events.