Connection to Oracle via VBScript

Question

I have two databases on the same server with the same name and different port number. The tnxnames.ora entry looks like (actually one database is a clone of the other one.)

AAAA.FSA.GOV.UK =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1530)))
(CONNECT_DATA = (SID = AAAA)))


AAAA.FSA.GOV.UK =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1521)))
(CONNECT_DATA = (SID = AAAA)))

I am using MS-Access to develop the application and using ADODB connectivity to access the server. I am try to access the data from the server. My connection string is look like:

sConStr = "Driver={oracle in oraclient10g_home1};SERVER=SERVER_NAME;DBQ=AAAA;UID=username;Pwd=pswd"

Set oConn = New ADODB.Connection

oConn.connectionstring = sConStr
oConn.open

My problem is, my ADODB always connect to database port number 1530. I am not able to set it in the connetion string to access the database in the port 1521.

Is it possible to mention the port number in the connection string to access different database with the same name and in the same server?

I need to prepare the application to access the cloned database instead of original one. Nothing but the port number is different between the databases.

Hi, I am managed to resolve this problem. There is a different connection string is available for ADODB connectivity. DNS less connection. I used that, and mentioned the tnsnames entry in the same connectionstring itself that helps me to connect to the database. — chinnagaja
– chinnagaja, Commented Aug 26, 2009 at 11:01

Fionnuala · Accepted Answer · 2009-08-24 16:44:32Z

1

You may find this useful: http://www.connectionstrings.com/oracle

answered Aug 24, 2009 at 16:44

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Walker Over a year ago

Absolutely, connectionstrings.com is your friend!

chinnagaja Over a year ago

i have checked in that.. i am using "Oracle in OraClient10g" driver and the same in not listed in that. I accept Oracle in OraHome92 (Driver={Oracle in OraHome92};Dbq=myTNSServiceName;Uid=myUsername;Pwd=myPassword;) setting is similar to mine, but in my case, myTNSServiceName is same for both the database.

chinnagaja Over a year ago

From this site, I got the DNS less connectionstring and I am able to achieve what i needed.

David Walker · Accepted Answer · 2009-08-24 16:08:48Z

0

Try adding your port number to the end of the server name separated by a colon.

SERVER=SERVER_NAME:1521

I'm not an Oracle user, but that will work with MS SQL Server.

answered Aug 24, 2009 at 16:08

David Walker

1,5062 gold badges16 silver badges19 bronze badges

1 Comment

chinnagaja Over a year ago

this setting takes server name but not recognise the port number!

Mark Nold · Accepted Answer · 2009-08-25 14:57:26Z

0

This may be a dumb question... but are both of those entries in your tnsnames.ora?

AAAA.FSA.GOV.UK =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1530)))
(CONNECT_DATA = (SID = AAAA)))


AAAA.FSA.GOV.UK = 
(DESCRIPTION = 
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1521)))
(CONNECT_DATA = (SID = AAAA)))

Try changing it so each name is unique.

AAAA.FSA.GOV.UK =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1530)))
(CONNECT_DATA = (SID = AAAA)))


AA21.FSA.GOV.UK = 
(DESCRIPTION = 
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1521)))
(CONNECT_DATA = (SID = AAAA)))

Then on the command line try tnsping AAAA.FSA.GOV.UK and tnsping AAAA21.FSA.GOV.UK

answered Aug 25, 2009 at 14:57

Mark Nold

5,6937 gold badges34 silver badges34 bronze badges

1 Comment

chinnagaja Over a year ago

I have tried that too. But the ADODB connection is always connecting to the first database since both databases are having the same username and password. I am not sure the Ports are being recognised by the ADODB connectivity.

Collectives™ on Stack Overflow

Connection to Oracle via VBScript

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related