PowerShell using SMO - how to look at the query results?

This code worked well with PS 4.0, and .net 4 something. But when I try to execute it on another machine the FOREACH gets an indexing error.

Guess my first question is (1) after executing the ExecuteWIthResults, how do you look at the individual row/column values returned?  (2) what problems will I run into moving a SMO solution between different .NET versions?

 

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlsvr
= 'fidev360x\fidev360x'
$dir
= 'c:\temp\pssmo\'
$database = '
aaatest'
$diagnostics = 1
$sqlserver = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlsvr)
$db = $sqlserver.databases[$database]
$Tables = $db.ExecuteWithResults("Select * FROM ifs_exporttables")
foreach ($DataRow in $Table[0].Tables[0].Rows)
    {
    $TableName = $DataRow[2].tostring()    # ifs_ExportTables.TableName
    $Delimiter = $DataRow[4].tostring()    # ifs_ExportTables.Delimiter
    $Header = $DataRow[5].tostring()       # ifs_ExportTables.ColumnHeaders
    if ($diagnostics = 1)
        {write-host -ForegroundColor Green "Creating Table $TableName to path $dir$TableName.csv" }
    $sqlserverR = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlsvr)
    $dbR = $sqlserverR.databases[$database]
    $TablesR = $dbR.ExecuteWithResults("Select * FROM $TableName")
    $result = $TablesR[0].tables.item(0)
    if ($Header -eq '
1')
    {$result |export-csv -Delimiter $Delimiter –notype -path $dir$TableName.csv  -Encoding ASCII}
    else
    {$result | ConvertTo-Csv -NoTypeInformation -Delimiter $Delimiter | Select-Object -Skip 1 | Out-File -FilePath $dir$TableName.csv  -Encoding ASCII}
    }

 

 

  • this is a better generic example. works on one machine but not another. gets "cannot index to a null array" on the $tablename = . the machine that get the error clrver says that 2.0 and 4.0 are installed.

     

    [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    $sqlsvr = 'fidev360x\fidev360x'
    $dir = 'c:\temp\pssmo\'
    $database = 'aaatest'
    $diagnostics = 1
    $sqlserver = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlsvr)
    $db = $sqlserver.databases[$database]
    $Tables = $db.ExecuteWithResults("Select * FROM ifs_exporttables")
    foreach ($DataRow in $Table[0].Tables[0].Rows)
        {
        $TableName = $DataRow[2].tostring()    # ifs_ExportTables.TableName
        $Delimiter = $DataRow[4].tostring()    # ifs_ExportTables.Delimiter
        $Header = $DataRow[5].tostring()       # ifs_ExportTables.ColumnHeaders
        if ($diagnostics = 1)
            {write-host -ForegroundColor Green "Creating Table $TableName to path $dir$TableName.csv" }
        $sqlserverR = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlsvr)
        $dbR = $sqlserverR.databases[$database]
        $TablesR = $dbR.ExecuteWithResults("Select * FROM $TableName")
        $result = $TablesR[0].tables.item(0)
        if ($Header -eq '1')
        {$result |export-csv -Delimiter $Delimiter –notype -path $dir$TableName.csv  -Encoding ASCII}
        else
        {$result | ConvertTo-Csv -NoTypeInformation -Delimiter $Delimiter | Select-Object -Skip 1 | Out-File -FilePath $dir$TableName.csv  -Encoding ASCII}
        }

     

     

  • geez, you would think by the second try I could get it right

     

    $sqlsvr = YOUR SERVER'
    $dir = 'YOUR OUTPUT PATH'
    $database = 'YOUR DATABASWE'