locked
Inconsistent Powershell error when trying to do a Dataset Fill RRS feed

  • Question

  • I am a relatively inexperienced in Powershell, and would really appreciate some assistance please.

    I have a script that runs multiple queries and saves the data to file (based on a MSOLAP connection). Each query has a unique QueryID which is referenced from an Excel Spreadsheet, using a Python script (which does this + more), and this QueryID is then passed through to Powershell to execute the query.

    The script works, however I am receiving errors with certain QueryID's at this step:

    $adapter.Fill($dataset)

    Some work, some don't. It's really inconsistent behavior.

    The Error is:

    Exception calling "Fill" with "1" argument(s): "Object reference not set to an instance of an object." At D:\script.ps1:33 char:1 + $adapter.Fill($dataset) + ~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : NullReferenceException

    I run the query manually using Excel Powerpivot, and it is valid for those QueryID's and data exists. The reference Excel spreadsheet's contents are valid, I have checked as well.

    My code below:

    $param ($QueryID)
    $connectionString = “Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Data Source=1.2.3.4;Initial Catalog=Reporting;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error”
    $query = "SELECT QUERY..."+$QueryID+"MORE OF THE QUERY"
    
    $savefilename = ("D:\output.csv")
    
    $connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
    $connection.ConnectionString = $connectionString
    
    $command = $connection.CreateCommand()
    $command.CommandTimeout = 0 
    
    $command.CommandText = $query 
    $adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command  
    $dataset = New-Object -TypeName System.Data.DataSet
    
    $adapter.Fill($dataset)
    
    foreach ($Row in $dataset.Tables[0]) {
        #...
    }

    Monday, June 11, 2018 10:25 AM

All replies

  • Start here to learn how to use the OLAP data provider.  The standard provider cannot execute OLAP queries.

    https://msdn.microsoft.com/en-us/library/dn141152%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396


    \_(ツ)_/

    Monday, June 11, 2018 10:37 AM
  • Hi, thank you for the response. Sorry, But I don't understand what you mean.

    When I run this query by hard coding values in the specific QueryID's which usually generate errors, it works.

    Data is returned.

    So what do you mean "The standard provider cannot execute OLAP queries."

    Thank you!

    Monday, June 11, 2018 10:55 AM
  • If it works then what is your issue?  What is the query that works?

    For the standard SQL provider the code is correct so your query must be wrong. 

    If you use a reader, executing the reader will give you information on what is wrong with the query.


    \_(ツ)_/

    Monday, June 11, 2018 11:08 AM
  • Hi,  as I posted in my original post, the behavior is inconsistent - the script works for some and doesnt work for other ID's.

    The query stays the same, the ID changes as part of the loop.

    Please can you be specific. what is the code to use a reader which will show me what is wrong?

    Tuesday, June 12, 2018 5:20 AM
  • $rdr = $command.ExecuteReader()

    \_(ツ)_/

    Tuesday, June 12, 2018 5:22 AM