SQL Query with Powershell - Return Values not maching

Question

So I have some code I wrote to take the input form a text file and run some sql checks against another database I populated:

$volOutput = gc C:\Users\<user>\Desktop\mutant.txt 

foreach ($m in $volOutput) { 

  $check = $m.split()[-1] | select -Unique

  foreach ($c in $check) {
    #$c - this lists all of them so the foreach is working...

    # Build the connection and search the db for $c names.
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Server=(localdb)\mutex; Database=MutexObjects"
    $conn.Open()
    $db = $conn.CreateCommand()
    $db.CommandText = "select Names from Objects WHERE Names='$c'"
    $db.ExecuteScalar()
    $conn.Close()    

  } # Foreach Check

} # First foreach

The return values I get are:

PS C:\> B:\Programming\powershell\parse_vol.ps1
ZonesCounterMutex
ZoneAttributeCacheCounterMutex
ZonesCacheCounterMutex
ZoneAttributeCacheCounterMutex
ZonesLockedCacheCounterMutex
ZonesCounterMutex
ZoneAttributeCacheCounterMutex
ZonesCacheCounterMutex
ZoneAttributeCacheCounterMutex
ZonesLockedCacheCounterMutex

Which is correct, but it's also missing a lot more. If I take individual samples and run queries from within SQL management studio for example, I get:

I populated the word "test" in each list as a....test.

Select Names From Objects WHERE Names='test'

Names

test

But I don't see test on the output from the above code. There are about 5 or 6 more than it's missing that I have validated manually by querying the db w/in SQL management studio.

Any help is much appreciated.

Ansgar Wiechers · Accepted Answer · 2013-06-03 08:58:42Z

1

Compare the file content against the full list of Names from the database:

$filecontent = Get-Content "C:\Users\<user>\Desktop\mutant.txt" `
          | % { $_.split()[-1] } `
          | select -Unique

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=(localdb)\mutex; Database=MutexObjects"
$conn.Open()
$dbwrite = $conn.CreateCommand()
$dbwrite.CommandText = "SELECT Names FROM Objects"

$reader = $dbwrite.ExecuteReader([System.Data.CommandBehavior]::CloseConnection)
$dbcontent = while ( $reader.Read() ) { $reader[0] }

$conn.Close()    

Compare-Object $filecontent $dbcontent

Does Compare-Object show differences?

answered Jun 3, 2013 at 8:58

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

3 Comments

mrwh1t3 Over a year ago

Yes, a lot for both <= and = (more to the $filecontent <=)

Ansgar Wiechers Over a year ago

The names with a <= side indicator are not present in the database, so it's unsurprising that they're not listed by your script.

mrwh1t3 Over a year ago

So I actually went back and looked. I was missing some, and I think this was working all along... In either case, I like your response and gave you credit.

Collectives™ on Stack Overflow

SQL Query with Powershell - Return Values not maching

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related