ODBC Connects

Hi there!

Apologies to you Whiz kids for my ignorance herewith but I am trying to link an SQL database table to another database table from which I want to import data.

I'm an "Access" database girl who has had to face the fact that my database must now become an adult and join the 'big league' and so I am currently "playing" in SQL as I try to come to grips with this new programme. In Access, ODBC links were so easy! I used to go to TABLES, select LINK TABLES, select my ODBC link, log into the other database and, viola!, the tables would be there. I'd select the one/s I wanted and could even limit the fields that came though.

How does one do this in SQL? I do have an SQL manual here but have no idea where to even start reading in that (and it's a BIG MANUAL!)

If anyone can give me some direction on how to do this, I would be most appreciative. Remember, I'm a baby in SQL so please keep it simple!

Thanks everyone!!

MariaI'm not much ahead of you and perhaps there's a better way but...

SELECT * FROM OPENROWSET( parameters )

-- OR --

SELECT * FROM table_name1 JOIN OPENROWSET( parameters)

would appear to fit the bill.

Trouble is I can't get the 'parameters' bits figured out! (see my earlier post 'openrowset parameters").

Also DTS (Data Transformatin Services) makes it a snap to import via ODBC etc but is tedious to maintain for anything more than quick fixes.|||Do you want to use SQL Server databsese tables in Access? If you do than you have two choices:

1) Use the "old fashion" ODBC Databases tehnique: first create an ODBC chanel to your SQLServer database (From ODBC Manager in Control panel, or in Administrative Tools if you are using Windows 2000. You can create either a file datasource or a machine datasource). Then you can use this chanel in you link table wizard.

2) Starting with Access 2000, you can have a different type of database: .adp - Microsoft Access Project. This type of "database" allows you to use Access as a front-end to your SQLServer database. This means that you have tables, queries, storeproc in SQL Server and Forms and Reports in Access (all in one single project - .adp) For these kind of project native driversfor SQLServer are used. With Access 2000 you can use SQLServer 7 database, and with Access 2002 (XP) you can use SQLServer 2000 databases.

.Adp are version dependent as you can see from above paragraph. With "link method" you can use any type of SQLServer database you want. The only thing you have to have is the correct version of ODBC drivers for SQLServer. You can download ODBC drivers from microsoft. They are found in a package called "mdac" (Microsoft data access components)

IONUT

PS
As an own opinion it's a very good ideea to migrate your databases to SqlServer, but you should also stop using Access even for a front end. It's very slow with large amounts of data, it has runtime libraries anly from XP version (as much as I know) and therefore is very expensive (you have to have a MS Office licence for each seat). It's true that it is easy to code and it has one of the best report designer Microsoft has ever build, but... that's all

Good luck!|||How would one link the tables of one (production DB) to another SQL DB?

Thanks!!|||Hi there!

Thanks for that. I am actually, trying to leave out Access altogether and link the SQL database (which will be my data warehouse) to the source database where staff completed their service statistics. To date, I am using Access to link to the souce database but this is becoming too large and, as you will appreciate, is slow.

The ODBC connnection, that I am already using for the Access link/import is the same for SQL (according to the software house who produce the source database) so I just need now to tell SQL to go and get the specified fields/tables from the source database and dump them into new tables in this new SQL database. That's the bit I'm having the problems with.

Cheers!

Maria

Originally posted by ionut calin
Do you want to use SQL Server databsese tables in Access? If you do than you have two choices:

1) Use the "old fashion" ODBC Databases tehnique: first create an ODBC chanel to your SQLServer database (From ODBC Manager in Control panel, or in Administrative Tools if you are using Windows 2000. You can create either a file datasource or a machine datasource). Then you can use this chanel in you link table wizard.

2) Starting with Access 2000, you can have a different type of database: .adp - Microsoft Access Project. This type of "database" allows you to use Access as a front-end to your SQLServer database. This means that you have tables, queries, storeproc in SQL Server and Forms and Reports in Access (all in one single project - .adp) For these kind of project native driversfor SQLServer are used. With Access 2000 you can use SQLServer 7 database, and with Access 2002 (XP) you can use SQLServer 2000 databases.

