Problem connecting to oracle 10g database.

dabiri · Dec 8, 2014

Hi,
I have a simple vbscript that I had used to connect to an Oracle 10g database and execute simple queries. the database is on a server the vbscript was on my windows-XP machine.
Now the script has been moved to a windows 7-64 bit machine and I'm getting an error when I execute the script. Any suggestions would be greatly appreciated. Here's the script:

Code:

strCon = "Driver={Microsoft ODBC for Oracle}; " & _
        "CONNECTSTRING=(DESCRIPTION=" & _
        "(ADDRESS=(PROTOCOL=TCP)" & _
        "(HOST=xxx.xxx.xxx.xxx.)(PORT=1521))" & _
        "(CONNECT_DATA=(SERVICE_NAME=sid1))); uid=xxxxxx;pwd=xxxxxx;" 
     Set objConn = WScript.CreateObject("ADODB.Connection")    
     Set objRs = WScript.CreateObject("ADODB.Recordset")
     objConn.Open strCon

dabiri · Dec 8, 2014

When I execute the script, this is the error message that I get:
Error: [Microsoft][ODBC Driver Manager] Data source name not
found and no default driver specified.

fredericofonseca · Dec 8, 2014

you need to install the correct client - depending on whether your application runs in 32 or 64 bit you need to install the corresponding Oracle Client (do not install the instant client version) - and you probably also don't have the correct Microsoft odbc client installed and for this I would advise you to use the Oracle client instead of the old Microsoft one.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

LKBrwnDBA · Dec 9, 2014

dabiri said:
Driver={Microsoft ODBC for Oracle};

What? It's widely known that the Windoze driver for Oracle has problems (bugs?).
Follow Federico's advice and install the Oracle driver.
[noevil]

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

dabiri · Dec 10, 2014

where would I find oracle drive, please?
what is it called. oracle's site is like a jungle. any download has 5,000 versions and variations.

fredericofonseca · Dec 10, 2014

oracle 10 no longer available for download, but 11 driver will work fine to connect to 10.

64bit - you need Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64) which is available on this page

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

32bit - you need Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (32-bit) which is availabe on this page

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html

you will need to select the client install (not the instant client) and make sure you select all extras you may need for java/odbc/oledb - not sure now if you will need so select custom install.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

dabiri · Dec 10, 2014

thank you so much frederico.
fantastic. will this install oracle (the whole database thing) or just the odbc part?

dabiri · Dec 10, 2014

trying to install oracle 11g r2:
it has 4 options:
1 - instant client
2 - administrator
3 - runtime
4 - custom
I have already installed this yesterday using option 2 (administrator).
and I can access oracle database using developer tools.
but, the vbscript odbc connection is not working.
do I need to change my connection string from "Driver={Microsoft ODBC for Oracle};" to something else?
thanks.

fredericofonseca · Dec 10, 2014

yes you do need to change your connection string - and as I said you may need other bits installed from the custom install.

for correct connection string see

http://www.connectionstrings.com/oracle/

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

dabiri · Dec 10, 2014

I put the new connection string.
and now i'm getting this error:

80040E21
Error: Multiple-step OLE-DB operation generated errors Check each OLE DB status value, if available. No work was done.
this is my connection string:
strCon = "Data Source=prod;User Id=xxxxxx;Password=xxxxxx;" & _
"Integrated Security=no;"
thanks.

fredericofonseca · Dec 10, 2014

you probably getting another error on the background. hard to tell what the issue is without a lot more information.

see

http://databases.aspfaq.com/database/what-is-this-multiple-step-ole-db-error.html

as it will help you identify the issue.

And I would start by setting up a simple query just to prove that the connection works .. something like "select 'this is a test' from dual"

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

dabiri · Dec 11, 2014

thanks frederico.
i'm not even running a query - just trying to connect is what giveS me the error.
here's my simple script which tries just to connect:

Code:

msgbox "started script"  

