How to execute a PowerShell file from stored procedure
<p>How do I execute a PowerShell file from inside a stored procedure?</p>

<p>I have not had any luck executing the PowerShell file from within a stored procedure.</p>

<pre><code>exec xp_cmdshell 'powershell ""E:\PowershellScripts\todayErrorLog.ps1""'
</code></pre>

<p>When I run the stored procedure, I get access is denied errors on the file. I know the ps1 file itself is good because I can execute it both on the command line and as an Agent job. So I figure it's a matter of giving the stored procedure sufficient permissions. </p>

<p><a href="https://i.stack.imgur.com/IgwPy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IgwPy.png" alt="Access denied errors"></a></p>

<p>Previously, I made sure to enable <code>xp_cmdshell</code> with this script</p>

<pre><code>-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
GO  

-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  

-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  

-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO  
</code></pre>

<p>What else do I need to do in order to execute the PowerShell script file from the stored procedure?</p>

<p>This is the stored procedure:</p>

<pre><code>SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[RefreshErrorQuery] 
    -- No parameters    
AS
BEGIN
    SET NOCOUNT ON;
    -- Step 1
    exec xp_cmdshell 'powershell ""E:\PowershellScripts\todayErrorLog.ps1""'

    -- Step 2: TODO: Run SSIS package
END
</code></pre>