Microsoft ODBC driver for Oracle ORA-00942: Table or view does not exist issue

I have a SQL 2005 job that runs some script to pull info from Oracle to SQL.   The script is unchanged and executes once daily in the AM, however started receiving the error message on 2/19/2015.  I just started with this company and am going through all the servers, DB's, etc...  

I have verified that the connection criteria is working.   The other possible issues I have found could be can't find the table or view, or possibly a schema definition in the script?  I have included the  error message below along with the the script from the first step (and error is occuring on first step.  

Executed as user: DOMAIN\SERVER$. Error Code: 0  Error Source= DBServer.Retrieve  Error Description: [Microsoft][ODBC driver for Oracle][Oracle]ORA-00942: table or view does not exist    Error on Line 9.  The step failed.


Dim oCOM
Dim sSrcConn
Dim sDestConn

sSrcConn = "DRIVER={Microsoft ODBC For Oracle};SERVER=Alpha.Omega;UID=abcdefg;PWD=12345678"
sDestConn = "Provider=SQLOLEDB;Server=SERVER01;Database=Stat;UID=;Trusted_Connection=yes"
Set oCOM = CreateObject("StatEmp.Emp")

oCOM.LoadCatalogData sSrcConn, sDestConn
Set oCOM = Nothing
Set sSrcConn= Nothing
Set sDestConn= Nothing
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicIT ArchitectCommented:

can you confirm that this user has the appropriate rights? Maybe someone revoked the right or removed the synonym?
Walter RitzelSenior Software EngineerCommented:
Can you share the code that does the Select statement that is failing? But this seems to be permission issue on Oracle database.
slightwv (䄆 Netminder) Commented:
Fist:  Stop using the Microsoft drivers and use the Oracle native drivers.

The Microsoft drivers have been deprecated for some time now.

I would suggest the Oracle Instant Client.  You'll need two downloads:

Instant Client Package - Basic
Instant Client Package - ODBC

You'll need to manually create the network/admin folder and copy a good sqlnet.ora and tnsnames.ora file into it.  Then install the ODBC drivers.

Second:  The above Experts are correct.  User abcdefg doesn't have the correct permissions to the table being accessed or the table no longer exists.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
66chawgerAuthor Commented:
The oracle DB is remote and am waiting on verification of the uid and pw being used.  slightwv, I will look into downloading/setup for the Oracle Instant Client.
66chawgerAuthor Commented:
Ultimately this was the best solution.  The proprietary Oracle native drivers are more reliable.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.