asked on

Invalid Identifier for SSRS query to an Oracle database View (not Table) with a ODBC Connection

I am writing a SSRS report that pulls data from a Oracle Server we have. I have an ODBC connection built. The vendor who supports our Oracle Server has some tables available, however most of our access is through Views that were built.

I created the Data Source with no problem. The problem I have is with the Data sets. It seems that data sets I create that come from Tables work just fine. However when I create a data set that gets data from any of the Views display an "Invalid Identifier" when I run the query.

I've search the internet and I'm not finding much about that error for Oracle ODBC connections to a View instead of a table (again, queries to Oracle Tables do work).

Is there something else I need to do for connections to Views?

Kelvin Sparks

The first things I would check

Have you got the View Name with the correct capitalisation (generally Oracle is Case sensitive) - and more often than not you need an object name in upper case.
Have the views been given a select grant (and the user that you're connecting with is using that grant - is possible that the user inherits the grant of a role that it belongs to).

Kelvin

slightwv (䄆 Netminder)

>>Have you got the View Name with the correct capitalisation (generally Oracle is Case sensitive)

No it isn't. You "can" make it like that using double quotes but don't do that...

>>display an "Invalid Identifier" when I run the query

Might just be a messed up view. Have you confirmed the views are correct?

Can you run the query through SQL Developer or sqlplus when connecting directly to the Oracle database?

Joseph LaFlex

ASKER

Thanks for the input so far.
The view name appears to be correct. The table / view names are all CAPS which is what I entered. The Column names are mix Cap.
I did just try something different in that I just used a SELECT * instead of listing specific columns. That worked.
So then I put double quotes around each of the column names and that worked as well.
You stated "don't do that " in regards to double quotes. Why not? and what should I do?

Thanks

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Joseph LaFlex

ASKER

Okay, it's a pain in the a_ _ but I'll do it.

Thanks
Joe

slightwv (䄆 Netminder)

>>it's a pain in the a_ _

Yep. That's why you don't do it.....