Sign in
 
HomeWindows Server 2012Windows Server 2008 R2Windows Server 2003LibraryForums
Windows Server TechCenter >
> 
> 
invoke-sqlcmd output - store and call property values

Answered invoke-sqlcmd output - store and call property values

  • Wednesday, May 09, 2012 6:07 PM
     
     
    Sign In to Vote
    0

    invoke-sqlcmd -query "sp_spaceused" -database master -serverinstance SQLDB\INST1

    database_name     : master
    database_size     : 1573.44 MB
    unallocated space : 225.14 MB
    RowError          :
    RowState          : Detached
    Table             : {}
    ItemArray         : {master, 1573.44 MB, 225.14 MB}
    HasErrors         : False

    I saved the output of the above command to a variable, but $VAR.database_name does not work. 

    The only way I could get it to work was to use select for each property; for example:

    $dbname = invoke-sqlcmd -query "sp_spaceused" -database master -serverinstance SQLDB\INST1 | select database_name

    Since I want to get more than one property this is not efficient; is there a better way?

    database_name     NoteProperty System.String database_name=master

     

All Replies

  • Wednesday, May 09, 2012 6:48 PM
     
     Proposed Has Code
    Sign In to Vote
    0

    Do it like this:

    $var | select -expand database_name
$var | select -expand database_size
# etc...

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

     
  • Thursday, May 10, 2012 11:32 AM
     
     
    Sign In to Vote
    0

    Your suggestion gives me what I'm looking for, but an error is displayed on the console.

    $TEST = invoke-sqlcmd -query "sp_spaceused" -database master -serverinstance SQLDB\INST

    $DBNAME = $TEST | select -expand database_name
    $DBSIZE = $TEST | select -expand database_size
    $DBNAME
    $DBSIZE

    --------------------------------------

    Select-Object : Property "database_name" cannot be found.
    At C:\users\anthonys\desktop\t5.ps1:3 char:25
    + $DBNAME = $TEST | select <<<<  -expand database_name
        + CategoryInfo          : InvalidArgument: (System.Data.DataRow:PSObject) [Select-Object], PSArgumentException
        + FullyQualifiedErrorId : ExpandPropertyNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand

    Select-Object : Property "database_size" cannot be found.
    At C:\users\anthonys\desktop\t5.ps1:4 char:25
    + $DBSIZE = $TEST | select <<<<  -expand database_size
        + CategoryInfo          : InvalidArgument: (System.Data.DataRow:PSObject) [Select-Object], PSArgumentException
        + FullyQualifiedErrorId : ExpandPropertyNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand

    master
    1573.44 MB

    How can I clear the errors?


    • Edited by wallst360 Thursday, May 10, 2012 12:00 PM
    •  
     
  • Thursday, May 10, 2012 11:55 AM
     
     Answered Has Code
    Sign In to Vote
    1

    I have found the cause and solution to your problem. $TEST is actually an array, and to access it, you can use array notation:

    $TEST = invoke-sqlcmd -query "sp_spaceused" -database master -serverinstance grant-pc
$TEST[0].database_name
$TEST[0].database_size

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

     
  • Thursday, May 10, 2012 12:03 PM
     
     
    Sign In to Vote
    0
    Works great; thanks!
     
  • Thursday, May 10, 2012 12:12 PM
     
     
    Sign In to Vote
    0
    Note that more data is stored in $TEST[1] too.

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)