Solved

SQL 2005 - OLE DB Provider for ODBC???

Posted on 2007-03-30
16
4,929 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
ID: 18825813
.NET includes data providers for both OLE DB and ODBC but they are different things.  
0
 
LVL 4

Author Comment

by:abbdan
ID: 18825844
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
ID: 18825919
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 4

Author Comment

by:abbdan
ID: 18826010
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
ID: 18826017
The error I get is "Insufficient information for a connection..."
0
 
LVL 5

Expert Comment

by:nakul_vachhrajani
ID: 18848377
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
ID: 18954364
You don't need a specific one for SQL Server 2005.  You can just use the SQL Server driver.  

0
 
LVL 4

Author Comment

by:abbdan
ID: 18970902
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
ID: 18970909
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
ID: 18973671
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
ID: 18978906
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
ID: 20599751
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
ID: 22141935
No solution.. please close
0
 
LVL 1

Expert Comment

by:moosetracker
ID: 25845924
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

808 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