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]

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]

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]

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.

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]

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]

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]

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]

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]

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]

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...