Why am I getting an error with my PowerShell Invoke-SQLCMD InputFile?

Question

I am trying to invoke SQLCMD from a PowerShell script but Powershell does not seem to like how I am passing -InputFile to Invoke-Sqlcmd. MY code looks something like this:

$files = Get-ChildItem -Path $PSScriptRoot -Recurse | Where-Object { $_.Name -match $regex};
Invoke-Sqlcmd -InputFile $files[0].FullName

$files is never empty as printing $files[0].FullName outputs a string like "D:\foo\foo.sql". When my application reaches this line, it throws an error pointing to the same line that I use Invoke-Sqlcmd and the character of the "$" in "$files".

I receives these two error messages:

Invoke-Sqlcmd : The pipeline has been stopped.
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], PipelineStoppedException
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Invoke-Sqlcmd : An expression of non-boolean type specified in a context where a condition is expected
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Is there an issue with how I am feeding the filepath of the script I want to run? What would be the correct format. I have also tried to use .PSPath instead of .FullName but I get an error saying that the file path is not supported.

This is an error that is the result of commands in your script, as can be recognized from the SqlError, and so it probably has nothing to do with what you do in PowerShell. You can get this, for example, with SELECT 1 WHERE 1, as 1 is not valid as a condition. — Jeroen Mostert

postanote postanote · Accepted Answer · 2020-01-17T17:31:36

That message is pretty specific and saying that Invoke-SqlCmd does not like what you are passing in. Variant types being used passed around, are important.

This ...

$files = Get-ChildItem -Path $PSScriptRoot -Recurse

... of course, gets populated before anything else happens. Regardless of what you do afterward.

FYI, as for this...

Where-Object { $_.Name -match $regex};

... you really don't need that semicolon, in PowerShell at all except in specific cases. The semicolon is a code terminator, normally used in the consolehost window to keep things on one line, but not something really needed for scripts or modules, in most cases.

You will see it used, by folks coming from other languages, because of habits, and from folks trying to cram everything on one line, and calling it a one-liner, which it really isn't.

Try it this way instead.

(Get-ChildItem -Path $PSScriptRoot -Filter $RegEx).FullName[0]

So, you'd end up with something a bit more direct like this...

$regex = '*.sql'
(Get-ChildItem -Path $PSScriptRoot -Filter $RegEx).FullName[0] | 
Invoke-Sqlcmd -InputFile $PSItem

Thus, no reason for the additional variable and that added Where-Object and the dot notation in the Invoke-Sqlcmd call.

You also could have done this...

Invoke-Sqlcmd -InputFile $files[0].FullName

... this way

Invoke-Sqlcmd -InputFile ($files.FullName)[0]

Why am I getting an error with my PowerShell Invoke-SQLCMD InputFile?

1 Answers