I am trying to connect to an Oracle database using ADO.
I have installed Oracle Instant Client on my Windows XP machine. I don't want to use the full Oracle client at 175 MB. This is for rare connections on user workstations from an Excel file, and I don't want to make them install a ton of stuff. 11 MB is still more than I wanted but acceptable.
Install process:
[ul][li]Downloaded Oracle Instant Client v 10.2.0.3 Basic Lite and ODBC from Instant Client Downloads.[/li]
[li]Extracted both zip files to C:\Program Files\Oracle Instant Client.[/li]
[li]Added this directory to my path in "System control panel|Advanced|Environment Variables|System variabls|Path".[/li]
[li]Ran cmd and verified the path had been added.[/li]
[li]Double-clicked on odbc_install.exe.[/li][/ul]
I have been web searching and working on this for 90 minutes. I did find a seemingly useful thread on the subject, connecting to Oracle with Instant Client and tried every suggestion that I could.
I do not have SQL*Plus installed. I do not want to use a tnsnames.ora file. I got a copy of a valid tnsnames.ora file from a coworker to see what the connection information should be, here is the entry in question:
[ul][tt]NAME1.MYDOMAIN.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NAME2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = NAME1)
(SERVER = DEDICATED)
)
)[/tt][/ul]
I tried using this suggestion from page 2 of the above thread and saved the following as a .udl file:
[ul][oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Password=PASSWORD;Persist Security Info=True;User ID=USERNAME;Extended Properties="Driver={Oracle in instantclient};Dbq=NAME1.MYDOMAIN.COM:1521/NAME2"[/ul]
When I double-click on the udl file and click Test Connection I get the following message:
[ul]Provider=MSDAORA.1;Password=PASSWORD;User ID=USERNAME;Data Source=name1.mydomain.com;Persist Security Info=True[/ul]
Makes me wonder if the data source parameter is referring to a DSN. I played with DSNs, too, on the theory that if I could at least get it working then I could research a DSN-less connection from that starting point.
Add a System DSN, Microsoft ODBC for Oracle (the only Oracle thing listed):
[ul]Data Source Name=TEST
User Name=Username
Server=name1.mydomain.com[/ul]
There is no "test connection" option.
So I go back to my UDL file and double-click it and select "Microsoft OLE DB Provider for ODBC Drivers", then enter the data source name TEST, add a username and password, and click Test Connection:
I'm stuck. Does anyone have any ideas? Your help would be greatly appreciated.
Erik
I have installed Oracle Instant Client on my Windows XP machine. I don't want to use the full Oracle client at 175 MB. This is for rare connections on user workstations from an Excel file, and I don't want to make them install a ton of stuff. 11 MB is still more than I wanted but acceptable.
Install process:
[ul][li]Downloaded Oracle Instant Client v 10.2.0.3 Basic Lite and ODBC from Instant Client Downloads.[/li]
[li]Extracted both zip files to C:\Program Files\Oracle Instant Client.[/li]
[li]Added this directory to my path in "System control panel|Advanced|Environment Variables|System variabls|Path".[/li]
[li]Ran cmd and verified the path had been added.[/li]
[li]Double-clicked on odbc_install.exe.[/li][/ul]
I have been web searching and working on this for 90 minutes. I did find a seemingly useful thread on the subject, connecting to Oracle with Instant Client and tried every suggestion that I could.
I do not have SQL*Plus installed. I do not want to use a tnsnames.ora file. I got a copy of a valid tnsnames.ora file from a coworker to see what the connection information should be, here is the entry in question:
[ul][tt]NAME1.MYDOMAIN.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NAME2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = NAME1)
(SERVER = DEDICATED)
)
)[/tt][/ul]
I tried using this suggestion from page 2 of the above thread and saved the following as a .udl file:
[ul][oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Password=PASSWORD;Persist Security Info=True;User ID=USERNAME;Extended Properties="Driver={Oracle in instantclient};Dbq=NAME1.MYDOMAIN.COM:1521/NAME2"[/ul]
When I double-click on the udl file and click Test Connection I get the following message:
So I tried a different way. I clicked on Provider in the "Data Link Properties" dialog and choose "Microsoft OLE DB Provider for Oracle." For server name I tried name1.mydomain.com and name1 and name2. I received the following when clicking on Test Connection:Microsoft Data Link Error said:
Examining the connection string,Microsoft Data Link Error said:
[ul]Provider=MSDAORA.1;Password=PASSWORD;User ID=USERNAME;Data Source=name1.mydomain.com;Persist Security Info=True[/ul]
Makes me wonder if the data source parameter is referring to a DSN. I played with DSNs, too, on the theory that if I could at least get it working then I could research a DSN-less connection from that starting point.
Add a System DSN, Microsoft ODBC for Oracle (the only Oracle thing listed):
[ul]Data Source Name=TEST
User Name=Username
Server=name1.mydomain.com[/ul]
There is no "test connection" option.
So I go back to my UDL file and double-click it and select "Microsoft OLE DB Provider for ODBC Drivers", then enter the data source name TEST, add a username and password, and click Test Connection:
I also tried all of the above steps on a Windows Server 2003 machine. It happens to have Oracle Client installed, but I removed this directory from the path. When it was in the path, for "Microsoft OLE DB Provider for Oracle" I was getting:Microsoft ODBC for Oracle said:
and for "Microsoft OLE DB Provider for ODBC Drivers" I got the same error when trying to use the DSN described above, and using the connection string I first mentioned in this post, I get the same error as then.Microsoft Data Link Error said:
I'm stuck. Does anyone have any ideas? Your help would be greatly appreciated.
Erik
