Recently we have been seeing some users complain about how the PowerPivot Excel client add-in deals with queries. There are three limitations that you need deal with when working with SQL (TSQL, PL/SQL, or whatever) and stored procedures:
- The result set must have a name associated with each column. If you are not returning the column name, then you must assign an SQL alias to the column. Some query tools allow you to work with ordinal numbers for the columns; some query tools assign a name based on the ordinal (e.g. COLUMN_42); some query tools use a combination of the two. The add-in (and the SSAS engine) does not not – it directly binds to the column name.
- You cannot have duplicate column names. Each column name must be unique.
- You cannot return multiple rowsets. The client add-in only deals with a single rowset. Some tools allow you to return multiple rowsets but only deal with first one; or the last one. In the client add-in, you must have only a single return rowset. If you are entering queries via SQL then this is typically not a problem – but it has serious complications if you are using stored procedures. If you don’t use “SET NOCOUNT ON” at the beginning of your stored procedure, then a rowset is returned for each SELECT expression, even if that expression is just setting a variable. Thus you must have a “SET NOCOUNT NO” at the start of your stored procedure or the stored procedures are likely to fail with the client add-in.
I am just sharing the love here . . . the end result is that you might not be able to use all of your existing queries and stored procedures with the PowerPivot Excel client add-in. Being for-warned is for-armed.
And “Oh…” another possibility issue that you might run into is support for variant datatype. The rowset previewer can handle variant data — but the Engine cannot.
Enjoy.
Hi Dave,
The statement to put at the start of your application has to be “SET NOCOUNT OFF”.
Kasper
Hi, I really need some help here.
I am currently using Windows XP Pro 2002 SP3 . Testing on Power Pivot Excel add-in.
Power Pivot Data connections via Data Source (OLE DB/ODBC).
And running Interbase 2009 database.
Currently I encounter an error message shown as below when clicking on Preview & Filter button after check mark a table in Table Import Wizard:-
OLE DB or ODBC error: [DataDirect][ODBC InterBase driver][InterBase]Dynamic SQL Error, SQL error code = -104, Token unknown – line 1, char 7, [; 37000.
An error occurred while processing the ‘PROCORD1′ table.
The operation has been cancelled.
The fact is that all other tables also facing the same issue when I click on Preview & Filter button.
However,I was able to write a query to specify the data to import and proceed to create Pivot Charts and tables.
Questions
1) Do you think this is related to the ODBC driver that I’m currently using ? If no what is the other most possible reasons?
2)Is the ODBC Driver- Data Direct Version 05.10.0044 (B0099,U0055) , File Date:10/17/2007 that I’m using compatible with Microsoft Power Pivot?
3)Usually what does this SQL error means “Dynamic SQL Error, SQL error code = -104″ ?
Hi Lewis:
WOW. I have no idea. I hope that one of the other readers will be able to answer.
I can tell you a few things:
1 — From what I know, this is likely an issue with the SQL statement that we generate. I am not aware of any issues like this.
2 — We do not test 3rd party providers such as Data Direct. You might contact their customer support. To get the exact SQL statement issued, you can use ODBC tracing. Or you could temporarly add the table to a SQL Server database and then use SQL Profiling to get the SQL statement.
3 — Error returns are dependent on the ODBC provider.
_-_-_ Dave
[...] Continue reading Querying data within the PowerPivot Excel client add-in [...]