Powershell: Convert Table Row To String Values

Question

In a Powershell script I have the following code. I am trying to enumerate a list of DB tables from SQL and then use that list to execute subsequent commands.

$q = "SELECT name FROM master..sysdatabases 
      WHERE name NOT IN ('tempdb', 'model', 'msdb')"

$ListOfDatabases = Invoke-Sqlcmd -ServerInstance $s.ServerName 
                                 -Username $s.Username 
                                 -Password $s.Pass 
                                 -Query $q 

foreach ($db in $ListOfDatabases) {

         #If I just print $db Like so...
             $db
         # yields 
         #       name
         #       -------
         #       table1
         #       table2

         #But If I Try to use that value I get an error
             $Q = "EXECUTE SOME_PROC($db)"
             $Q   
         #   yields 
         #    EXECUTE SOME_PROC([System.Data.DataRow])
         #    EXECUTE SOME_PROC([System.Data.DataRow])
         #    EXECUTE SOME_PROC([System.Data.DataRow])
         #     etc... for each table

So where am I going wrong here? I'm trying to extract the string value table1 and table 2, etc for all the tables and use it in another SQL command. And because the SO editor is yelling at me telling me that my post is mostly code, I am adding this additional sentence to help 'convince' it to let me submit this question. -Given that most of my "explaining" is done in the comments of the code.

Why don't you use EXECUTE SOME_PROC($(db.name))"? It says right there that the property is called "name". ;) — Tomalak
– Tomalak, Commented Mar 29, 2015 at 21:50
It would have to be $Q = "EXECUTE SOME_PROC($($db.Name))" if you were to try that. Accessing Properties inside a string needs to be done in a subexpression. — Frode F.
– Frode F., Commented Mar 29, 2015 at 21:51
Do either of you want credit? Post an answer.. I'll mark it solved — HashHazard
– HashHazard, Commented Mar 29, 2015 at 21:52

Frode F. · Accepted Answer · 2015-03-29 21:53:52Z

3

$db is a datarow-object with a name property. Try a subexpression to extract the value of the name-property:

$Q = "EXECUTE SOME_PROC($($db.Name))"

answered Mar 29, 2015 at 21:53

Frode F.

55.4k9 gold badges104 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bluecakes · Accepted Answer · 2015-03-29 21:52:31Z

What you can do is expand the variable to get just the name instead of an object with a name property and call the stored proc with only the value.

$q = "SELECT name FROM master..sysdatabases 
      WHERE name NOT IN ('tempdb', 'model', 'msdb')"

$ListOfDatabases = Invoke-Sqlcmd -ServerInstance $s.ServerName 
                                 -Username $s.Username 
                                 -Password $s.Pass 
                                 -Query $q 

foreach ($db in $ListOfDatabases) {

    #If I just print $db Like so...
    #    $db
    # yields 
    #       name
    #       -------
    #       table1
    #       table2

    # So we need to delve into each name separately like so
    ForEach ($dbName in $db.Name)
    {
        # This will call the proc with each name in $db
        $Q = "EXECUTE SOME_PROC($dbName)"
        $Q   
    }
}

Collectives™ on Stack Overflow

Powershell: Convert Table Row To String Values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related