Search code examples
sqlpowershellssasdmv

DMV Query syntax incorrect exception

I'm writing a simple query function to make my script efficient and simple as I start writing more queries in the future. 

Function Query($Query) {
    $connectionString = "Provider=msolap;Data Source=$Server;Initial Catalog=$Database;"

    $connection = New-Object System.Data.OleDb.OleDbConnection $connectionString

    $command = New-Object System.Data.OleDb.OleDbCommand
    $command.Connection = $connection 
    $command.CommandText = $Query 

    $connection.Open()

    $adapter = New-Object System.Data.OleDb.OleDbDataAdapter #$command
    $adapter.SelectCommand = $command 

    $dataset = New-Object System.Data.DataSet
    [void] $adapter.Fill($dataSet)

    $connection.Close()

    $dataSet.Tables[0]
}

Query 

SELECT [SESSION_ID], [SESSION_SPID] 
FROM $SYSTEM.DISCOVER_SESSIONS";

I get this error:

Exception calling "Fill" with "1" argument(s): "Query (1, 41) The syntax for '.' is incorrect. (SELECT [SESSION_ID],[SESSION_SPID] FROM .DISCOVER_SESSIONS)."
+ [void] $adapter.Fill($dataSet)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : OleDbException

Am I missing something? I followed the syntax per MSDocs:

https://learn.microsoft.com/en-us/sql/analysis-services/instances/use-dynamic-management-views-dmvs-to-monitor-analysis-services?view=sql-server-2017

And I actually tried it successfully without creating a query function and it displayed results. somehow with making it as a query function it doesn't work...

Solution

  • Use single-quoted strings:

    Query 'SELECT [SESSION_ID],[SESSION_SPID] FROM $SYSTEM.DISCOVER_SESSIONS';

    In Powershell, a double-quoted string does string interpolation, i.e. the $SYSTEM in your string is replaced with the value of variable SYSTEM, or (in this case) a blank string when SYSTEM is not defined.