INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Problem connecting to oracle 10g database.

Problem connecting to oracle 10g database.

(OP)
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 

RE: Problem connecting to oracle 10g database.

(OP)
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.

RE: Problem connecting to oracle 10g database.

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Problem connecting to oracle 10g database.

Quote (dabiri)

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

RE: Problem connecting to oracle 10g database.

(OP)
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.

RE: Problem connecting to oracle 10g database.

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/enterpr...

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/enterpr...

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Problem connecting to oracle 10g database.

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

RE: Problem connecting to oracle 10g database.

(OP)
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.

RE: Problem connecting to oracle 10g database.

(OP)
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.

RE: Problem connecting to oracle 10g database.

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-... 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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Problem connecting to oracle 10g database.

(OP)
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" 

RE: Problem connecting to oracle 10g database.

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Problem connecting to oracle 10g database.

(OP)
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

RE: Problem connecting to oracle 10g database.

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=800A0E7A+Provi...

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

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Problem connecting to oracle 10g database.

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Problem connecting to oracle 10g database.

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close