New to PowerShell, but not programming. very confused

Occasional Contributor

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]

    $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.



2 Replies
best response confirmed by MNtobar (Occasional 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.






“Everything works the way it is supposed to, even when it isn’t what you expect.” -- Gyro Gearloose (attributed) :smiling_face_with_smiling_eyes:


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.