locked
Microsoft OLE DB Provider for ODBC connectivity failed RRS feed

  • 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

        

    Monday, October 31, 2016 7:40 AM

Answers

  • 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 :-)


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

    Monday, October 31, 2016 8:22 AM
  • 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


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

    Monday, October 31, 2016 9:46 AM
  • 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 :-)

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


    Monday, October 31, 2016 9:51 AM

All replies

  • 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 :-)


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

    Monday, October 31, 2016 8:22 AM
  • 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

    Monday, October 31, 2016 8:55 AM
  • 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.

    Monday, October 31, 2016 9:37 AM
  • 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


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

    Monday, October 31, 2016 9:46 AM
  • 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 :-)

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


    Monday, October 31, 2016 9:51 AM