none
MSDASQL and Oracle / SERVER=SHARED RRS feed

  • Question

  • Hello,

    I'm using Microsoft Office 2013 on a Windows 7 platform.  I am also using Oracle XE 11g on another Windows 7 platform. 

    I have a macro in Excel that runs on a regular basis to load a small amount of data into Oracle tables.  It worked fine for years.

    Recently, for another reason, I had to update TNSNAMES.ORA on both the Windows client and the Oracle server.  I had to change the (SERVER = DEDICATED) setting to (SERVER = SHARED).

    Since I made that change, the macro no longer works.  If I change TNSNAMES.ORA back to DEDICATED on the client, all is well again, but my other new functions fail. 

    I get the following error from Microsoft VBA when I Open the connection from the client:

    [Oracle][ODBC][Ora]ORA-12520: TNS Listener could not find available handler for requested type of server.

    The relevant part of the VBA code is:

    Dim objConnection As ADODB.Connection
    Dim objRecordSet As ADODB.Recordset
    Dim strConnection As String

    Set objConnection = New ADODB.Connection
    Set objRecordSet = New ADODB.Recordset
     
    strConnection = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Server1;Password=ffffff"
    objConnection.ConnectionString = strConnection
     
     'Open the database connection
    objConnection.Open

    Is there anything different I have to do on the Microsoft end to accommodate for the SERVER = SHARED option?

    Thanks for looking at this.

    Phil


    • Edited by Budget Programmer Sunday, September 21, 2014 10:18 PM
    • Moved by George Hua Wednesday, September 24, 2014 2:46 AM Moved from Excel for developers forum
    Sunday, September 21, 2014 10:15 PM

Answers

  • Hi George.  Thanks for the reply.  In the meantime I found an answer.  It seems that Microsoft's ODBC Data Source Administrator tool using the Oracle ODBC Driver will only work with the TNSNAMES.ORA / SERVER=DEDICATED option and will not work when the SERVER=SHARED option is used. I don't know why the ODBC driver won't work with Oracle's SERVER=SHARED option.

    In order to solve this dilemma I had to change both Oracle and Microsoft settings.  On my Windows client, where the Excel Macros execute, I edited the TNSNAMES.ORA file by adding the section XE_MSOFFICE.  That was a copy of the original section called XE.  As you can see, the XE section has the SERVER=SHARED and the XE_MSOFFICE section has the SERVER=DEDICATED.

    Then I used the Microsoft ODBC Data Source Administrator tool and in the USER DSN tab I reconfigured the TNS Service Name field  to the new XE_MSOFFICE.  I tested all functions and they all work perfectly now.  Here's a copy of the TNSNAMES.ORA file. 

    XE =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = server_address)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = SHARED)
          (SERVICE_NAME = XE)
        )
      )

    XE_MSOFFICE =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = server_address)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = XE)
        )
      )

    EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
        )
        (CONNECT_DATA =
          (SID = PLSExtProc)
          (PRESENTATION = RO)
        )
      )

    ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
        )
        (CONNECT_DATA =
          (SID = CLRExtProc)
          (PRESENTATION = RO)
        )
      )

    Wednesday, September 24, 2014 4:04 AM

All replies

  • Hi Phil,

    >>I had to update TNSNAMES.ORA on both the Windows client and the Oracle server.  I had to change the (SERVER = DEDICATED) setting to (SERVER = SHARED).<<

    According to your description, you have made some changes with Oracle and you can't connect to Oracle database through VBA.

    I'm not familiar with Oracle, I think you need to get support from Oracle related forum to check whether we can connect to such server if we make such changes.

    Here is the article for Oracle connection string for your reference:

    Oracle connection strings

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 24, 2014 2:54 AM
  • Hi George.  Thanks for the reply.  In the meantime I found an answer.  It seems that Microsoft's ODBC Data Source Administrator tool using the Oracle ODBC Driver will only work with the TNSNAMES.ORA / SERVER=DEDICATED option and will not work when the SERVER=SHARED option is used. I don't know why the ODBC driver won't work with Oracle's SERVER=SHARED option.

    In order to solve this dilemma I had to change both Oracle and Microsoft settings.  On my Windows client, where the Excel Macros execute, I edited the TNSNAMES.ORA file by adding the section XE_MSOFFICE.  That was a copy of the original section called XE.  As you can see, the XE section has the SERVER=SHARED and the XE_MSOFFICE section has the SERVER=DEDICATED.

    Then I used the Microsoft ODBC Data Source Administrator tool and in the USER DSN tab I reconfigured the TNS Service Name field  to the new XE_MSOFFICE.  I tested all functions and they all work perfectly now.  Here's a copy of the TNSNAMES.ORA file. 

    XE =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = server_address)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = SHARED)
          (SERVICE_NAME = XE)
        )
      )

    XE_MSOFFICE =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = server_address)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = XE)
        )
      )

    EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
        )
        (CONNECT_DATA =
          (SID = PLSExtProc)
          (PRESENTATION = RO)
        )
      )

    ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
        )
        (CONNECT_DATA =
          (SID = CLRExtProc)
          (PRESENTATION = RO)
        )
      )

    Wednesday, September 24, 2014 4:04 AM