.Adp are version dependent as you can see from above paragraph. With "link method" you can use any type of SQLServer database you want. The only thing you have to have is the correct version of ODBC drivers for SQLServer. You can download ODBC drivers from microsoft. They are found in a package called "mdac" (Microsoft data access components)

IONUT

PS
As an own opinion it's a very good ideea to migrate your databases to SqlServer, but you should also stop using Access even for a front end. It's very slow with large amounts of data, it has runtime libraries anly from XP version (as much as I know) and therefore is very expensive (you have to have a MS Office licence for each seat). It's true that it is easy to code and it has one of the best report designer Microsoft has ever build, but... that's all

Good luck!|||LOL!! Glad to know there are others in the same situation as me!! I'll check out your posting re the parameters. The replies there may be exactly what I am after.

I've tried the DTS and it works, albiet slowly from Access but getting it to talk to the source software (Jade) is proving the problem. According to the software suppliers, it is the exact same ODBC link as used when importing in to Access but I can't get it work to date. I generally get an MMC.exe error and it 'packs a sad'.

I'll keep you posted on what I learn from here!

Cheers!

Maria

Originally posted by berniev
I'm not much ahead of you and perhaps there's a better way but...

SELECT * FROM OPENROWSET( parameters )

-- OR --

SELECT * FROM table_name1 JOIN OPENROWSET( parameters)

would appear to fit the bill.

Trouble is I can't get the 'parameters' bits figured out! (see my earlier post 'openrowset parameters").

Also DTS (Data Transformatin Services) makes it a snap to import via ODBC etc but is tedious to maintain for anything more than quick fixes.|||Also, have a look at "linked servers" under SQL Sercurity tab.

You still have to either input various parameters, but in my case this worked immediately USING A DSN.

And then
SELECT *
FROM OPENQUERY(linked_server_name, 'SELECT * FROM table_name')
-- works

To run DSN-less is still eluding me, but a reply to my post did lead me to save the DTS as VBscript which gave a heap of info. Problem is that the DTS seem to have its own way of doing things that is different to TSQL. Worse, it uses Microsoft Jet OLEDB 4.0, which SQLServer2000 Books on line says is for Access only! (I thought of you) I am using SQLServer7. Perhaps there's a difference. And all references to connectionproperties seem to refer to DTS programming only.

odbc connection to access

Hello,

I am a newcomer to databases in general and I am having some difficulties. I have an access database that i want to link to sql. I have trauled the web and notice that ODBC connection seems to be the recommended way.
I have set up a DSN which points to the access database but am now stuck as to what to do in sql to be able to query the database.
Could someone please help?
Many thanksHere is how you can connect. HTH

Set cnndb= New ADODB.Connection
cnndb.ConnectionString = "DSN=<dsnname>;UID=<userID>;PWD=<pwd>;"
cnndb.Open

setup record set .
set rs=new adodb.recordset
rs.open (<your sql statement>)

set rs=nothing
set cnndb=nothing|||Thank you sivaroo,
How do I query the connection?
I know it sounds thick but I really am i newboy|||To see if connection is successful you can use

if cnndb.state = 0 not connected
if cnndb.state = 1 connected

lets say you have sql like this.

strSql="Select * from table1"

once you created connection like previously mentioned.

dim rs as adodb.recordset
dim data1 as string

Set rs = Cnndb.Execute(strSql) 'this will pull data. then you can loop throug it to see
do while not rs.eof
data1=rs.fields("Column1").value
rs.movenext
loop

remeber to close like previously mentioned.|||Hello,

if you don't want to use VBA you can connect to your sql-server in different way.

Make up a new dsn for your sql server.
-> goto your tables object browser
-> press new
-> link table
-> filetyp : odbc database
-> tabstrip computer data source
-> choose your dsn
-> select your table

the table will now appear as a 'normal' table in your object browser.
You can query this like a regular table.
(Note that i use a german access so i only translated the menu items back to english)

ODBC communication link failure to SQL server

Okay-- if anyone can solve this they are truly the SQL genius! We are getting this error when we run a VB program that we use to access an SQL database on a server across our network on a workstation. In fact we get this same error when we even run the program on the server where the SQL database is running or on any of our workstations. Here is the error message:

08501:[Micorsoft][odbc sql server driver] communications link failure

Now the odd thing is that many other functions in the workstation application work fine and retrieve data from SQL but certain data requests by the workstation application fail with the above error message and we get this message consistently. Even though it appears that different workstations running the identical Vb application will get this error consistently but in different locations when running the application. We were running SQL 6.5 on an old server, with the workstation application for literally years without any problems. We also decided to upgrade to a new server, installed server 2000 operating system and the latest version of SQl -- moved all the databases pointed the workstations odbc at this new server and get exactly the same error in the same location in the workstation applications. The programmer that wrote the application and designed the database in SQL can't find the problem and a number of other computer "experts" also could not find the error. We did add a new linksys DSL router/firewall but everything kept working after this installation for several weeks so I don't know if this is the problem on the network. THe programmer also noted that he had problems using terminal services on our network to connect to his office computer and decided that there must be some network issues that are causing the ODBC communicaitions to fail and also terminal services to fail-- or of course they may be unrelated. Has anyone ever seen this ODBC communication error in their travels through SQL implementations? Any help will be greatly appreciated. If we can't fix this we will have to abandon a software application that has been used for over five years and just too complex to rewrite.

Jeff KilpatrickWell, this challenge is not really for a "true DBA", simply because it's related to ODBC driver for SQL Server. Link failure may be caused by several things, including improper closing of resultsets without notifying the server that no more records are needed. The definition ofthe error is:

The communication link between the driver and the data source to which the driver was connected failed before the function completed processing.

And that's what you need to investigate. The reason why it worked with 6.5 may be as simple as the version of the driver. So all the seeming mysteries are probably lying right in there.|||Not playing around with Windows XP SP2, are you?

Anyway, It sounds as though there is an ODBC DSN set up on each of these machines. Go through the configuration of this DSN on one of the affected machines, and see if the test connection button comes back successfully. If it is successful, then you may be looking at a client (i.e. VB Application) problem.

If the test is successful, have the programmer assemble all of his connection strings. If any of them specify PROVIDER as one of the attributes, then you may have your suspect. An ODBC DSN specifies PROVIDER and DATA SOURCE for most connection strings it is used in. Most often, it also specifies DATABASE. I have not experimented with it so much that I know if a connection string can override the DSN, or not, but it is worth a check. For this line of questioning, it would help if you could identify what functions seem to be erroring out. If it is random on each machine, and each attempt, then this will be a pain to figure out.

Another place to check is to make sure you have at least MDAC 2.7 on all of the machines. If you have applied SQL SP3 to the SQL box, then that box at least should have MDAC 2.7 sp1, I think. Microsoft has an MDAC version checker available on their website.

Hope this helps.|||We recently moved from an older (SQL 7 I think) server to SQL2000. Our VB application was using a DNS pointer to point to the SQL server.

This no longer worked once we moved the data to the SQL2000 server. As the poster above mentioned, I ran across a suggestion to run the MDAC patch. There is actually a newer version out now MDAC 2.8. I first ran this on my desktop and then we had to push it out and install it on all the clients which needed to connect to the new server.

Once we did that, we were able to connect to the SQL 2000 server without any problems using the DNS pointer.

A note, with ODBC, the pointer will not show up on your server list, you have to just type it in manually.

odbc communication link failure

Hello,

I have a problem in ODBC that when I open a call logging program, it display "08S01 communication link failure. However, the connection between the driver and data source (in Sydney) is ping ok and data source test successful.

My PC os : window xp professional sp2

ODBC drvier version : 2000.85.1117.00

Can anyone help me to solve the problem ?

Thanks

Rex Leung

Rex,

Could share out the exact error message from your odbc driver when connect fails? Also, is your server name instance and default instance. Is it listened on TCP or NP. can you connect to the server from the same machine. What exactly the connection string you were using for the local odbc connection?

Thanks,

ODBC Access Link to SS2K5-Cannot update new records.

Access 2003 connected via ODBC using
ODBC;Driver={SQL Native
Client};Server=ServerName;Database=DbName;UID=xx;P WD=xxx
Originally the data was imported from Access tables. All imported records
can be edited or updated without a problem but new records created from
access using code or access table views cannot be edited.
The returned error:
The records has been changed by another since you started editing it. ...
If a record is copied from an existing record by cutting and pasting the
complete record in an access table view then it is ok but if the record is
added by going to the new record (*) at the bottom of the table and adding
the one field that does not allow nulls then the autonumber gets updated
properly but none of the other fields can be edited.
It appears the SS2K5 server will not allow the access application to delete
one of the records that cannot be edited either.
The error:
The Microsoft Jet dtabase engine stopped the process because you and another
user are attempting to change the same data at the same time.
If you log into the Server using Management Studio with the same credentials
you can do anything you want.
Looked a differnet connection strings but am not aware of an ODBC
configuration with record locking directives.
Thanks in advance for any help you might offer.
RobGMiller
It's hard to diagnose a problem like this without being able to see
the database schema and the application/form code. One thing that
often helps is to add a timestamp column to the table. This lets
Access know if a row has been altered by another process since it was
fetched.
-Mary
On Fri, 14 Dec 2007 12:43:02 -0800, RobGMiller
<RobGMiller@.discussions.microsoft.com> wrote:

>Access 2003 connected via ODBC using
>ODBC;Driver={SQL Native
>Client};Server=ServerName;Database=DbName;UID=xx; PWD=xxx
>Originally the data was imported from Access tables. All imported records
>can be edited or updated without a problem but new records created from
>access using code or access table views cannot be edited.
>The returned error:
>The records has been changed by another since you started editing it. ...
>If a record is copied from an existing record by cutting and pasting the
>complete record in an access table view then it is ok but if the record is
>added by going to the new record (*) at the bottom of the table and adding
>the one field that does not allow nulls then the autonumber gets updated
>properly but none of the other fields can be edited.
>It appears the SS2K5 server will not allow the access application to delete
>one of the records that cannot be edited either.
>The error:
>The Microsoft Jet dtabase engine stopped the process because you and another
>user are attempting to change the same data at the same time.
>If you log into the Server using Management Studio with the same credentials
>you can do anything you want.
>Looked a differnet connection strings but am not aware of an ODBC
>configuration with record locking directives.
>Thanks in advance for any help you might offer.

ODBC Access Link to SS2K5-Cannot update new records.

Access 2003 connected via ODBC using
ODBC;Driver={SQL Native
Client};Server=ServerName;Database=DbNam
e;UID=xx;PWD=xxx
Originally the data was imported from Access tables. All imported records
can be edited or updated without a problem but new records created from
access using code or access table views cannot be edited.
The returned error:
The records has been changed by another since you started editing it. ...
If a record is copied from an existing record by cutting and pasting the
complete record in an access table view then it is ok but if the record is
added by going to the new record (*) at the bottom of the table and adding
the one field that does not allow nulls then the autonumber gets updated
properly but none of the other fields can be edited.
It appears the SS2K5 server will not allow the access application to delete
one of the records that cannot be edited either.
The error:
The Microsoft Jet dtabase engine stopped the process because you and another
user are attempting to change the same data at the same time.
If you log into the Server using Management Studio with the same credentials
you can do anything you want.
Looked a differnet connection strings but am not aware of an ODBC
configuration with record locking directives.
Thanks in advance for any help you might offer.
RobGMillerIt's hard to diagnose a problem like this without being able to see
the database schema and the application/form code. One thing that
often helps is to add a timestamp column to the table. This lets
Access know if a row has been altered by another process since it was
fetched.
-Mary
On Fri, 14 Dec 2007 12:43:02 -0800, RobGMiller
<RobGMiller@.discussions.microsoft.com> wrote:

>Access 2003 connected via ODBC using
>ODBC;Driver={SQL Native
> Client};Server=ServerName;Database=DbNam
e;UID=xx;PWD=xxx
>Originally the data was imported from Access tables. All imported records
>can be edited or updated without a problem but new records created from
>access using code or access table views cannot be edited.
>The returned error:
>The records has been changed by another since you started editing it. ...
>If a record is copied from an existing record by cutting and pasting the
>complete record in an access table view then it is ok but if the record is
>added by going to the new record (*) at the bottom of the table and adding
>the one field that does not allow nulls then the autonumber gets updated
>properly but none of the other fields can be edited.
>It appears the SS2K5 server will not allow the access application to delete
>one of the records that cannot be edited either.
>The error:
>The Microsoft Jet dtabase engine stopped the process because you and anothe
r
>user are attempting to change the same data at the same time.
>If you log into the Server using Management Studio with the same credential
s
>you can do anything you want.
>Looked a differnet connection strings but am not aware of an ODBC
>configuration with record locking directives.
>Thanks in advance for any help you might offer.

Odbc access

I have a DB and some tables that I'm moving to Sql2005 machine that just get
query access. People use ACCESS with ODBC link table.
What permissions other than datareader would allow them to only see USER
tables?
Thanks.
db_datareader is enough.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:

> I have a DB and some tables that I'm moving to Sql2005 machine that just get
> query access. People use ACCESS with ODBC link table.
> What permissions other than datareader would allow them to only see USER
> tables?
>
> Thanks.
>
>
|||That's what I gave but when linking the tables the user saw lot of sys
tables also.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...[vbcol=seagreen]
> db_datareader is enough.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
|||Do not worry about those tables, they are SQL Server catalog views.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:

> That's what I gave but when linking the tables the user saw lot of sys
> tables also.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
>
|||Thanks..
Just didn't want to clutter there view of valid tables...
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:410C1840-0476-4606-8BC9-70D2345EC6E2@.microsoft.com...[vbcol=seagreen]
> Do not worry about those tables, they are SQL Server catalog views.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:

Odbc access

I have a DB and some tables that I'm moving to Sql2005 machine that just get
query access. People use ACCESS with ODBC link table.
What permissions other than datareader would allow them to only see USER
tables?
Thanks.db_datareader is enough.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:
> I have a DB and some tables that I'm moving to Sql2005 machine that just get
> query access. People use ACCESS with ODBC link table.
> What permissions other than datareader would allow them to only see USER
> tables?
>
> Thanks.
>
>|||That's what I gave but when linking the tables the user saw lot of sys
tables also.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
> db_datareader is enough.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
>> I have a DB and some tables that I'm moving to Sql2005 machine that just
>> get
>> query access. People use ACCESS with ODBC link table.
>> What permissions other than datareader would allow them to only see USER
>> tables?
>>
>> Thanks.
>>|||Do not worry about those tables, they are SQL Server catalog views.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:
> That's what I gave but when linking the tables the user saw lot of sys
> tables also.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
> >
> > db_datareader is enough.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "AHartman" wrote:
> >
> >> I have a DB and some tables that I'm moving to Sql2005 machine that just
> >> get
> >> query access. People use ACCESS with ODBC link table.
> >> What permissions other than datareader would allow them to only see USER
> >> tables?
> >>
> >>
> >> Thanks.
> >>
> >>
> >>
>|||Thanks..
Just didn't want to clutter there view of valid tables...
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:410C1840-0476-4606-8BC9-70D2345EC6E2@.microsoft.com...
> Do not worry about those tables, they are SQL Server catalog views.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
>> That's what I gave but when linking the tables the user saw lot of sys
>> tables also.
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
>> >
>> > db_datareader is enough.
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "AHartman" wrote:
>> >
>> >> I have a DB and some tables that I'm moving to Sql2005 machine that
>> >> just
>> >> get
>> >> query access. People use ACCESS with ODBC link table.
>> >> What permissions other than datareader would allow them to only see
>> >> USER
>> >> tables?
>> >>
>> >>
>> >> Thanks.
>> >>
>> >>
>> >>
>>

