Knowledgebase

Note

When you want to run / edit / debug PowerShell scripts inside Visual Studio 2013 / 2015 you will have to install the “PowerShell Tools for Visual Studio” Visual Studio extension found at: https://visualstudiogallery.msdn.microsoft.com/c9eb3ba8-0c59-4944-9a62-6eee37294597

I wanted to run and debug a PowerShell script inside Visual Studio 2013 / 2015, on a machine that only had SQL Server 2014 installed as database server. This threw an exception: The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet http://blog.sqlauthority.com/2014/11/23/sql-server-fix-the-term-invoke-sqlcmd-is-not-recognized-as-the-name-of-a-cmdlet/

Turns out you have to update the PowerShell “PSModulePath”, so it can find the PowerShell modules containing the SQL Server 2014 cmdlets.

Temp fix:

http://stackoverflow.com/questions/29562742/invoke-sqlcmd-failing-after-automated-sql-install-with-powershell

Existing Powershell session isn’t aware about Sql’s modules that were just installed. Take a look at environment variable $env:PSModulePath. Compare new shell’s variable to existing and you should see a missing path like …\Microsoft SQL Server\110\Tools\PowerShell\Modules\.
For a work-around, modify the path to include module dir. Like so,

$env:PSModulePath += ";C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules"

If you want to persist this extra “Module load path”, you have to change the registry:

http://tomtalks.uk/2013/06/powershell-add-a-persistent-module-path-to-envpsmodulepath/

Now I can run and debug the following PowerShell script inside Visual Studio 2013 / 2015:

# This sccript requires the system to allow running powershell scripts, when not allowed the following code can be used to allow running powershell scripts.
# Set-ExecutionPolicy RemoteSigned

# The following import statement enables the SQL Server PowerShell cmdlets, like "Invoke-Sqlcmd".
Import-Module SqlPs

$serverInstance = "(localdb)\v11.0"
$database = "App"


function ExecuteSqlFile {
    [string]$file = $args[0]
    Invoke-Sqlcmd -ServerInstance $serverInstance -Database $database -InputFile $file
}

ExecuteSqlFile('C:\Temp\test.sql')