SQL and System.Data.DataRow

Welcome Forums General PowerShell Q&A SQL and System.Data.DataRow

This topic contains 4 replies, has 2 voices, and was last updated by

 
Participant
1 year, 8 months ago.

  • Author
    Posts
  • #67486

    Participant
    Points: 1
    Rank: Member

    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

  • #67527

    Participant
    Points: 22
    Rank: Member

    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...

    • #67573

      Participant
      Points: 1
      Rank: Member

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

    • #68152

      Participant
      Points: 1
      Rank: Member

      That did the trick ! Thanks for the solution

    • #68155

      Participant
      Points: 22
      Rank: Member

      Happy to help Nick 🙂

The topic ‘SQL and System.Data.DataRow’ is closed to new replies.