SQL 2005 - OLE DB Provider for ODBC???

How do I install OLE DB Provider for ODBC in SQL 2005?  Its not in there by default.  There's a .Net ODBC Data Source option.  Is the .Net the same as OLE DB?
LVL 4
abbdanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
.NET includes data providers for both OLE DB and ODBC but they are different things.  
0
abbdanAuthor Commented:
So if I use an ODBC DSN name I shouldn't have to fill out any other fields?  Also I'm trying to select a Merant 3.60 drivers DSN.. I'm getting connection errors when I use dsn name.
0
dqmqCommented:
Well, the DSN needs to be configured properly, of course. And you need to use the corresponding object types.  For example, you would use ODBCConnection, not OLEDBConnection or SQLConnection.

What error are you getting?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

abbdanAuthor Commented:
In SQL 2005 if you attempt to import data from an ODBC data source the only ODBC data source available is .net which is first in the drop down list.  I've selected that and I put a DSN name that I can connect using the ODBC Administrator.  Its not connecting.  I was thinking I would use the same thing I used in SQL 2000 which was the Data Source "OLE DB Provider for ODBC" and select it from the drop box.  Its not listed.  I was wondering if it could be installed or is there another way to connect to my DSN in ODBC.
0
abbdanAuthor Commented:
The error I get is "Insufficient information for a connection..."
0
nakul_vachhrajaniCommented:
This might be of some help:
http://msdn2.microsoft.com/en-us/library/ms141696.aspx

The article does contain some information about configuring OLE DB data sources and stuff like that.

The OLE DB provider is bundled with the Feature Pack at:
http://www.microsoft.com/downloads/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17&DisplayLang=en
0
datamasonsCommented:
You don't need a specific one for SQL Server 2005.  You can just use the SQL Server driver.  

0
abbdanAuthor Commented:
I cannot use the SQL Server Driver if I'm trying to connect to a PROGRESS database using a Merant 3.6 or 3.7 ODBC driver.  I cannot use .Net ODBC for Merant ODBC drivers either.  Any other solutions?
0
abbdanAuthor Commented:
I cannot use OLE DB when I depend on ODBC since PROGRESS only has an ODBC driver.  I had hoped that I could have configured OLE DB to host an ODBC configuration but that doesn't seem to work either.
0
datamasonsCommented:
How are you actually trying to access your data?  vb.net?  If so, you can use various types of connection methods in .NET

SQLConnection
OleDbConnection
OdbcConnection

Those are all data connection objects in .NET and are easy to configure and assign to a data reader or dataset.


SQL
0
abbdanAuthor Commented:
When you import data into an SQL 2005 database you initially select the source database and the destination database.  If my source database is connectable only through an ODBC driver I would need to use a data source that included an SQL 2005 connection object that was compatable with utilizing an ODBC connection.  I have tried using the ODBC.net connection object in SQL2005 and it does not work.  I know the ODBC driver is configured correctly because I can link to Progress tables in an Access database with no problems.  My thinking was that I needed a connection object like the ODBC connection object that is an option in SQL 2000, however, that object is not available in 2005.  My question probably should have been how do I establish a connection to a Merant 3.6 driver in SQL 2005 but I didn't think too many people have had experience with Progress and Merant ODBC drivers.  In my opinion it doesn't matter what you call it, as long as I can get information on something that will work.  Is there an ODBC connection object similar to the SQL 2000 version that can be implemented in SQL 2005 version or am I stuck with the .net version that has failed me?
0
abbdanAuthor Commented:
I haven't abandoned the question.  I'm waiting on an answer but I don't think I can get one.  You should close this question if you don't think its going to be answered, but it has not been abandoned be me anyway.
0
abbdanAuthor Commented:
No solution.. please close
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
moosetrackerCommented:
Way.. Way late.. I was just surfing and came across this. But..
A. was the ODBC on your desktop, or on your server. Even though you are sitting at your desktop you are using the server and the ODBC must be configured on the server.. We had a merant driver for a different DB and it would not give you license to put in on the server.. We had to find a different provider for a Server Side driver.
B. that might do it for you, or you can look into a linked server to your data.. For an ODBC you would use the "Microsoft OLE DB Provider for ODBC"
 http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx

Now back to my surfing.. I myself am on a hunt for information about a server side Provider for Progress... But hopefully OLE DB, ODBC can be limited & quirky, OLE DB most times makes the DB act like you are working with true SQL data..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.