Oracle Error when connecting from PowerPivot for Excel 2010 “Connection not open”
Today we got this error when using 32-bit Excel and the PowerPivot addin to connect to Oracle Express.
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
Dir C:\PROGRA~2 /x
-- Look for Microsoft Office short name. Mine is MICROS~4 but yours may be a different number or abbreviation.
Dir C:\PROGRA~2\MICROS~4\ /x
-- Look for Office14 (short name is same as long name)
Dir C:\PROGRA~2\MICROS~4\Office14 /x
-- Look for Excel.exe (short name is same as long name)
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.
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 removedAnonymous
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? -JasonAnonymous
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 CAnonymous
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, CAnonymous
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.