How to convert this sqlcmd script to an Invoke-Sqlcmd script?

For this answer, assume that the directory C:\Users\iain.CORP\SqlcmdQuestion exists and that executing dir at that location produces the following output, as implied by the question:

    Directory: C:\Users\iain.Corp\SqlcmdQuestion


Mode                LastWriteTime     Length Name
----                -------------     ------ ----
-a---        26/09/2012     15:30         27 ExampleQuery.sql
-a---        26/09/2012     15:30         61 ExecQuery.cmd
-a---        26/09/2012     15:34        172 ExecQuery.ps1

PowerShell ignores the working directory by design

My question has a false premise:

How do I make Invoke-Sqlcmd use the current working directory instead of the default working directory?

The cmdlet does use the current working directory. The problem is that I didn't change the working directory at all in my PowerShell session.

In PowerShell, cd is an alias for the Set-Location cmdlet. You can prove this using the Get-Alias cmdlet:

Get-Alias cd

Output:

CommandType     Name                                                Definition
-----------     ----                                                ----------
Alias           cd                                                  Set-Location

Alex Angelopoulos explains:

[A]lthough PowerShell's location is analogous to the working directory, the location is not the same thing as the working directory. In fact, PowerShell doesn't touch the working directory.

Set-Location does not set the working directory. It sets the working location, which is a similar but distinct concept in PowerShell.

You can prove this by inspecting the working directory using the .NET property Environment.CurrentDirectory after setting the working location using cd as in the question:

cd C:\Users\iain.CORP\SqlcmdQuestion
Environment::CurrentDirectory

Output:

Z:\

I would guess this design decision was made to be consistent. The working directory would be undefined when, for example, the working location were set to a registry hive.

Invoke-Sqlcmd violates this design principle

Invoke-Sqlcmd violates PowerShell's general design principle to use the working location rather than the working directory. Most cmdlets use the working location to resolve relative paths, but Invoke-Sqlcmd is an exception.

Using the ILSpy disassembler and a little intuition to inspect the containing assembly Microsoft.SqlServer.Management.PSSnapins, I believe I have found the reason for the error.

I believe that the cmdlet's parameter -InputFile is implemented by the method IncludeFileName. ILSpy's disassembly of the method looks like this:

// Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor
public ParserAction IncludeFileName(string fileName, ref IBatchSource pIBatchSource)
{
    if (!File.Exists(fileName))
    {
        ExecutionProcessor.sqlCmdCmdLet.TerminateCmdLet(new FileNotFoundException(PowerShellStrings.CannotFindPath(fileName), fileName), "ExecutionFailureException", ErrorCategory.ParserError);
        return ParserAction.Abort;
    }
    BatchSourceFile batchSourceFile = new BatchSourceFile(fileName);
    pIBatchSource = batchSourceFile;
    return ParserAction.Continue;
}

Invoke-Sqlcmd uses the .NET method File.Exists to check whether the specified input file exists. The method's documentation remarks that relative paths are resolved using the working directory:

The path parameter is permitted to specify relative or absolute path information. Relative path information is interpreted as relative to the current working directory. To obtain the current working directory, see GetCurrentDirectory.

This suggests that File.Exists would return false in this case, which would cause the error message seen in the question. You can prove this by executing the method directly from the prompt:

cd C:\Users\iain.CORP\SqlcmdQuestion
[IO.File]::Exists('ExecQuery.sql')

Output:

False

The method returns false, so the cmdlet terminates with a 'file not found' error.

You can work around the unusual behavior

There are two workarounds for Invoke-Sqlcmd using the working directory instead of the working location to resolve relative paths:

1. Always use an absolute path as the value of the -InputFile parameter. CandiedCode's answer shows how to do this.
2. Set the working directory and use a relative path.

I solved the problem without side-effects by modifying ExecQuery.ps1 like this:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

$RestoreValue = [Environment]::CurrentDirectory
[Environment]::CurrentDirectory = Get-Location
Invoke-Sqlcmd -InputFile 'ExampleQuery.sql' -Variable "ComputerName = $Env:COMPUTERNAME"
[Environment]::CurrentDirectory = $RestoreValue

I see this output:

Column1
-------
SKYPC0083

Success!

The new script sets the working directory to match the working location before executing Invoke-Sqlcmd. To avoid unintended side-effects of changing the working directory, the scrtipt restores the working directory value before completing.

Setting the current directory is described in this Channel 9 thread. The example there uses the Directory.SetCurrentDirectory method, but I find it simpler to set the property directly.