Running Powershell scripts through SQL

I haven't had any problem doing this and even created a couple of blog posts:

http://sev17.com/2009/04/05/executing-powershell-in-sql-server (mirror)

http://sev17.com/2010/11/29/executing-powershell-in-sql-server-redux (mirror)

The one thing that I'm doing differently is using the -command parameter with a file name instead of -file, but that shouldn't make a difference. I'm also enclosing the file name in double quotes but this shouldn't make a difference either if the script file has not spaces in the file path.

Outside of that I would need to see what your script is doing. For instance is it connecting to other machines? Can you run a simple command in powershell like 'powershell -command get-command'?

It would seem that i was not loading the snapins correctly. Although my SQLsnapins were loaded for the first session it wasnt passing it to the second PS script that was running invoke-SQLcmd. My second script did add the cmdlet snapin but that may not have been enough.

It should have worked but for whatever reason, adding the script block from here fixed it. :/

http://msdn.microsoft.com/en-us/library/cc281962.aspx

Thanks to those that responded.

What am i doing wrong?

I think there is an error in the example provided. I would have expected:

xp_cmdshell 'powershell.exe -ExecutionPolicy Unrestricted -file c:\script.ps1'

Because: "File must be the last parameter in the command, because all characters typed after the File parameter name are interpreted as the script file path followed by the script parameters and their values."

Source: PowerShell.exe Command-Line Help