Power Pivot not liking the same ODBC connection that worked just fine in Excel

[xsil]

Hello Power Pivot fans

Attempting to import some data into Power Pivot using an ODBC connection. I was able to set up the queries in an Excel worksheet and they work just fine. Now trying to 'migrate' the project to Power Pivot, using the exact same connection logic, and the process fails.

Here is what I did: In Power Pivot, I start with Get External Data > From Other Sources > Others (OLEDB / ODBC). Click 'Next', Click 'Build'. So far so good. I choose '..ODBC Drivers' in the Provider tab, then click the 'Connection' tab. I see that my list of data sources show up nicely in the dropdown under 'Use data source name'. Still good. Then I enter credentials, and a promising "Test connection succeeded" message appears when I click the 'Test Connect' button.

But after that point, things become problematic. Clicking 'Next' on the Table Import Wizard reveals this very mean looking error message that reads:

Failed to connect to the server. Reason: ERROR [28000] [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK Service]Required user name is missing.
ERROR [01S00] [DataDirect][ODBC OpenAccess SDK driver]Invalid attribute in connection string: User ID.
ERROR [01S00] [DataDirect][ODBC OpenAccess SDK driver]Invalid attribute in connection string: Initial Catalog.
ERROR [01S00] [DataDirect][ODBC OpenAccess SDK driver]Invalid attribute in connection string: Persist Security Info.
ERROR [01S00] [DataDirect][ODBC OpenAccess SDK driver]Invalid attribute in connection string: Persist Security Info.
ERROR [01S00] [DataDirect][ODBC OpenAccess SDK driver]Invalid attribute in connection string: User ID.
ERROR [01S00] [DataDirect][ODBC OpenAccess SDK driver]Invalid attribute in connection string: Initial Catalog.
ERROR [28000] [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK Service]Required user name is missing.


So many things not clear. Invalid attribute? Why? It worked so easily in the Excel worksheet, but Power Pivot seems much pickier.

Thanks in advance for your wisdom.

 

[nashguy]

Sorry - meant to also ask which version of Windows are you running on?

 

[xsil]

Patience is a virtue, time is a commodity...

Yes, I have Excel 2010, 64 bit.

I installed the ServiceNow driver from the ServiceNow website. They have downloads for both 64 and 32 bit and I am sure I have the right one since I have had a few successful connections at least.
Which reminds me, I think that fact bears repeating here: I am able to connect using Excel some of the time - same driver, same DSN. IF this were stricly a 64/32 bit issue, I wouldn't expect the connection to work in Excel, ever. Yet it does, some of the time. (Power Pivot doesn't, ever.. but that's a different story. Or is it? ). So I am not sure it would be a valuable pursuit to test some other 64-bit app as you suggest..?

Do you have any more suggestions for me? Or is it time to start pestering the ServiceNow team?

Thanks.

 

[nashguy]

Well, the fact that your success is intermittent is just very odd. The hope with some other app is that perhaps we'd be able to find something that could connect all the time. The "Test Connect" always works, though?

You're right that if it were a 32/64 bit mismatch, it would never work.

And it would be better to solve getting Excel working reliably, before we attempt to debug/repair getting Power Pivot working with ServiceNow.

I would get ServiceNow involved - it is their code, when it comes down to it.

Sorry - I will try to bounce this past some coworkers, too, to see if they have any additional suggestions.

 

[SabineV]

Hey guys,

maybe I've found a solution for your problem. This blog post could help you:
PowerPivot & ODBC & SAP HANA

The author connect PowerPivot with ODBC to get data from his SAP HANA instance.
Sorry, if it's wrong, but i think it's useful.

 

[Maxxwake]

Hello, I am having this exact same problem, was this issue ever resolved? If yes what is the fix.

 

[scottsen]

I know *nothing* of this stuff, but I know what I would try first... using Power Query.