Invoke-SQLCMD2 - Datatable not populating

by Silverback at 2012-08-29 12:25:42

I have been using the Invoke-sqlcmd2 command for a number of scripts but am having trouble with one that I just cant seem to get working:

$dt = Invoke-sqlcmd2 -ServerInstance $SQLServer -Database $SQLDBName -Query "
SELECT org_unit_description_long
FROM table1
SELECT substring(Name,5,LEN(Name))
FROM table2"

write-host ‘There are ’ $dt.tables[0].rows.count ’ rows:’

The query works directly in SSMS. The variables $SQLServer and $SQLDBName work in prior statements.
The error is the standard "Cannot index into a null array"

Am I missing something obvious?
Thanks. SB
by poshoholic at 2012-08-29 16:18:28
My first instinct is to take a look at $dt.tables[0]… in your Write-Host call. Since the error indicates you "cannot index into a null array", and since that is the only place you are indexing into an array, that’s a good place to start. If you comment that Write-Host call out does your script seem to run? Can you double-check the value of $dt after Invoke-SqlCmd2 returns, and make sure it contains what you expect by simply invoking $dt? Those would be good first steps to take.
by Silverback at 2012-08-30 05:39:08
I had added the write-host command just to see what was in $dt. The next statement is:
foreach ($row in $dt.Tables[0].Rows) and I get the same error.

I found this post: … fault.aspx

If ($dt)
write-host ‘hi’
#Do something

write-host prints ‘hi’. I also changed the query to select 1 where 1 = 1 and get ‘hi’ so it appears to exist. The query produces data when run by itself and other queries run prior using similar technique make changes to the database so its not an access/rights issue.

Note: Im coming from a VBScript background so debugging objects is still a little difficult for me. Thanks for your patience.
by poshoholic at 2012-08-30 05:44:37
The indexing error seems to come specifically from $dt.Tables[0].

What do you see if you invoke this:

[script=powershell]$dt.Tables -ne $null # make sure $dt.Tables is not null[/script]
or this:

[script=powershell]$dt.Tables.Count # Show how many tables there are.[/script]

I think $dt.Tables is probably null or empty (that’s what the error seems to be indicating).

We also have a PowerShell and SQL forum, so I’m going to mirror this post into that forum, see if those moderators have ideas about this as well.
by Silverback at 2012-08-30 06:08:23
$dt.Tables -ne $null ---- False
$dt.Tables.Count ------ Blank (I thought I would see 0 but I got nothing)
by poshoholic at 2012-08-30 07:41:46
Since $dt.Tables -ne $null returns false, that means that $dt.Tables is equal to null. This also explains why $dt.Tables.Count returns nothing – the Tables property is null, so you can’t check the Count property of that because it does not exist. It seems your query isn’t returning what you are expecting it to. Does $dt contain anything else that might indicate why? Maybe a property that identifies why the Tables property is null? Try $dt | Format-List * to see what information you do have after running your query.
by Silverback at 2012-08-30 08:21:22
I get the following for each record - correct number of records and the right field returned.

org_unit_description_long : Department1 Name
RowError :
RowState : Unchanged
Table : {System.Data.DataRow, System.Data.DataRow, System.Data.DataRow, System.Data.DataRow…}
ItemArray : { Department1 Name }
HasErrors : False
by poshoholic at 2012-08-30 11:22:54
There you go! In the property list it looks like you have a "Table" property (singular), not "Tables" (plural). With that in mind you should be able to do this in your script you posted above:

[script=powershell]Write-Host 'There are ' $dt.Table.Rows.Count ' rows]
by Silverback at 2012-08-30 12:28:14
If I change the write-host statement from
write-host ‘There are ’ $dt.tables[0].rows.count ’ rows:’
write-host ‘There are ’ $dt.table.rows.count ’ rows:’

it doesnt error but the value of ’ $dt.table.rows.count ’ is null… Im thinking the count property is not valid.

When I remove the write-host line and let it fall to the foreach command, both
foreach ($row in $dt.Tables[0].Rows)
foreach ($row in $dt.Table.Rows)

Give the null array error.
by poshoholic at 2012-08-30 12:38:41
Ok, closer but not there yet. Let’s do two things.

1. Copy the error message you get (null array error) in its entirety into this thread. It may be showing something that I’m not seeing. All of the error details are important.

2. Peel this onion back some more by interactively verifying that you get expected, non-null results with each piece of what you’re doing.

For example, you start with $dt. When you invoke $dt on a line by itself, you see a bunch of properties, including table. Then invoke $dt.table. Does that give you output? If so, use does $dt.table have a property called rows ($dt.table | Get-Member -Name rows)? If now, verify that $dt has a property called table ($dt | Get-Member -Name table). Once you have gotten to the next step (identified the property you need as rows or row, output it as well). Each step of the way you should output what you have, make sure that all assumptions you are making in your script are valid (i.e. make sure that what you expect to be non-null is non-null, and make sure that the property names are correct each step of the way).

Another way to do this is to use the debugger that comes in PowerShell ISE. Open your script in PowerShell ISE and step through it, see exactly where it fails, and ideally invoke some of the commands to verify things along the way in the embedded console. This is the best way to get to the bottom of things.
by Silverback at 2012-08-31 12:30:16
I was able to get this working. Your help with debugging were the keys. I believe there were 2 issues - I added a '|out-datatable '
$dt1 = Invoke-sqlcmd2 -ServerInstance $SQLServer -Database $SQLDBName -Query "
SELECT org_unit_description_long
FROM table1
SELECT substring(Name,5,LEN(Name))
FROM table2" |out-datatable

Also the get-member command gave me TypeName: System.Data.DataRow
modifying the failing line
foreach ($row in $dt.Tables[0].Rows) to
foreach ($row in $dt1.Rows)

Thanks for all of the help, it really is appreciated.
by poshoholic at 2012-08-31 14:59:03
Excellent, I knew we’d get to the bottom of it! Maybe next time I’ll have SQL server installed again (had to rebuild my system recently) so that I can troubleshoot directly instead of just working with forum contents. :slight_smile: