locked
Missing rows when using lots of columns in sql query (no error msg) RRS feed

  • Question

  • My first post in this forum. We've experienced some strange behavious in Powerpivot and thought I'd try to see if someone here has experienced similar problems.

     

    We use PowerPivot with Sybase ASE 15 (Very similar to MS SQL Server) using Sybase own OLEDB provider. I've experienced this problem on two computers.

    Computer #1: Win XP 32-bit, 2 Gb RAM, Office 2010 pro 32-bit, Sybase 32-bit OLEDB provider

    Computer #2: Win 7 64-bit, 4 Gb RAM, Office 2010 pro 64-bit, Sybase 64-bit OLEDB provider

     

    I'm trying to extract around 3.5 million rows of name and adress data (i.e mostly string/char data), each row has 24 columns.

     

    If I either choose "select * from ..." or choose to import the whole database view, I get approximately 7000 rows. If I choose maybe 15 of the 24 columns, I get the full 3.5 million rows.

     

    I've never any seen any kind of error message (i.e out-of-memory or similar) which I've read blog posts about. I've also looked in the windows sys log without any luck. I'm thinking that it's either a memory problem somewhere or there some data in column that either PowerPivot or the driver does'nt like (although this seems unlikely).

     

    Any suggestions?

     

    Regards,

    Martin

    Monday, November 1, 2010 5:13 PM

Answers

  • UPDATE

     

    I've done some troubleshooting and I finally found the most likely problem with my query. There were some incorrect data in database which results in the following error:

    "Subquery returned more than 1 value.  This is illegal when the subquery follows =, !=, <, <= , >, >=, or when the subquery is used as an expression.
    Command has been aborted."

     

    Unfortunately this information wasn't recognized by either PowerPivot or the OLEDB provider. I thought I'd post the solution here, might help someone else in the future.

    Tuesday, November 2, 2010 4:48 PM