"Cannot find table 0" on running an INSERT

[replicaJunction]

When running an INSERT command, the database operation succeeds, but I get a "Cannot find table 0" error in PowerShell:

Cannot find table 0.
At C:\Users\replica\Documents\WindowsPowerShell\Modules\MyModule\Internal\Invoke-SqlCmd2.ps1:522 char:21
+                     $ds.Tables[0]
+                     ~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], IndexOutOfRangeException
    + FullyQualifiedErrorId : System.IndexOutOfRangeException

I found an answer on Stack Overflow that describes a similar issue - since an INSERT naturally doesn't produce any output, the function is trying to reference row 0 in an empty collection.

My proposed solution is to wrap the switch block near the end of the function in an if:

if ($ds.Tables.Count -gt 0)
{
    switch ($As)
    {
        # ...
    }
}
else
{
    Write-Verbose "No output was returned from the SQL instance."
}

I thought about just wrapping specific cases in the switch block, but there are two that use that index behavior ($ds.Tables[0]), and I'm not certain it's a big deal if the function doesn't provide output in this case, rather than providing a DataTable or DataSet object that contains no tables or rows. I could definitely be wrong, though, as I'm still wrapping my mind around SQL stuff.

I've tested this change and confirmed that it seems to be working for me - the INSERT succeeds, there's a verbose message and no error, and there's no output. I can submit a PR if you all would like.

Thanks!
~replica

This issue is copied over from issue 19 on ramblingcookiemonster's original version of Invoke-SqlCmd2.

[ashdar]

I ran into the same problem a number of months ago and reworked my own copy of the function to support a new value of "NonQuery" for the -As parameter. I then added around 10 lines of code to support that. My thinking was that I wanted to minimize the impact to existing code. (I've also made a few other small changes.)

Unfortunately, the code is in a private TFS repo and not GitHub, so I'd have to do some work to get this into something I can easily share via GitHub tools. Would that be worthwhile?

[alevyinroc]

I'm unable to reproduce this with the current release of the module. Has it been resolved, or is there a particular way the cmdlet has to be called to trigger it? I'm executing it with pretty simple parameters:

invoke-sqlcmd2 -ServerInstance MYINSTANCE -database demo -query "insert into stores (StoreId,city,state) values ('SFO','San Francisco','CA');"

[AndyP2]

I think it occurs when you tighten up the language behaviour with Set-StrictMode -Version Latest or somesuch. In any case, I believe I've fixed it in #12 - do we mark this issue closed now?

[RamblingCookieMonster]

I think it occurs when you tighten up the language behaviour with Set-StrictMode

Sounds right to me!

Side note for anyone who ran into this: is there a reason you're using strict mode? Have you considered not using it? IIRC it's not a recommended practice, and some folks will go so far as to discourage it

Cheers!

[AndyP2]

I like to use strict mode when I'm writing reusable code, because it forces me to handle edge conditions that I might have overlooked. I agree if you're writing single-use code then if it works on the day that's good enough. But then I prefer c# over js, and I know others who are the opposite :-)
A library function like invoke-sqlcmd2 shouldn't force a coding style on the consumer anyway.