November 25, 2014 at 8:25 am
I am trying to execute a powershell script from sql. The powershell script works fine when tested from powershell console. But when i kick start it from SSMS it fails. I am using the following script to run the ps script. Execution policy is set to remotesigned on the server. Any suggestions ??
declare @cmd varchar(2000)
set @cmd='sqlps -c ' + '"c:\powershell-scriptsotification.ps1"'
print @cmd
exec master..xp_cmdshell @cmd
November 25, 2014 at 8:43 am
You have to have the sqlps module if you're running this from standard PowerShell. Here's the topic on MSDN. I know you're calling it from SQL Server, but because you're just using xp_cmdshell, it makes it a standard PowerShell environment. You can edit the config for the server so that sqlps is automatically loaded.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 25, 2014 at 9:23 am
Grant Fritchey (11/25/2014)
You have to have the sqlps module if you're running this from standard PowerShell. Here's the topic on MSDN. I know you're calling it from SQL Server, but because you're just using xp_cmdshell, it makes it a standard PowerShell environment. You can edit the config for the server so that sqlps is automatically loaded.
I imported the module,but got the same error--
c:\powershell-scripts\psemail.ps1 : The term
'c:\powershell-scripts\psemail.ps1' 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.
November 25, 2014 at 9:41 am
That almost sounds like a permissions issue on the script location. Just for an experiment, try placing it in the SQL Server \bin directory and see if you can execute it there. Not saying that's a good idea. In general, I really dislike calls to xp_cmdshell for anything.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply