Cannot run Invoke-Sqlcmd in a PS1 file but can on the console

1
votes

I'm currently on a machine with SQL server 2008 and according to this question (How to execute .sql file using powershell?), I've installed the powershell snapins to interact with SQL server.

The problem is that now I have it installed, I can run Invoke-Sqlcmd from the command line like this.

Invoke-Sqlcmd -inputfile "myTestFile.sql" -serverinstance '.\sql2008';

but if I put that line inside a PS1 file run and try to run it like so

powershell ./runSQLscript.ps1

I get this error

The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. At D:\TEMP\powershellTests\runSQLscript.ps1:1 char:14 + Invoke-Sqlcmd <<<< -inputfile "myTestFile.sql" -serverinstance '.\sql2008'; + CategoryInfo : ObjectNotFound: (Invoke-Sqlcmd:String) [], CommandNotFoundException + FullyQualifiedErrorId : CommandNotFoundException

1
sql-serverpowershellsql-server-2008-r2sqlcmd
In your PS1 file you would also need to Add-PSSnapin the SQL snapins so PowerShell knows to load this function before it's used. – Bluecakes
@Bluecakes - yes that fixed it! Please add that as the answer. Okay so add-snapins are not global commands and relevant to the context it's run in? – Diskdrive

1 Answers

4
votes

In your PS1 you need to Add-PSSnapin and load the SQL snapins.

Each Powershell window is a new environment in which modules and snapins are not yet loaded.