One cool stuff I found out about Sql Server Agent is that it allows you to run PowerShell scripts in it. This gives you the opportunity to schedule the execution of PowerShell scripts even inside SQL Server.

This way I prepared a simple example to show you how to use PowerShell in SQL Server however i must say that this might not be the best example or the best practice in this kind of example, but it is enough to show you how to do it.

Step-by-step guide:

1. Open SQL Server management studio (This demo will be with the 2012 version)

2. Make sure SQL Server Agent is running using SQL Server Configuration Manager

image

3. In the SQL Server Agent select New –> Job

Job1

4. Enter a name for the job and a description if you want

Job2

5. Select the steps properties and now click on New to create a new step

job3

6. Select PowerShell in the step type

job4

7. In the command textbox, enter your powershell code or select open to insert an external script.

In this case the script will invoke a sql query and then save the output as an xml file.

01 #Invoca o comando sql que retorna um array com todos os registos obtidos em formato XML
02  $xmlDoc=Invoke-Sqlcmd -ServerInstance "." -Database "PowershellTestes"`
03  -Query "SELECT *
04  FROM [dbo].[PERSON]
05  FOR XML auto, ROOT('PERSONS')
06  "
07  
08 #Concatena todas as linhas do array de elementos retornados
09  #numa string com conteudo equivalente ao XML
10  $xmlString=''
11  $xmlDoc | %{
12  $xmlString+=$_[0]
13  }
14  
15 #Converte string para XML
16  $xmlDocument = [xml]$xmlString
17  
18 $xmlDocument.Save("c:\Temp\teste3.xml")

job5

8. You can schedule an execution timer but in this case i will just click Ok to finish

job6

9. Check if the job was created in your object explorer

job7

10. Start the job to execute it.

job8

The job will start and if everything was well done it will show you a success message

job9

11. Done. Check in your file system if the file was created and open it to see the result

job11

As you can see it is pretty easy to introduce PowerShell to SQL Server and its jobs.



See Also