Common error in a Peculiar Situation - URGENT - HELP PLEASE 4

[Deadline]

Hi,
Actually I am required to write an program that will be scheduled to run once every night.

In that program I am making a connection to ORacle to fetch list of email addresses and Employee Names and insert that info into an Access database. In my machine I have a DSN to the Oracle Server but no Oracle Client COmponents. I am on Win 2000 platform. It works fine when running in my machine.

When I run the same program the server(WinNT 4 Server), where it is supposed to run actually, it returns me an error that reads like this -

Microsoft OLE DB Provider for Oracle-ORA-12154: TNS:could not resolve service name
--2147467259

The connectivity to Oracle server isn't an issue because, when I attempted to login using the SQL*Plus in the server using the same user name and password as in my code, it perfectly gets into it and I am able to make queries and stuff.

It works here and not there... What could be the reason ??


F1 me please... Very urgent Thank you very much.

RR

 

[Deadline]

UPDATE :
The 'code' I was talking about is in Visual Basic. Thank you very much.

RR

 

[karluk]

My guess is the VB connection is looking in a different directory than SQL*Plus when attempting to resolve the service name - for example {oracle_home}\network\admin instead of {oracle_home}\net8\admin.

 

[Deadline]

UPDATE:
I discovered that my machine has Oracle Client Components Installed. Its that it did not appear in the Start->Program Menu


And Karluk the following is my VB Connection string:-

cnOracle.open  "Provider=MSDAORA.1;Password=xxxxx;User ID=xxd01;Data Source=hrsp;Persist Security Info=True"

Where could this be wrong ??
Thank you very much.

RR

 

[karluk]

My earlier post was directed at the possibility that you have two versions of sqlnet/net8 installed. Perhaps your sql*plus connection is using one and your VB is using the other. See thread186-69001 for more details on this issue.

Which ODBC driver are you using? I've found that switching from the MS ODBC driver for Oracle to the Oracle supplied driver, or vice versa, will sometimes resolve this type of issue. At the least, it provides info about the scope of the problem.

 

[Deadline]

Hi,
I am using Microsoft ODBC Driver for Oracle. And could you please tell me how/where to aquire the Oracle supplied ODBC driver ???
Thank you very much.

RR

 

[waterDBA]

>> oerr ora 12154
12154, 00000, "TNS:could not resolve service name"
// *Cause: The service name specified is not defined correctly in the
// TNSNAMES.ORA file.
// *Action: Make the following checks and correct the error:
// - Verify that a TNSNAMES.ORA file exists and is in the proper
// place and accessible. See the operating system specific manual
// for details on the required name and location.
// - Check to see that the service name exists in one of the
// TNSNAMES.ORA files and add it if necessary.
// - Make sure there are no syntax errors anywhere in the file.
// Particularly look for unmatched parentheses or stray characters.
// Any error in a TNSNAMES.ORA file makes it unusable. See
// Chapter 4 in the SQL*Net V2 Administrator's Guide. If
// possible, regenerate the configuration files using the Oracle
// Network Manager.

 

[ljspop]

Hi,

Oracle ODBC Drivers are available at technet:

http://technet.oracle.com/software/utilities/software_index.htm

That said, I've had good success with the Microsoft supplied
drivers in the past. As Karluk said, make sure all of your tnsnames files are properly configured.

Good luck!

Bob Bob Lowell
ljspop@yahoo.com

 

[Deadline]

Dear Bob, and Karluk and WaterDBA,

While waiting for your post, I did an extensive solution-hunt in the Net. I came across this article:

http://support.microsoft.com/support/kb/articles/Q259/9/59.ASP

After reading that article, I thought, why not try restarting the (production) server once!! And to my amazement and surprise, it DID work!!! I don't know the reason for this exactly. But without fail, go through the article. It gives us Microsoft's perception of Oracle problems. Interesting really.

Once again,
Thank you very much.

RR

 

[loschiavoj]

Try placing the tsnames file in the same directory as your app.