What Do I Need To Execute A Sql Server Powershell Module

I am creating an Octopus deploy that installs a windows service on a remote server. I am re-using a template from a web deploy project that sets up the web services and web applica

Solution 1:

SQLPS is auto installed in sql server 2012. The old versions need to install it from download Featurpack.

It's installed normally for sql server 2012 in the folder:

c:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS

If it's not available in that path require installing:

   Microsoft Windows PowerShell Extensions forMicrosoft SQL Server 2012

It's availabl at url: 32 bit: 64 bit:

in Poweshell 3 you can run the command (without path):

      Import-Module Sqlps -DisableNameChecking;

and it's valid if you use the path

Invoke-Sqlcmd is similar to the command line tool sqlcmd

You can run your script file using the command:

 sqlcmd -S <server> -U <user> -P <password>  -i <qry.sql>

Solution 2:

What you seek is the Microsoft SQL Server 2012 Feature Pack, available here:

https://www.microsoft.com/en-us/download/details.aspx?id=29065

If you expand the "Install Instructions" section, and then scroll about halfway down the page, you will see that one of the components listed there is the PowerShell Extensions, which contains the SQLPS module. Note that this component depends on the Shared Management Objects component (also on the same page), which in turn depends on CLR Types (also on the page, but at least that one is already installed on your server).

There are different versions available for other versions of SQL Server:

SQL Server 2014: https://www.microsoft.com/en-us/download/details.aspx?id=42295

SQL Server 2016: https://www.microsoft.com/en-us/download/details.aspx?id=52676