SQL and System.Data.DataRow

This topic contains 4 replies, has 2 voices, and was last updated by  Shane O’Neill 8 months, 1 week ago.

  • Author
    Posts
  • #67486

    Nick Rimmer
    Participant

    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

    Shane O’Neill
    Participant

    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

      Nick Rimmer
      Participant

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

    • #68152

      Nick Rimmer
      Participant

      That did the trick ! Thanks for the solution

    • #68155

      Shane O’Neill
      Participant

      Happy to help Nick 🙂

You must be logged in to reply to this topic.