Microsoft KB Archive/108439: Difference between revisions
(importing KB archive) |
m (Text replacement - ">" to ">") |
||
(2 intermediate revisions by the same user not shown) | |||
Line 12: | Line 12: | ||
<div id="TitleRow"> | <div id="TitleRow"> | ||
= <span id="KB108439"></span>ACC: | = <span id="KB108439"></span>ACC: "ORA-00904: Invalid Column Name" Error Message in Query = | ||
Line 77: | Line 77: | ||
* In Microsoft Access, attach directly to the table on which the synonym is based. | * In Microsoft Access, attach directly to the table on which the synonym is based. | ||
* In Oracle, assign the owner of the synonym permissions greater than read-only. | * In Oracle, assign the owner of the synonym permissions greater than read-only. | ||
* In Microsoft Access, remove the | * In Microsoft Access, remove the "order by" clause from the query. | ||
Line 89: | Line 89: | ||
<div class="errormessage"> | <div class="errormessage"> | ||
SELECT * FROM | SELECT * FROM <owner>.<synonym_name><br /> | ||
ORDER BY | ORDER BY <owner>.<synonym_name>.<column_name> | ||
</div> | </div> | ||
<br /> | <br /> | ||
Note that the same errors will occur if this command is issued in SQL*DBA or SQL*PLUS, which are Oracle's interface tools. If you remove the ORDER BY clause, the statement will run. The statement will run with the ORDER BY clause if you remove the | Note that the same errors will occur if this command is issued in SQL*DBA or SQL*PLUS, which are Oracle's interface tools. If you remove the ORDER BY clause, the statement will run. The statement will run with the ORDER BY clause if you remove the <owner>. clause instead. The statement will run with all the clauses included if you increase the owner's permissions on the table on which the query is based.<br /> | ||
<br /> | <br /> | ||
Latest revision as of 17:45, 20 July 2020
Article ID: 108439
Article Last Modified on 1/26/2005
APPLIES TO
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
This article was previously published under Q108439
SYMPTOMS
If you have a query based on an attached Oracle synonym that has a sort on a column name, and the owner of the synonym has read-only permissions on the table on which the synonym is based, you will receive the error messages:
-and-
CAUSE
This error occurs because the owner of the synonym has read-only permissions on the Oracle table on which the synonym is based.
RESOLUTION
There are four ways to correct this problem:
- Create a Public synonym in Oracle.
- In Microsoft Access, attach directly to the table on which the synonym is based.
- In Oracle, assign the owner of the synonym permissions greater than read-only.
- In Microsoft Access, remove the "order by" clause from the query.
MORE INFORMATION
The following is an example of an SQL statement that will cause this error:
Note that the same errors will occur if this command is issued in SQL*DBA or SQL*PLUS, which are Oracle's interface tools. If you remove the ORDER BY clause, the statement will run. The statement will run with the ORDER BY clause if you remove the <owner>. clause instead. The statement will run with all the clauses included if you increase the owner's permissions on the table on which the query is based.
Steps to Reproduce Behavior
- In Oracle, create a table. Assign all permissions on the table to user A, and read-only permissions to user B.
- Log into Oracle as user A. Create synonym A based on the new table.
- Log into Oracle as user B. Create synonym B based on the new table.
- Start Microsoft Access. Attach synonym A with user A's login account. Create a query based on synonym A with a sort on one of the columns.
- Run the query. Note that the query runs correctly.
- Attach synonym B with user B's login account. Create a query based on synonym B with a sort on one of the columns.
- Run the query. The error messages stated above will occur.
NOTE: You can reproduce this behavior in Oracle's SQL*DBA as well. To do so, connect as user A and issue the following command:
SELECT * FROM userA.synonymA ORDER BY userA.synonymA.column1;
Note that the command runs correctly. Next, connect as user B and issue the following command:
SELECT * FROM userB.synonymB ORDER BY userB.synonymB.column1;
An error message will occur.
Additional query words: adk
Keywords: kberrmsg kbinterop kbprb KB108439