SQL and System.Data.DataRow

PowerShell Help
1

Hi,

I’m trying to put together a script that reads in a list of filenames from a CSV and then retrieves a list of
filenames from a SQL database. I want to iterate through the list of files from the database and see if any
match the files in the CSV.
I have the barebones of the script and I can import the CSV and manipulate the data from the CSV ok, I can
also retrieve the filenames from a SQL table using Invoke-SQLCMD, my problem seems to happen when I try and
use the data from the SQL database. I get System.Data.DataRow or no data at all.

Can anyone point me in the right direction of a good tutorial on manipulating SQL data within PS or
any advice ?

Many Thanks

2

Hi Nick,

I had something of a similar problem and it was because I was using 

$_
for the results of my Invoke-SqlCmd.

So

 
$database = Invoke-Sqlcmd -ServerInstance localhost -Database tempdb -Query 'SELECT name FROM sys.databases'

$database | % { "I'm working on $_"}

gave me a whole long line of

I'm working on System.Data.DataRow I'm working on System.Data.DataRow I'm working on System.Data.DataRow I'm working on System.Data.DataRow I'm working on System.Data.DataRow I'm working on System.Data.DataRow I'm working on System.Data.DataRow I'm working on System.Data.DataRow I'm working on System.Data.DataRow

To work around this I had to use the name of the column that I was returning.

$database | %{ "I'm working on $($_.name)"}

Which gave me the results I wanted

I'm working on master I'm working on tempdb I'm working on model I'm working on msdb I'm working on ReportServer I'm working on ReportServerTempDB

Hope it helps…

3

Thanks for this, I’ve not tried it yet, but will report back.

4

That did the trick ! Thanks for the solution

5

Happy to help Nick :slight_smile: