Partilhar via

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

  • Artigo

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

Comments

  • Anonymous
    June 28, 2011
    The comment has been removed

  • Anonymous
    June 28, 2011
    Hi ChrisP, I don't use Oracle a whole lot, so I'm not sure. I tried to log in as a test, but it gave "503 Service Unavailable" so I'm not sure if its a temporary error, or if it means the link is no longer good. Will try again later on. Did the workaround of providing an Excel shortcut with short names help at all? -Jason

  • Anonymous
    June 29, 2011
    Hi Jason, thanks for your quick reply. I tried your 1st solution and it worked for sql environment but it still gave me the same result for oracle databases. Is it worth reinstalling MSoffice in C:..Program Files instead of C:..Program Files(x86). I got in touch with oracle and the patches are only available to customers who purchased customer support. Cheers, C C

  • Anonymous
    September 20, 2011
    Hi Jason, Would it be possible to email me these patches? I dont have a CSI number that would allow me to download it from oracle website. Cheers, C

  • Anonymous
    September 20, 2011
    Sorry, I just realized you already answered that question.

  • Anonymous
    February 24, 2015
    Wow is all I can say.  I would never have figured this out and thank you so much for the post.  Worked perfectly.