Odbc access

I have a DB and some tables that I'm moving to Sql2005 machine that just get
query access. People use ACCESS with ODBC link table.
What permissions other than datareader would allow them to only see USER
tables?
Thanks.db_datareader is enough.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:

> I have a DB and some tables that I'm moving to Sql2005 machine that just g
et
> query access. People use ACCESS with ODBC link table.
> What permissions other than datareader would allow them to only see USER
> tables?
>
> Thanks.
>
>|||That's what I gave but when linking the tables the user saw lot of sys
tables also.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...[vbcol=seagreen]
> db_datareader is enough.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
>|||Do not worry about those tables, they are SQL Server catalog views.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:

> That's what I gave but when linking the tables the user saw lot of sys
> tables also.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
>|||Thanks..
Just didn't want to clutter there view of valid tables...
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:410C1840-0476-4606-8BC9-70D2345EC6E2@.microsoft.com...[vbcol=seagreen]
> Do not worry about those tables, they are SQL Server catalog views.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
>

ODBC / Oracle Linked Server Problem

I have a SQL server that I am trying to link to a number of Oracle environments. After much tuning, we managed to achieve this although the four-part naming was not possible and we had to use Openquery and run pass throughs.

Nothing in our configuration has changed and SQL Server is no longer able connect to the linked databases. The Oracle client on the PC is fine and is able tnsping any of the remote databases. I am also able to create ODBC connections to the remote databases on the SQL box that are fine.

Using a datalink in DTS, I can connect to the remote databases. This suggests to me that there is something wrong within the actual database links. I have set them up using the working ODBC DSN's on the SQL box.

If I try and run a query against them in Query Analyser, I get the following error message :

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

If I click on the tables icon in EM to view the remote catalogues I get the following error :

Error 7399: OLE DB provider 'MSDORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

Any help that could be give on this would be greatly appreciated.Can you post the script(s) that you used to create the linked servers on your SQL Server?

Also, I think it would be helpful if you could post the content of the tnsname.ora file.

regards,

hmscott|||Hi, it appears the problem lies somewhere in the Oracle client on the server or the NT build. We have successfully managed to get the links up and alive by bouncing the server once a day, stopping and restrating the SQL Services and refreshing the login details.

We are currently investigating as to whether it not it could be related to the connections created by Terminal Services.


Originally posted by hmscott
Can you post the script(s) that you used to create the linked servers on your SQL Server?

Also, I think it would be helpful if you could post the content of the tnsname.ora file.

regards,

hmscott|||I'm ware of the errmsg "Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name
] "
I think the problem is that u haven't configure sql*net correctly.
See the tnsnames.ora in %ORACLE_HOME%/network/admin.

ODBC

When trying to open a link from the main Web Page of
TimberHunt I get the following information:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error
converting data type varchar to numeric.
/timber_trade/comp_trade_board.asp, line 60
Anyone available to help solve the problem?
Will appreciate your support.
RicardoHello,
<%
set conn1=server.CreateObject("adodb.connection")
conn1.cursorlocation=3
conn1.Open "Provider=sqloledb;" & _
"Data Source=ayaz;" & _
"Initial Catalog=ayaz;" & _
"User Id=ayaz;" & _
"Password=ayaz"
%>
try this.
Warm Regards,
Ayaz Ahmed
Software Engineer & Web Developer
Creative Chaos (Pvt.) Ltd.
"Managing Your Digital Risk"
http://www.csquareonline.com
Karachi, Pakistan
Mobile +92 300 2280950
Office +92 21 455 2414
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!