I currently link a table to an Oracle db. Anytime I open a query
based off it, I have to input username & password. Is there a way to
do this automatically?
I'd prefer to use linked tables, but I've read a pass-through query
can pass the passwords automatically. The directions seem
straightforward, but I can't get one to work. The select query from
the ODBC linked table looks like:
SELECT OSCAR_TBLSALESMAN.SALESMAN, OSCAR_TBLSALESMAN.NAME,
OSCAR_TBLSALESMAN.INITIALS, OSCAR_TBLSALESMAN.TERRITORY,
OSCAR_TBLSALESMAN.SALESMANTYPE, OSCAR_TBLSALESMAN.SALESFORCE,
OSCAR_TBLSALESMAN.SALESMANRSM, OSCAR_TBLSALESMAN.SALESMANRVP,
OSCAR_TBLSALESMAN.LOCATION, OSCAR_TBLSALESMAN.CUSTOMER,
OSCAR_TBLSALESMAN.TRANSMITTINGLOCATION
FROM OSCAR_TBLSALESMAN;
When I convert it to a pass-through I get the error message:
ODBC – call failed.
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00911:invalid character
(#911)
I know the underscore is invalid for Oracle so I replaced it with a
period
SELECT OSCAR.TBLSALESMAN.SALESMAN, OSCAR.TBLSALESMAN.NAME,
OSCAR.TBLSALESMAN.INITIALS, OSCAR.TBLSALESMAN.TERRITORY,
OSCAR.TBLSALESMAN.SALESMANTYPE, OSCAR.TBLSALESMAN.SALESFORCE,
OSCAR.TBLSALESMAN.SALESMANRSM, OSCAR.TBLSALESMAN.SALESMANRVP,
OSCAR.TBLSALESMAN.LOCATION, OSCAR.TBLSALESMAN.CUSTOMER,
OSCAR.TBLSALESMAN.TRANSMITTINGLOCATION
FROM OSCAR.TBLSALESMAN;
I get the same error message, so I tried taking out the Oscar section
SELECT TBLSALESMAN.SALESMAN, TBLSALESMAN.NAME, TBLSALESMAN.INITIALS,
TBLSALESMAN.TERRITORY, TBLSALESMAN.SALESMANTYPE,
TBLSALESMAN.SALESFORCE, TBLSALESMAN.SALESMANRSM,
TBLSALESMAN.SALESMANRVP, TBLSALESMAN.LOCATION, TBLSALESMAN.CUSTOMER,
TBLSALESMAN.TRANSMITTINGLOCATION
FROM OSCAR.TBLSALESMAN;
And
SELECT TBLSALESMAN.SALESMAN, TBLSALESMAN.NAME, TBLSALESMAN.INITIALS,
TBLSALESMAN.TERRITORY, TBLSALESMAN.SALESMANTYPE,
TBLSALESMAN.SALESFORCE, TBLSALESMAN.SALESMANRSM,
TBLSALESMAN.SALESMANRVP, TBLSALESMAN.LOCATION, TBLSALESMAN.CUSTOMER,
TBLSALESMAN.TRANSMITTINGLOCATION
FROM TBLSALESMAN;
Then I tried removing the tablename portion:
SELECT SALESMAN, NAME, INITIALS, TERRITORY, SALESMANTYPE, SALESFORCE,
SALESMANRSM, SALESMANRVP, LOCATION, CUSTOMER, TRANSMITTINGLOCATION
FROM TBLSALESMAN;
Any help?
Thanks