title: sql server tsql run powershell script
body: <p>I have created a powershell script that opens an excel file, formats a column, and then resaves the excel file.  This script works from powershell and the commandline, but when run in a stored procedure (tsql) it does nothing.  No errors either.  I've tried to run the powershell script from a job agent and it ran but nothing happens.  Do I have to do something to enable powershell scripts in sql server?</p>
  
  <p>Here is the code from the stored procedure:</p>
  
  <pre><code>DECLARE @SqlPowerShell AS VARCHAR(1000)
  set @SqlPowerShell = 'powershell.exe -ExecutionPolicy Bypass -File "C:\temp\Script.ps1"'
  
  EXEC xp_cmdshell @SqlPowerShell
  </code></pre>
  
  <p>Here is the powershell script:</p>
  
  <pre><code>#
  # Script.ps1
  #
  #param([string]$today="0") #Must be the first statement in your script
  $today=Get-Date -format Mddyyyy
  $file="c:\temp\TeamCurrentStockInventory_" + $today + ".xlsx"
  $xl=New-Object -ComObject "Excel.Application"
  $xl.DisplayAlerts = $FALSE
  #$xl.Visible = $TRUE
  
  $wb=$xl.Workbooks.Open($file)
  
  # Open/Edit first Sheet
  $ExcelWorkSheet = $xl.WorkSheets.item(1)
  #Select entire column
  $ExcelWorkSheet.Range("F:F").HorizontalAlignment = -4152 #right justified value
  
  $wb.SaveAs("c:\temp\TeamCurrentStockInventory_" + $today + "_v1.xlsx")
  $wb.Close()
  $xl.Quit()
  
  #Remove-Item $file
  
  # now you only need to release two.
  while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)){}
  while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)){}
  </code></pre>