Solved

Powershell 3 questions

Posted on 2013-12-17
1
284 Views
Last Modified: 2013-12-17
Here's a short sample to show the issue I'm experiencing with PS 4.0
I don't understand the logic of what is happening.
My background is BASIC.

I run a SQL query and then expect to be able to change the data on the current record being processed, which I take for granted is assigned to $CSVRecord by the FOREACH statement. However, it seems that the results are illogical, so please explain what is happening here.
My goal is to process a bunch of database records, change some of the fields in each record, duplicate some of the records by appending them to the end of the $CSV, and then finally exporting.
I have a 500 line program written and everything runs without errors, but the results of appended records are not what is expected.
I can do the same thing on the command line and it seems to work as I expected it to and I verify by using the index of $CSV to display the entire record.
For example, the record appended in the program is just a copy of the original record in $CSV, instead of being a modified $CSVRecord, but on the command line I can then modify a field manually (as done in the program) and append it correctly. I can then use something like $CSV[$CSV.count-1] to view the last appended record and it is correct.

What is going on?




# requires powershell 3+
clear-host
cls
$Query = "SELECT 'one', 'two', 'three' "

$CSV = Invoke-Sqlcmd -Query $Query
$CSV = @($CSV) + @($CSV)


write-host Show two idnetical records
$CSV
pause
cls
[int]$i=0
write-host Why does this NOT show 2 unique records???

foreach ($CSVRecord in $CSV)
{
$i++
#loop through each record in $CSV and modify the 2nd column to be unique
$CSVRecord.Column2 = "RECORD #"+$i
}

$CSV
pause
cls
write-host NOW WHAT is happening???
$CSVRecord.Column3 = "BUTTER"
$CSV=$CSV+$CSVRecord
$CSV
0
Comment
Question by:johnj_01201
1 Comment
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39724454
The issue is that the SQL query's results are NOT duplicated. You are only copying the "container" for those columns, and the container points to individual column objects - those pointers are copied, instead of the content.
You always need to be very careful when duplicating objets in PowerShell. Only simple types (strings, ints, aso) are copied, everything else remains a pointer to the same object element.

Further, $CSVRecord at the very end points to the last $CSV row, and again you are just copying a pointer to the last row.

In general, if you want to create valid copies, use New-Object, or the Clone() method if existing.
However, both are not correct with the result of Invoke-SqlCmd, which is a [System.Data.DataRow] array. It is much more complex, see http://msdn.microsoft.com/en-us/library/system.data.datarow(v=vs.110).aspx for background info.

What you want to do has to be done differently. You cannot add rows to a query result of Invoke-SqlCmd. You'll have to use DataReader and stuff to get managable objects, like the table definition, and then be able to insert rows.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now