SQL Server Agent Meets PowerShell
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
http://powershellpt.files.wordpress.com/2013/01/image_thumb1.png?w=389&h=294
3. In the SQL Server Agent select New –> Job
http://powershellpt.files.wordpress.com/2013/01/job1_thumb.png?w=392&h=374
4. Enter a name for the job and a description if you want
http://powershellpt.files.wordpress.com/2013/01/job2_thumb.png?w=394&h=357
5. Select the steps properties and now click on New to create a new step
http://powershellpt.files.wordpress.com/2013/01/job3_thumb.png?w=393&h=355
6. Select PowerShell in the step type
http://powershellpt.files.wordpress.com/2013/01/job4_thumb.png?w=397&h=357
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") |
http://powershellpt.files.wordpress.com/2013/01/job5_thumb.png?w=405&h=316
8. You can schedule an execution timer but in this case i will just click Ok to finish
http://powershellpt.files.wordpress.com/2013/01/job6_thumb.png?w=411&h=319
9. Check if the job was created in your object explorer
http://powershellpt.files.wordpress.com/2013/01/job7_thumb.png?w=172&h=246
10. Start the job to execute it.
http://powershellpt.files.wordpress.com/2013/01/job8_thumb.png?w=127&h=246
The job will start and if everything was well done it will show you a success message
http://powershellpt.files.wordpress.com/2013/01/job9_thumb.png?w=246&h=176
11. Done. Check in your file system if the file was created and open it to see the result
http://powershellpt.files.wordpress.com/2013/01/job11_thumb.png?w=466&h=279
As you can see it is pretty easy to introduce PowerShell to SQL Server and its jobs.