Forum Discussion
MNtobar
Sep 02, 2022Copper Contributor
New to PowerShell, but not programming. very confused
I have no idea what version of PowerShell I am running, but I am using Win10.
PowerShell newbie just trying to understand how things work. Many of years experience scripting SQL database stuff.
Here is my setup:
create table AA_ToddPS
(
Tseq int,
Tnum int,
Tstr varchar(40)
)
Insert into AA_ToddPS (Tseq, Tnum, Tstr)
Values (1, 711, 'Birthday'),(2, 721, 'Anniversary')
Select * from AA_toddPS
Tseq Tnum Tstr
1 711 Birthday
2 721 Anniversary
Here is what I have cobbled together from various sources:
## Just the User tablerows
$query = "SELECT Tseq, tnum, tstr FROM grain.dbo.AA_TODDPS order by Tseq"
$seqquery = "select max(Tseq) FROM grain.dbo.AA_TODDPS"
$seqnew = 0
$seqnew = invoke-Sqlcmd -Query $seqquery -ServerInstance 'WME-VM10-RW'
$tablerows = invoke-Sqlcmd -Query $query -ServerInstance 'WME-VM10-RW'
## build a "data" table
$Datatable = $Null
$Datatable = New-Object System.Data.DataTable
$Datatable.Columns.Add("Tseq", "System.Int32") | Out-Null
$Datatable.Columns.Add("Tnum", "System.Int32") | Out-Null
$Datatable.Columns.Add("Tstr", "System.String") | Out-Null
$count = 0
foreach ($row in $tablerows)
{
$row = $Datatable.NewRow()
$row.Tseq = $tablerows.Tseq[$count]
$row.Tnum = $tablerows.Tnum[$count]
$row.Tstr = $tablerows.Tstr[$count]
$Datatable.Rows.Add($row)
$count += 1
}
$count = 1
foreach ($Row in $DataTable.Rows) {
$insert = "insert into grain.dbo.AA_TODDPS (tseq, tnum, tstr)
Values({0}, {1}, '{2}');" -F ($seqnew), ($Row.tnum + ($count * 100)), $Row.tstr
$result = invoke-Sqlcmd -Query $insert -ServerInstance 'WME-VM10-RW' -OutputSqlErrors $true
write-host "$result"
$count = $count + 1
$seqnew = $seqnew + 1
}
What I am anticipating is that when I hit my breakpoint at line 36, I would expect $insert to be
insert into grain.dbo.AA_TODDPS (tseq, tnum, tstr)
Values (3, 921, 'Anniversary')
What I get is this:
$seqnew is just a variable, but has it been morphed into some object? I am not tied to using this setup to get this done, I just want to have it work, and to understand why.
Thank you for your time.
Yes, your screenshot showing "what you get" makes perfect sense.
As noted in the commandlet's documentation, Invoke-Sqlcmd returns one of the following types (see the OutputAs parameter description for these details):
- DataRows ([System.Data.DataRow])
- DataSet ([System.Data.DataSet])
- DataTables ([System.Data.DataTable])
If you don't specify the -OutputAs parameter, then you get DataTables by default - which is what you see in your screenshot.
If you cross-reference that with your script, here are the points of interest:
- Line 5: An integer value of 0 is assigned to the variable $seqnew, which ends up never being used because:
- Line 6: A new value of type [System.Data.DataTable] is assigned to $seqnew from the output of Invoke-Sqlcmd;
- Line 34: $seqnew, which is still of type [System.Data.DataTable] is included in the formatted string, probably under the assumption it's an integer when it's not.
This is why your screenshot is telling you $seqnew is a [System.Data.DataTable], because as of line 6, that's what it has become.
Cheers,
Lain
- LainRobertsonSilver Contributor
Yes, your screenshot showing "what you get" makes perfect sense.
As noted in the commandlet's documentation, Invoke-Sqlcmd returns one of the following types (see the OutputAs parameter description for these details):
- DataRows ([System.Data.DataRow])
- DataSet ([System.Data.DataSet])
- DataTables ([System.Data.DataTable])
If you don't specify the -OutputAs parameter, then you get DataTables by default - which is what you see in your screenshot.
If you cross-reference that with your script, here are the points of interest:
- Line 5: An integer value of 0 is assigned to the variable $seqnew, which ends up never being used because:
- Line 6: A new value of type [System.Data.DataTable] is assigned to $seqnew from the output of Invoke-Sqlcmd;
- Line 34: $seqnew, which is still of type [System.Data.DataTable] is included in the formatted string, probably under the assumption it's an integer when it's not.
This is why your screenshot is telling you $seqnew is a [System.Data.DataTable], because as of line 6, that's what it has become.
Cheers,
Lain
- MNtobarCopper Contributor
Thanks,LainRobertson
“Everything works the way it is supposed to, even when it isn’t what you expect.” -- Gyro Gearloose (attributed) 😊
Your reply was gracious, instructional and very much appreciated. If have been in environments where the reply would have been laced with derision and the (usually) unhelpful statement of "RTFM!!"
I had looked at the referred to documentation. My background caused me to expect the “Inputs” and “Outputs” sections to be the first items on a doc page. I gave up after reading through all the non-Azure examples and scrolling through several pages of parameters. I should have persevered and read closer. Thanks.
Tobar