strCon = "Data Source=oracle_SID;User Id=XXXXXX;Password=XXXXXX;" & _ 
         "Integrated Security=no;"      
     Set objConn = WScript.CreateObject("ADODB.Connection")      
     Set objRs = WScript.CreateObject("ADODB.Recordset")
     objConn.Open strCon 

msgbox "finished script"

fredericofonseca · Dec 11, 2014

missing provider.

try
strCon = "Provider=OraOLEDB.Oracle;Data Source=oracle_SID;User Id=XXXXXX;Password=XXXXXX;"

http://www.connectionstrings.com/oracle-provider-for-ole-db-oraoledb/

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

fredericofonseca · Dec 11, 2014

or and as you had on your original post

strCon = "Provider=OraOLEDB.Oracle; " & _
"Data Source=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=xxx.xxx.xxx.xxx.)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=sid1))); uid=xxxxxx;pwd=xxxxxx;"
Set objConn = WScript.CreateObject("ADODB.Connection")
Set objRs = WScript.CreateObject("ADODB.Recordset")
objConn.Open strCon

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

dabiri · Dec 12, 2014

thanks.
I ran the original connection string:

Code:

msgbox "started" 
     strCon = "Provider=OraOLEDB.Oracle; " & _
        "CONNECTSTRING=(DESCRIPTION=" & _
        "(ADDRESS=(PROTOCOL=TCP)" & _
        "(HOST=999.999.999.999)(PORT=1521))" & _
        "(CONNECT_DATA=(SERVICE_NAME=my_SID))); uid=XXXXXX;pwd=XXXXXX;"  
     Set objConn = WScript.CreateObject("ADODB.Connection")      
     Set objRs = WScript.CreateObject("ADODB.Recordset")
msgbox strCon
     objConn.Open strCon 
msgbox "connected"
wscript.quit

now i'm getting this error:
Provider cannot be found. It may not be properly installed.
code:800A0E7A

fredericofonseca · Dec 12, 2014

possibly 32bit vs 64bit - the driver you installed needs to match how your application is running - if you are running a 32 bit application you need the 32 bit oracle driver.

Also in some version of oracle the oledb driver does not get correctly installed - see

https://community.oracle.com/message/10790543

and also see

https://www.google.ie/?gws_rd=ssl#q...be+properly+installed.+oracle+client+vbscript

plenty of answers to the same question so start digging and see which solution applies to your case

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

guitarzan · Dec 17, 2014

Cross-posted in the vbscript forum: thread329-1741720

fredericofonseca · Dec 17, 2014

taking what was posted on your other thread you need either to use 32 bit oracle client or use the 64 bit cscript.

access is running ok when I do it from the command prompt using: C:\Windows\SYSWOW64\cscript.exe myscript.vbs
but oracle is giving error: ORA-12560 TNS: PROTOCOL ADAPTOR ERROR

Regarding the error above you need to make sure you have the correct tnsnames setup as it seems by the error that you are either using a different connection than the one above, or that you solved that issue and are now facing another one.

and please put here the full content of your myscript.vbs - replacing only the userid-password with something else. Leave everything else as is

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

dabiri · Dec 17, 2014

thanks frederico.
I got the access job to work as per guitarzan's suggestions. it worked.
I also got oracle to work. the tnsnames.ora file is properly set up in the right directory. (sorry for cross-postings). I've been stumped by this issue for almost two weeks now. kudos to guitarzan. he pointed me in the right direction, and thank you as well for posting so many helpful hints. the only problem now is that the script needs to be executed either in command prompt or via a bat file.
thanks a bunch.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Problem connecting to oracle 10g database.

Technical User

Technical User

IS-IT--Management

MIS

Technical User

IS-IT--Management

Technical User

Technical User

IS-IT--Management

Technical User

IS-IT--Management

Technical User

IS-IT--Management

IS-IT--Management

Technical User

IS-IT--Management

Programmer

IS-IT--Management

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor