Powershell SQLCMD not recognizing inputfile - passing files obtained via Get-ChildItem to external programs by path

Question

I have this code, which gets all *.sql files in the current directory and for every child directory runs them on a specified $server and $database:

$dirs = Get-ChildItem | ?{ $_.PSIsContainer }
foreach ($d in $dirs)
{    
    $Result = Get-ChildItem ./ -Filter *.sql
    Foreach($item in $Result)
    {
        echo $item.Name
        sqlcmd /S $server /d $database -E -i $item
    }
}

This code works just fine but it is just a stepping stone towards a script that should get all the *.sql files in the child directories and run them on the server.

This code is the same with the difference of ./ being replaced by $d and it doesn't work:

$dirs = Get-ChildItem | ?{ $_.PSIsContainer }
foreach ($d in $dirs)
{
    $Result = Get-ChildItem $d -Filter *.sql
    Foreach($item in $Result)
    {
        echo $item.Name
        sqlcmd /S $server /d $database -E -i $item
    }
}

Instead I get the error:
SQLCMD.EXE : Sqlcmd: 'INSERT Script1.sql': Invalid filename. At E:\dir\Run all SQL in dir.ps1:23 char:12 + sqlcmd <<<< /S $server /d $database -E -i $item + CategoryInfo : NotSpecified: (Sqlcmd: 'INSERT...valid filename.:String) [], RemoteException + FullyQualifiedErrorId : NativeCommandError

As far as I could tell from testing $item is the same in both cases and $Result looks the same.

I have no idea why the second version does not work. I want the script to iterate over all child directories of the current directory it is located in, get all *.sql files in each one, print the name of the file and run it against the set $server and $database.

Answer 1

$item is a [FileInfo] object.

Change to $item.FullName, which will contain the full file path.

I assume the first version works because the file is in the same directory.

Answer 2

To add to gms0ulman's helpful answer:

In Windows PowerShell (as opposed to PowerShell (Core) 7+), [System.IO.FileInfo] and [System.IO.DirectoryInfo] instances output by Get-ChildItem / Get-Item situationally^[1] stringify to the file name only (.Name) rather than the full path (.FullName), which, in combination with PowerShell's parameter binding, can lead to subtle bugs:

^{Note: I use file name loosely here to refer to both a file's name and a directory's name; to put it differently: a file-system item's name.}

• Passing such an instance to an external program, as in your case, then passes the file name only, because all arguments to external programs are implicitly stringified.

  • Using .FullName in this case is indeed the correct solution.

• More surprisingly, however, the problem also affects cmdlet / function calls:

  • In your code, the $d in Get-ChildItem $d is also passed as a string and therefore as a file name only, because - unfortunately - the -Path parameter binds its direct arguments as strings instead of recognizing [System.IO.FileInfo] / [System.IO.DirectoryInfo] instances as such; by contrast, passing such instances via the pipeline works correctly.

  • Thus, even though $d unambiguously identifies a given directory, only its name is passed, which at best happens to work, at worst targets a different directory, and otherwise fails.

    • Using .FullName here too bypasses the problem: Get-ChildItem $d.FullName

  • This problem has been reported in GitHub issue #6057 and I encourage anyone interested in a fix to make their voice heard there.
    A note on backward compatiblity: changing how [System.IO.FileInfo] and [System.IO.DirectoryInfo] stringify (stringifying as their .FullName property value) is probably too breaking a change, but changing the parameter binding as described in the linked GitHub issue is well worth considering, given that the current behavior is essentially broken.

---

^{[1] See this answer for details on the conditions under which this name-only stringification occurs in Windows PowerShell.}