Microsoft OLE DB Provider for ODBC connectivity failed

Question

Hi,

  I have installed Oracle 11gr2 client and configured the ODBC to access the Oracle Database from sql server 2008.

  Linked server creation failed with the following error.

Error Details:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DMTEST".
OLE DB provider "MSDASQL" for linked server "DMTEST" returned message "[Microsoft][ODBC Driver Manager] Invalid connection string attribute".

OLE DB provider "MSDASQL" for linked server "DMTEST" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)
   

     Kindly provide the solution for this issue.

Regards

Sheik MD

    

Answer 1

Hi,

  I have installed Oracle 11gr2 client and configured the ODBC to access the Oracle Database from sql server 2008.

  Linked server creation failed with the following error.

Error Details:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DMTEST".
OLE DB provider "MSDASQL" for linked server "DMTEST" returned message "[Microsoft][ODBC Driver Manager] Invalid connection string attribute".

OLE DB provider "MSDASQL" for linked server "DMTEST" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)
   

     Kindly provide the solution for this issue.

Regards

Sheik MD

    

Good day 

As the message clearly say Invalid connection string attribute which mean the issue related to the Oracle (provider) connection string more then to the SQL Server. Basically in order to connect to Oracle you need to use the Oracle connection provider which is not installed by default (unless you have oracle on the same machine). I understand that you insttaled it but...

>> I have installed Oracle 11gr2 client and configured the ODBC to access the Oracle Database from sql server 2008.

We have no idea of what is your linked server configuration :-(
Please post the connection string, or the exeact way you created the linked server.

* Linked server to oracle should look like something like this

sp_addlinkedserver 'OraDB', 'Oracle', 'MSDAORA', 'Ora817'
sp_addlinkedserver 'OraDB', 'Oracle', 'OraOLEDB.Oracle', 'Ora817'

Without any more information, my guess is that your linked server do not use the right provider.

* The code above taken from this article which should give you moire information on what you need to do. Another good article can be seen here. and here is another one which can help you.

If you still have an issue after following the tutorials, please give us more information regarding you linked server configuration :-)

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 2

Hi Rolen,

   I have tried to create the Linked Server in SQL Server Management Studio Express 2008.

   Passed the arguments as:

    Linked Server : DMTEST

    Provider : Microsoft OLE DB Provider for ODBC Drivers.

    Data Source : DMDS

    Product : Oracle

Regards

Sheik MD

Answer 3

http://stackoverflow.com/questions/20470237/linked-server-data-source-name-not-found-and-no-default-driver-specified-error

---

Please click Mark As Answer if my post helped.

Answer 4

Hi Rolen,

   I have tried to create the Linked Server in SQL Server Management Studio Express 2008.

   Passed the arguments as:

    Linked Server : DMTEST

    Provider : Microsoft OLE DB Provider for ODBC Drivers.

    Data Source : DMDS

    Product : Oracle

Regards

Sheik MD

Hi,

As I said, you are using wrong provider, while trying to connect Oracle. Please read the tutorials I posted above and follow the instructions. Once you installed the Oracle provider you should use it :-)

The Oracle provider name is "Oracle Provider For Ole DB"

I posted three links for three tutorial which all have the solution for you. Please read them again

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 5

By the way, using a wrong provider, or one that is not specific for the specific database type might lead to situation that some queries works and other do not. Part of the provider job after the connection is to convert the queries to fitting format, Moreover In some cases the provider also in charge of other complex stuff like security encrypt and decrypt as we have in SQL Server 2016 always encrypted.... always try to find the best provider for the the specific database :-)

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]