Re: ODBC connection string

qetoom_at_gmail.com wrote:
> Hi all,
> I have setup an oracle10g database. I can connect to the data base
> through sqlplus from the local machine. I want to export msaccess
> tables to the oracle db, but it seems that I'm unable to provide the
> ODBC driver (the one that comes with windowsxp) with a vialed
> connection string. I searched the web but I hadn't found a working
> example. here is my tnsnames file, does anybody have an idea what the
> connection string is? I've been trying the following connection string:
> "Driver={Microsoft ODBC for Oracle};Server=test;Uid=scott;Pwd=;"
> with quotes and every thing.
>
> ========================================
> ORCL =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = TCP)(HOST = test.myhome.com)(PORT = 1521))
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = orcl)
> )
> )
>
> EXTPROC_CONNECTION_DATA =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
> )
> (CONNECT_DATA =
> (SID = PLSExtProc)
> (PRESENTATION = RO)
> )
> )
> =========================================

I suggest that you use the Oracle ODBC client rather than the one supplied by Microsoft - this will likely require you to select "Custom" during the client installation.

If you create an ODBC connection named MYTEST, that points to the "ORCL" connection in your TNSNAMES.ORA, the connection string would look like this:
"Data Source=MYTEST;User ID=MyUserName;Password=MyPasswordName;"

If you do not want to set up an ODBC connection on each computer, and you installed the Oracle OLE DB functionality during the client install, you can use a connection string like this: "Provider=OraOLEDB.Oracle;Data Source=ORCL;User ID=MyUserName;Password=MyPasswordName;"

I suggest that you change "ORCL" in the TNSNAMES.ORA to something more meaningful, unless that is the database SID name. If this is a test server, you can change the line that reads "ORCL =" to "ORCLTEST =" if you would like to be able to distinguish between the product and test servers that have an identical database SID name. After a sucessful login to the database, the connection string will have additional data appended to it - some of that data may be of use.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Nov 19 2006 - 08:27:23 CST