Oracle Error when connecting from PowerPivot for Excel 2010 “Connection not open”

  • Article

Today we got this error when using 32-bit Excel and the PowerPivot addin to connect to Oracle Express.

clip_image002

 

The full stack of the error appears when you expand the [Details >> ] button

Error Message:
============================
ORA-06413: Connection not open.
----------------------------
Failed to connect to the server. Reason: ORA-06413: Connection not open.
============================
Call Stack:
============================
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.InitializeConnectionObject(String connectionIdentifier)
----------------------------
   at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.InitializeConnectionObject(String connectionIdentifier)
   at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.Open(String& connectionIdentifier)
   at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.Open()
   at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.TestConnection()
   at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.ClickTestConnection(Object progressControl)

 

There is also a potential secondary error

ORA-12154: TNS:could not resolve the connect identifier specified

 

 

We found out this is a very well known issue in the community with the Oracle OLEDB provider.

SSIS users launching 32-bit DTExec.exe or the DTSWizard.exe have probably known this for years, but PowerPivot for Excel 2010 users may be seeing this for the first time.

The cause is When the path of the application which launches the connection contains parenthesis, the provider fails to connect.

When using 32-bit Excel on a 64-bit machine, the 32-bit WOW program files folder has parenthesis in it

C:\Program Files (x86)\Microsoft Office\Office14\Excel.exe

1. Quick and Dirty Workaround:

To work around this is very easy… find the equivalent 8.3 short naming convention for Excel.exe, and use that to launch Excel. Build yourself a shortcut from the short-named path.

With this approach the Oracle provider won’t have any trouble from what we’ve seen. Each system’s folder names can be different so you may need to check your own folders to find the right ~1 ~2 ~3 suffix when there are duplicate short folder names.

For example, mine is:   C:\PROGRA~2\MICROS~4\Office14\Excel.exe

Here is how I determined the short name… to explore your own short folder names, use this approach on the drive letter where Office is installed.

Start > Run > Cmd.exe  (or Start > CMD.exe if you don’t have a Run box)

Dir C:\ /x

-- look for Program Files (x86) short name

image

Dir C:\PROGRA~2 /x
-- Look for Microsoft Office short name. Mine is MICROS~4 but yours may be a different number or abbreviation.

image

Dir C:\PROGRA~2\MICROS~4\ /x 
-- Look for Office14 (short name is same as long name)

image

Dir C:\PROGRA~2\MICROS~4\Office14 /x 
-- Look for Excel.exe (short name is same as long name)

image

Therefore, my short-name to launch Excel can be:

C:\PROGRA~2\MICROS~4\Office14\Excel.exe

To make this easy to remember next time, take the short path that you found, copy paste it, and right click on your desktop or start menu to make a new shortcut.

image image  image

Of course this is just a workaround, but this is easy enough, and maybe even easier than the alternatives….

When you install Office, pick a non-default folder that doesn’t contain parenthesis. That’s probably not a good option for you, since you already have Office 2010 installed!

2. Long Term – The Real Solution:

Contact Oracle to get the patches for the client drivers which eliminates this problem.

Oracle Number 3807408 CANNOT EXTERNALLY AUTHENTICATE USER WITH QUOTE IN USERNAME
https://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=3807408
This fix requires that both the client and database software be patched.

The patch is available only for the currently certified versions (9.2.0.7, 10.2.0.1) and not on lower versions such as (9.2.0.4). There is a secondary patch for the client machine (machine where PowerPivot connects from.

4928723 (Description: ORACLE 9I 9.2.0.7 PATCH 6 ON WINDOWS 32 BIT) 
4928724 (Description: ORACLE 9I 9.2.0.7 PATCH 6 FOR WINDOWS (64 BIT) ) 

 

Technorati Tags: PowerPivot,Excel 2010,ErrorMessage,Oracle Connectivity