System.OutOfMemoryException while MDX Query using Invoke-ASCmd

Question

i want to import a MDX result set into Powershell using Invoke-ASCmd.

Import-Module SQLPS -DisableNameChecking
Import-Module SQLASCMDLETS

Push-Location SQLSERVER:\SQLAS\$ServerInstance\Default\Databases\$Database\Cubes\

$result = Invoke-ASCmd -Database $Database -Query $Query

Pop-Location

It fails with a System.OutOfMemoryException

The same MDX query works fine in SQL Server Management Studio.
There is still a lot of RAM free.
The Powershell runs in 64bit mode.
MaxMemoryPerShellMB is increased.
I am using SQL Server 2016 and Powershell 4

What's to do, to import large datasets from SQL Server Analysis Services using Powershell?

What software versions are involved? How large is the result set? — mklement0, Oct 26 '18 at 14:25
google for System.OutOfMemoryException, it's not necessarily about the RAM. Can you post entire script you are running? — Mike Twc, Oct 26 '18 at 15:58
I fixed the example. The Parameters are Database, ServerInstance (Hostname) and Query. — abbgrade, Dec 03 '18 at 13:58

score 1 · Answer 1 · answered Oct 29 '18 at 08:03

I found an workaround for this:

If you add a linked server to a SQL Server to the SSAS Cube you can execute it wrapped in SQL.

Invoke-Sqlcmd `
    -ServerInstance $ServerInstance `
    -Query "SELECT * FROM OpenQuery([SSAS_LinkedServer],'$( $query.Replace("'", "''" ) )')"

System.OutOfMemoryException while MDX Query using Invoke-ASCmd

1 Answers1