Problem with receiving large recordset from PivotTable connection

[jankrib]

I want to get around 60000 fields from an OLAP cube into a recordSet.
I have a OWC11 PivotTable connected to the Cube. My code worked fine
when I used it on less than 1000 fields.

My Code:

Dim cs As ADODB.Command

cs = New ADODB.Command
cs.ActiveConnection = AxPivotTable1.Connection

cs.CommandText = "SELECT [World].[Town] ON COLUMNS FROM [WorldCube]"

Dim re As ADODB.Recordset

re = cs.Execute()

On the cs.Execute() I get a error:
Memory error: While attempting to store a string, a string was found
that was larger than the page size selected. The operation cannot be
completed.

I have read several places that when collecting more than 1000 records
paging should be used. I tried adding this line before the execute
command:

cs.Properties("Page Size") = 1000

, but I get that 'Item' are 'ReadOnly'. This is strange because I've
seen this line used in a lot of example codes.

I've also tried

cs.Properties("Page Size").value = 1000

,but then I get that Properties("Page Size") doesn't exist.


Very thankful if anyone can help me.

 

[Alvin Bruney [MVP]]

c'mon, 60k is just too much. I don't know of a user who can process that
much information. trim your set to a couple hundred records

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Book www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

 

[jankrib]

Hehe. I know, but I am going to do some custom prossessing on the
data. It's not for the end user to see. So it's not possible?

c'mon, 60k is just too much. I don't know of a user who can process that
much information. trim your set to a couple hundred records

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Bookwww.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

I want to get around 60000 fields from an OLAP cube into a recordSet.
I have a OWC11 PivotTable connected to the Cube. My code worked fine
when I used it on less than 1000 fields.

My Code:

Dim cs As ADODB.Command

cs = New ADODB.Command
cs.ActiveConnection = AxPivotTable1.Connection

cs.CommandText = "SELECT [World].[Town] ON COLUMNS FROM [WorldCube]"

Dim re As ADODB.Recordset

re = cs.Execute()

On the cs.Execute() I get a error:
Memory error: While attempting to store a string, a string was found
that was larger than the page size selected. The operation cannot be
completed.

I have read several places that when collecting more than 1000 records
paging should be used. I tried adding this line before the execute
command:

cs.Properties("Page Size") = 1000

, but I get that 'Item' are 'ReadOnly'. This is strange because I've
seen this line used in a lot of example codes.

I've also tried

cs.Properties("Page Size").value = 1000

,but then I get that Properties("Page Size") doesn't exist.

Very thankful if anyone can help me.

 

[jankrib]

Hehe. I know, but I am going to do some custom prossessing on the
data. It's not for the end user to see. So it's not possible?

c'mon, 60k is just too much. I don't know of a user who can process that
much information. trim your set to a couple hundred records

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Bookwww.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

news:[email protected]...

I want to get around 60000 fields from an OLAP cube into a recordSet.
I have a OWC11 PivotTable connected to the Cube. My code worked fine
when I used it on less than 1000 fields.
My Code:
Dim cs As ADODB.Command
cs = New ADODB.Command
cs.ActiveConnection = AxPivotTable1.Connection
cs.CommandText = "SELECT [World].[Town] ON COLUMNS FROM [WorldCube]"
Dim re As ADODB.Recordset
re = cs.Execute()
On the cs.Execute() I get a error:
Memory error: While attempting to store a string, a string was found
that was larger than the page size selected. The operation cannot be
completed.
I have read several places that when collecting more than 1000 records
paging should be used. I tried adding this line before the execute
command:
cs.Properties("Page Size") = 1000
, but I get that 'Item' are 'ReadOnly'. This is strange because I've
seen this line used in a lot of example codes.
I've also tried
cs.Properties("Page Size").value = 1000
,but then I get that Properties("Page Size") doesn't exist.
Very thankful if anyone can help me.

One solution I found was to use the Subset method in the mdx query and
retrieve 1000 records at the time in a while loop. It works, but I
wish I had a better solution.

 

[Alvin Bruney [MVP]]

I can't think of any other clean solution for this. Other solutions would
typically involve moving the large data back and forth between the server
and client for each call. And that is where the problem started in the first
place.

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Book www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

Hehe. I know, but I am going to do some custom prossessing on the
data. It's not for the end user to see. So it's not possible?

c'mon, 60k is just too much. I don't know of a user who can process
that
much information. trim your set to a couple hundred records

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is
coming...https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Bookwww.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

I want to get around 60000 fields from an OLAP cube into a recordSet.
I have a OWC11 PivotTable connected to the Cube. My code worked fine
when I used it on less than 1000 fields.

My Code:

Dim cs As ADODB.Command

cs = New ADODB.Command
cs.ActiveConnection = AxPivotTable1.Connection

cs.CommandText = "SELECT [World].[Town] ON COLUMNS FROM [WorldCube]"

Dim re As ADODB.Recordset

re = cs.Execute()

On the cs.Execute() I get a error:
Memory error: While attempting to store a string, a string was found
that was larger than the page size selected. The operation cannot be
completed.

I have read several places that when collecting more than 1000
records
paging should be used. I tried adding this line before the execute
command:

cs.Properties("Page Size") = 1000

, but I get that 'Item' are 'ReadOnly'. This is strange because I've
seen this line used in a lot of example codes.

I've also tried

cs.Properties("Page Size").value = 1000

,but then I get that Properties("Page Size") doesn't exist.

Very thankful if anyone can help me.

One solution I found was to use the Subset method in the mdx query and
retrieve 1000 records at the time in a while loop. It works, but I
wish I had a better solution.