Solved

SQL 2005 - OLE DB Provider for ODBC???

Posted on 2007-03-30
16
4,924 Views
Last Modified: 2013-11-25
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?
0
Comment
Question by:abbdan
  • 8
  • 2
  • 2
  • +2
16 Comments
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
.NET includes data providers for both OLE DB and ODBC but they are different things.  
0
 
LVL 4

Author Comment

by:abbdan
Comment Utility
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
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
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
 
LVL 4

Author Comment

by:abbdan
Comment Utility
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
 
LVL 4

Author Comment

by:abbdan
Comment Utility
The error I get is "Insufficient information for a connection..."
0
 
LVL 5

Expert Comment

by:nakul_vachhrajani
Comment Utility
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
 
LVL 1

Expert Comment

by:datamasons
Comment Utility
You don't need a specific one for SQL Server 2005.  You can just use the SQL Server driver.  

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 4

Author Comment

by:abbdan
Comment Utility
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
 
LVL 4

Author Comment

by:abbdan
Comment Utility
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
 
LVL 1

Expert Comment

by:datamasons
Comment Utility
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
 
LVL 4

Author Comment

by:abbdan
Comment Utility
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
 
LVL 4

Author Comment

by:abbdan
Comment Utility
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
 
LVL 4

Accepted Solution

by:
abbdan earned 0 total points
Comment Utility
No solution.. please close
0
 
LVL 1

Expert Comment

by:moosetracker
Comment Utility
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now