Store Invoke-Sqlcmd Query as String

Question

I have a value that I want to find and pull out of a database. I have everything I need in order to run this script except how to store the result in a variable and then write those results to a txt file

$CNumber = Invoke-Sqlcmd -query ("Select Cust From SY.CompanyCode") -ServerInstance $SQLInstance -Database $Database

Add-Content $Mobile $CNumber

When it writes the results in the text file it looks like this:

System.Data.DataRow

When I just run the Invoke-Sqlcmd and don't try to store it in a variable I can see the correct value. So I'm assuming once this query runs, I need to convert the results somehow?

Please and thank you.

Answer 1

Because the result of the query is a System.Data.DataRow, PowerShell first needs to convert the DataRow to a String before it can be used with Add-Content and the easiest way to convert any object into a String is to use the ToString() method.

You can check this yourself by calling

$CNumber.ToString()

and it should show up in the console as:

System.Data.DataRow # OR System.Object[] if there are multiple DataRows

As you're only selecting one column you can simply access the property on the DataRow but keeping in mind that the query may return multiple DataRows we should loop through them, something like this:

foreach($row in $CNumber.Cust)
{
    # row will be equal to each cust from the query
    # add row to your file here
}