unable to connect oracle9i with vb6

[Mishika9486]

Hi pals..

i hav developed an application in vb nd am tryin to connect using oracle..

it gives the following error using ADODC and ODBC

"test connection failure because of an error in initializing provider.[microsoft][odbc]driver for oracle]"

please help..

[debasisdas]

kindly post your connection string for my reference.

[QVeen72]

Hi,

Use this Connection string :

ConnStr = "UID=username;PWD=password;" _
        & "Server=OSName;Driver={Microsoft ODBC For Oracle}" _
        & ";DSN = ''"
 

OSName in the above is the Oracle Client's Net Configuration Name...
First u have to configure the Client..

Regards
Veena

[Mishika9486]

Hi,

Thanks a lot. I could achieve it through ODBC driver .But the application has become really slow. it nearly hangs while fetching the data.

Also the problem still persists when i use " microsoft oledb provider for oracle".
the steps i follow are as below:
1. set the ado data control on form, click to set the properties of Adodc.
2. Choose build for conn str, microsoft ole db for oracle.
3. the next click asks for the server name, the user name and pwd.
4. testing connection after feeding "scott/tiger", gives error, it says "Connection fails.error while initializing provider, the error could not be recovered from oracle".
5. Even if i set the server name to oracle9i , it doesnt work giving the same error.

Thanks a lot for devoting time to my problems.

[QVeen72]

hI,

U HAVE TO GIVE "Server Name" as the NetConfig Client Name. And Why use Scott/Tiger..? Create a User /pwd in Oracle for ur Schema and give that..

REgards
Veena

[Mishika9486]

hi,
how do i find what is my net config client name?

[QVeen72]

Hi,

After Installing the Oracle Client, u have to do configure the Client using the "Net Configuration Assistant", by giving the Global Database name and option "Local Net Service Name Configuration" and Give the Host Name.
At the end of it, it will ask the Name, there provide some name. So whenevr u connect to oracle, u have to Refer that as ServerName...


REgards
Veena

[Mishika9486]

Hi,
I had given the gds name as oracle9i at the time of installation.
I dont use the server name while logging on, i directly logon with user/pwd.
I had given oracle9i while connectivity before alos but it is not working.

kindly suggest..

Also, how can i import a set of tables from one schema to another?

Giving the privilege for all table is not serving the purpose.

thanks & regards
Mishika

[QVeen72]

Hi,
I had given the gds name as oracle9i at the time of installation.
I dont use the server name while logging on, i directly logon with user/pwd.
I had given oracle9i while connectivity before alos but it is not working.

kindly suggest..

Also, how can i import a set of tables from one schema to another?

Giving the privilege for all table is not serving the purpose.

thanks & regards
Mishika

Hi,

Did u configure the Local Net Name using the "Net Configuration Assistant"...?
First u have to do that.. see my previous post how to create it..
To Export, use "Exp" in command prompt, and follow the Instructions on the screen, and Export the Tables to a Dump File.
And to Import those tables, use "Imp" at the command prompt.

One more way of creating tables from one schema to another is run this SQL Statement (provided u have Priviliges to Create Table)

Create Table NewSchema.MyTableName As Select * From OldSchema.MyTableName

If the table being created is already present in the NewSchema, u will get an Error, Probably u may have to drop the Table before creating it...

Regards
Veena

[Mishika9486]

hi,

Thanks a ton...will try n tell u.

[Mishika9486]

Hi,

I configured the net config client and gave the server name as well as the host name but in vain. the oracle connection is giving the same eror.

"Error in initializing provider. we cannot retrieve error report from oracle."

Also, the EXP command on SQL prompt is giving the following error SP2-0734.
It says statement not completed.
i wrote
sql> exp;
sql> exp table_name to A;

none of them worked also i tried
sql> help exp;
which said no help available..

the project deadline is approaching...kindly help..

[Mishika9486]

Hi,

Did u configure the Local Net Name using the "Net Configuration Assistant"...?
First u have to do that.. see my previous post how to create it..
To Export, use "Exp" in command prompt, and follow the Instructions on the screen, and Export the Tables to a Dump File.
And to Import those tables, use "Imp" at the command prompt.

One more way of creating tables from one schema to another is run this SQL Statement (provided u have Priviliges to Create Table)

Create Table NewSchema.MyTableName As Select * From OldSchema.MyTableName

If the table being created is already present in the NewSchema, u will get an Error, Probably u may have to drop the Table before creating it...

Regards
Veena

Hi,

I configured the net config client and gave the server name as well as the host name but in vain. the oracle connection is giving the same eror.

"Error in initializing provider. we cannot retrieve error report from oracle."

Also, the EXP command on SQL prompt is giving the following error SP2-0734.
It says statement not completed.
i wrote
sql> exp;
sql> exp table_name to A;

none of them worked also i tried
sql> help exp;
which said no help available..

the project deadline is approaching...kindly help..