Connecting to an Oracle 9i server from Excel

[Paul12345671]

Hello,
I'm trying to connect to an Oracle 9i server using Excel 2003 (running on Windows XP).

I click on Data -> Import External Data -> Import Data, then double-click '+Connect to New Data Source.odc'. I choose Oracle, then I'm presented with a dialog box requesting a Server name and username/password.

When I'm connecting to a test database on my local pc, I have no problem. However, when the oracle database is on a server on another machine, I get an error saying ORA=12154: TNS:could not resolve service name.

I'm assuming that I have not entered the server name in the correct format. I've tried entering <ipnumber>:1521:<databasename>, and all sorts of variations. When connecting to my local database, I just entered the SID, without the IP number. However, to connect to a server, I think that I must enter the IP number somehow.

I can connect to the remote oracle database using Crystal Reports, so I'm assuming that my Excel problem is caused by entering the server name in the wrong format. Can anyone assist?

Thank you.

 

[KenCunningham]

What is your exact entry in tnsnames.ora?

The internet - allowing those who don't know what they're talking about to have their say.

 

[karluk]

I went through the steps you indicate. I had no trouble connecting to a database located on a different server. For the "server" info, I simply entered the connection string from the tnsnames.ora file located in the network\admin subdirectory of my Oracle client home. No ip address or port was needed. The tnsnames.ora entry already contains this information.

So, in your case, the most likely cause of your ORA-12154 error is that your tnsnames.ora file doesn't yet contain an entry for the remote database. You find this file and check which databases are actually listed. If the remote database isn't there, you should edit tnsnames.ora to add the entry, supplying the correct host and port. I also suggest you save a copy of tnsnames.ora before editing it, just in case.

 

[Paul12345671]

Thanks, guys. However, I do have an entry in tnsnames.ora. It says:

HMPS.HS.QFA.COM.AU =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hs-gladiator)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hmps.hs)
)

 

[KenCunningham]

Hi Paul. Have you tried making 'SERVICE_NAME' = HMPS.HS.QFA.COM.AU as well?

The internet - allowing those who don't know what they're talking about to have their say.