MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1684

Can you add or use the Invoke-Sqlcmd PowerShell related commands with a SQL Server 2005 database server?

If so what would you have to do to get it working?

Thanks

Assuming you aren't going to install SQL Server 2008 yet, you can download the Microsoft Windows PowerShell Extensions for SQL Server from the Microsoft SQL Server 2008 Feature Pack Site at this URL: http://www.microsoft.com/downloads/details.aspx?FamilyID=228de03f-3b5a-428a-923f-58a033d316e1&DisplayLang=en  It says that you will also need to download SQL Server 2008 Management Objects (available on the same page).  There is also a link to get WIndows PowerShell 1.0.

Once you have downloaded the PowerShell Extensions you can install them into PowerShell by using these two commands:

add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100

I think there is a way that you can add these to your profile so you don't have to do it each time you launch PowerShell.

If you have access to SQL Server 2008 you can install PowerShell which will give you the SQLPS utility; then you have PowerShell and the SQL: Server 2008 support.

As far as connecting to a SQL Server 2005 database I can't find anyting in Books on Line that says you can or you can't.  I would assume you will be able to do that. 

With SQL Server 2008 you can launch PowerSheel from just about anywhere in SQL Server Management Studio.  I haven't seen anything that says there is a way to do that with SQL Server 2005 Management Studio.  I don't htink you can execute a PowerShell script in a SQL Agent job step with a SQL Server 2005 instance either.

Thanks for the reply.  I will attempt to install the Powershell Feature Pack on a SQL Server 2005 server and see how usable it is.

You can add the two add-pssnapin commands into your profile file and you can run PoSh scripts from SQL Agent. 

An example: "powershell H:\PO.ps1" (don't use the double quoutes) using the job step type of Operating System (CmdExec).

You can not run PoSh commands directly from within the job step which you can do in SS2K8.

Even installing the PowerShell feature pack would not install all the functionality in Management Studio but might give me the functionality within Powershell to use invoke-sqlcmd.

Follow up -

Powershell Feature Pack only works with PowerShell V2.