Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Powershell 3 questions

Posted on 2013-12-17
1
Medium Priority
?
291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Loops Section Overview
Screencast - Getting to Know the Pipeline

610 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