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



## xsil (Aug 12, 2013)

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 (Aug 18, 2013)

Hi there,

I work for DataDirect - the folks who make the OpenAccess SDK that's giving you that error. What data source are you actually connecting to?


----------



## xsil (Aug 19, 2013)

Hi nashguy. Any relation to Bridges?     Re - the data source.  It is a 'ServiceNow' database, trying to connect to it remotely.   What else can I tell you about that....?

To further complicate things, now the Excel worksheet connection (which I previously reported as "working") is also problematic.  I am presented with the following error message on *some* queries that previously worked:     [DataDirect][ODBC Open Access SDK Driver][Open Access SQL engine]Syntax Error in the SQL statement.   I did not change the syntax or location of these query files so I'm still a bit baffled as to what might have caused this.  How can the same SQL code be fine one day, and another day it has "Syntax Errors"?   

My client suggested I may have "some firewall settings" - not sure if those would have anything to do with anything.  I checked my firewall but didn't see any "smoking gun"... then again, I wouldn't know what that would look like in this case either.  

Any help you could provide would be greatly appreciated.


----------



## nashguy (Aug 19, 2013)

xsil said:


> Hi nashguy. Any relation to Bridges?     Re - the data source.  It is a 'ServiceNow' database, trying to connect to it remotely.   What else can I tell you about that....?
> 
> To further complicate things, now the Excel worksheet connection (which I previously reported as "working") is also problematic.  I am presented with the following error message on *some* queries that previously worked:     [DataDirect][ODBC Open Access SDK Driver][Open Access SQL engine]Syntax Error in the SQL statement.   I did not change the syntax or location of these query files so I'm still a bit baffled as to what might have caused this.  How can the same SQL code be fine one day, and another day it has "Syntax Errors"?
> 
> ...



The ServiceNow aspect helps - to give you perhaps more background than you'd like, the OpenAccess SDK is a toolkit used by companies to build SQL interfaces to their unique data sources. So ServiceNow has built an ODBC driver to allow their customers to talk to their ServiceNow data as though it were another relational database. 

While I believe that the way they've built their integration is inherently firewall-friendly (you shouldn't have to muck with any firewall issues), could there be a proxy server on the network? 

And a likely frustrating part of this is, the functionality of their driver depends on their custom integration. We supplied them with an SDK - they built the final driver. 

Have you checked any of the ServiceNow forums? This one seems pretty good.  Has the client tried getting any resolution from ServiceNow? 

Last diagnostic questions - 1. do you have any other ODBC applications that you could test with? 2. Which version of Excel are you using? 

We'll get it figured out...

Adding this - check out this blog post. it details ODBC use with PowerPivot. It's not the same ODBC driver, but perhaps something in the author's steps will resonate?


----------



## xsil (Aug 19, 2013)

Thanks again.  FYI - looks like you left off the link to the blog post you mentioned...?

Regarding your questions:

*  Yes -I did look at the SN forums but didn't see anything of interest there yet, may be posting there soon.  Or client will.  They haveo mentioned they are looking into, but I'm trying to continue & be proactive, so I don't have to be in perpetual "hurry up and wait" mode  

* Proxy server on the network?  No idea.  How would I check that?

* Re other ODBC applications:  Not much recent history to report on other applications using my current configuration.  The only thing that has never been problematic is connecting to locally available source files, eg. Excel, CSV, Access etc.  

* I am using Excel 2010.


----------



## nashguy (Aug 19, 2013)

If you click on the word "blog", it will take you there. Not sure why it's so light and not underlined.

Try WinSQL as another simple ODBC tool we can debug with. They have trial and light versions. 

The more I think about it, let's defer worries on proxy server. 

"Test Connection" is still working ok?


----------



## xsil (Aug 19, 2013)

Ah yes, sneaky link.  The method illustrated on that blog post is almost identical to the one I tried; the only difference being in Step #5, I chose the first option, 'Use the data source name', which revealed the list of my installed DSNs.  I got as far as clicking the 'Test Connection' button, which worked; I get a 'Test Connection Succeeded' message.  But then at the next 'Next' buttton I am still getting the original error message (from my first post on this thread).  So yes, 'Test Connection' still working.

I've also just installed the trial version of WinSQL.   Now what?

Thanks!!!


----------



## nashguy (Aug 19, 2013)

With WinSQL, try to establish a new connection (File/New Connection) using the same ODBC driver info that you were trying to use with Excel. See if you can successfully connect, and if not, what kind of errors crop up.


----------



## xsil (Aug 19, 2013)

OK, still no go:

"using the same ODBC driver info that you were trying to use with Excel"  ~ Couldn't do that. The drop down list under 'Data Source name' only pulls up "User DSNs".. the one I need is in the "System DSN" category.. 

So then I check 'Specify connection string' and I try manually typing in the DSN info, click OK, which then leads to an error message:  

SQL Error State: IM014, Native Error Code: 0, ODBC Error: [Microsoft][ODBC Driver Manager] The specified DSN contains and architecture mismatch between the Driver and the Application 

Hmmm. I thought, this is starting to sound like a 32/64 bit issue.  I wonder.   So I click the 'ODBC Mgr.'  button - First thing I notice, it says "ODBC Data Source administrator (32-bit)" in the title bar.  And I do need 64-bit!   Is this WinSQL thing a 32-bit only program??  From what I can deduce from the Synametrics website it is.   Which means, we need a new experiment.

Thanks for your continued support.  I did not imagine this would be so hard!


----------



## nashguy (Aug 19, 2013)

Should've touched on the 32/64 bit issue before. Apologies. You're correct that WinSQL is a 32-bit app. Finding an equally available 64-bit app will require some thought on my part.

To make sure we're all working under the same assumptions - is your copy of Excel 2010 a 64-bit copy? 

Where did you get the ODBC driver for ServiceNow? I believe that there have been some 32/64 bit challenges with that driver before.  

Patience is a virtue...


----------



## xsil (Aug 12, 2013)

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 (Aug 19, 2013)

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


----------



## xsil (Aug 19, 2013)

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 (Aug 19, 2013)

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 (Mar 8, 2014)

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 (Mar 4, 2015)

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


----------



## scottsen (Mar 5, 2015)

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


----------

