OPENROWSET Query limit

Question

OPENROWSET Query limit

Don Shields-368041

Ten Centuries

Points: 1189
More actions
April 22, 2010 at 12:01 pm

#231974

I am using a select from OPENROWSET in SQL Server 2005 which returns records from MSOLAP provider. I am executing an MDX query on SSAS 2008. As log as the query returns < 5000 rows it runs fine but otherwise I get this error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
How can I increase the limit? When I run select count(*) from openrowset ..... it tells me that there are 18025 rows.
Thanks,
Don Shields

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

I don't believe there's a limit on OPENROWSET. More likely, there's some error in the data especially if the data is actually coming from a file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 2

The data is coming from MSOLAP (ssas 2008). When I change the filter so that less than 5000 rows are returned it works fine. Anything overthat and I get the error.

Don

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 3

Perhaps I should change the subject to "MSOLAP row limit problem". I ran an openrowset query using SQLOLEDB and I can get over the 5000 record limit. It seems to only happen when I use MSOLAP.3 or MSOLAP.4 (ssas 2008) and the # of rows exceeds 5000.

Anybody?

Thanks,

Don

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 4

I narrowed this down to it being a problem with the first field in the result set. If I omit that field I can get all the rows. up to 18000 in fact. Not sure what is wrong on the dataside but there doesn't seem to be a row limitation.