{"id":1404,"date":"2012-09-18T00:00:00","date_gmt":"2012-09-18T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-posh-dba-solutions-using-powershell-and-sql-server\/"},"modified":"2021-08-24T13:40:06","modified_gmt":"2021-08-24T13:40:06","slug":"the-posh-dba-solutions-using-powershell-and-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/the-posh-dba-solutions-using-powershell-and-sql-server\/","title":{"rendered":"The PoSh DBA: Solutions using PowerShell and SQL Server"},"content":{"rendered":"<div id=\"pretty\">\n<p class=\"Start\">Why should a DBA learn PowerShell ? It is all about solutions. In this article, I want to explain how one can integrate PowerShell, TSQL, SQL Jobs and SQL WMI alerts into a complete solution. I will go further in this topic in my new written project along three great friends. Stay Tuned, as we will soon have a complete guide about day-to-day solutions for the DBA, using PowerShell and SQL Server.<\/p>\n<p>When you read about using PowerShell and SQL Server, you are usually learning about the way that you use PowerShell to access SQL Server. Sometimes, instead, you&#8217;ll want to use PowerShell directly from SQL Server to create solutions. You may want to do it from TSQL, getting data back in a form that can then be inserted into a table, or execute it on the server from SSMS. You might want to run PowerShell scripts from the SQL Server Agent, or to set up sophisticated alerts using WMI that then execute jobs that are written in PowerShell. I&#8217;ll be showing you how to do all this; but let&#8217;s take things in easy stages.<\/p>\n<h1>Running PowerShell scripts from SSMS Query Editor<\/h1>\n<p>Did you know that you can run your PowerShell cmdlets and functions, along with their parameters, very simply from the Management Studio (SSMS) Query Editor, executing them on the server? Yeah, by using xp_cmdshell. Before I start showing you how to use xp_cmdshell to run PowerShell cmdlets from within TSQL, I must make you aware that, by enabling <code>xp_cmdshell<\/code> on a server, you&#8217;re creating potential security issues. There are good reasons why <code>xp_CmdShell<\/code> is disabled by default.<\/p>\n<p>When using <code>xp_CmdShell<\/code> to run PowerShell in SSMS , you&#8217;ll Just need to remember three things:<\/p>\n<ul>\n<li>Use <em>&#8216;Results to text&#8217;<\/em> in SSMS, from the menu item <em>Query -&gt; &#8216;Results to&#8230;&#8217;<\/em> to see an easily-readable output from PowerShell.<\/li>\n<li>You are not running PowerShell on your own machine, but on the server that is hosting the SQL Server instance you&#8217;re connected to. The userID which you are using to open a connection will need execute permissions. You&#8217;ll need to make sure that your PowerShell profile has all the functions installed that you need to run. What account is this? If you&#8217;re using a SysAdmin account, then the PowerShell command will be executed under the &#8216;SQL Server service account&#8217;, which is the security context in which the SQL Server service is running. Otherwise, you&#8217;ll need to set up a proxy account on that server using sp_<code>xp_CmdShell<\/code>_proxy_account<\/li>\n<li>Make sure that PowerShell is installed to the correct version on the host server.<\/li>\n<\/ul>\n<p>Once you&#8217;ve enabled <code>xp_CmdShell<\/code>, and you have the necessary permissions to use it, PowerShell can give you valuable information easily. I&#8217;ll show you a couple of examples: Getting Disk space, and seeing what services are running.<\/p>\n<h3><strong>Seeing what services are running<\/strong><\/h3>\n<p>We&#8217;ll start simply by listing all the services on the server.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">xp_cmdshell 'PowerShell.exe -noprofile Get-Service'<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1561-afe27549-7ba7-4e89-a643-6fdc7cb22ab5.png\" alt=\"1561-afe27549-7ba7-4e89-a643-6fdc7cb22ab\" \/><\/p>\n<p>You have the full list of services, whatever their status. What if you wanted only those that had stopped? You&#8217;ll need to combine two CmdLets to do this in a pipeline , so it is now time to use a command-line parameter to run the command, the <strong>-Command<\/strong> parameter<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">xp_cmdshell 'PowerShell.exe -noprofile -command \"Get-Service | where {$_.status -eq ''Stopped''}\"'<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1561-21da0a4d-a58b-4be8-8649-111778fe15b5.png\" alt=\"1561-21da0a4d-a58b-4be8-8649-111778fe15b\" \/><\/p>\n<p>And with only a small change we can see all the SQL services that have stopped.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">xp_cmdshell 'PowerShell.exe -noprofile -command \"Get-Service -name *sql* | where {$_.status -eq ''Stopped''}\"'<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1561-f9d92fad-847f-46bc-ae6a-24e3b8647416.png\" alt=\"1561-f9d92fad-847f-46bc-ae6a-24e3b864741\" \/><\/p>\n<p>But you can also query a server remotely once it has been configured :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">xp_cmdshell 'PowerShell.exe -noprofile -command \"Get-Service -computername ObiWan -name *sql* | where {$_.status -eq ''Stopped''}\"'<\/pre>\n<h3>Getting the free disk space<\/h3>\n<p>Here, we are using a Get-Diskspace function (@sqlvariant) for the host of the SQL Server instance. This requires a function that you can download at the bottom of the article, and which will need to be placed on the server.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">xp_cmdshell 'PowerShell.exe -command \"get-diskspace .\"'<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1561-67df0a73-cb36-4b85-b913-7578ac548add.png\" alt=\"1561-67df0a73-cb36-4b85-b913-7578ac548ad\" \/><\/p>\n<p>To get the disk space for a different, remote, server, for example, use &#8230;<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">xp_cmdshell 'PowerShell.exe -command \"get-diskspace -servername ObiWan\"'<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1561-b7abc6e1-4e95-40ef-8351-4adf62c8f4ef.png\" alt=\"1561-b7abc6e1-4e95-40ef-8351-4adf62c8f4e\" \/><\/p>\n<p>To get the disk space for all the Servers into a file called Servers.txt<strong>:<\/strong><\/p>\n<pre class=\"theme:powershell-ise lang:ps\">xp_cmdshell 'PowerShell.exe -command \"get-diskspace -servername (get-content c:\\temp\\servers.txt)\"'<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1561-5ad90060-5554-47fd-ae8b-7c7104ca57df.png\" alt=\"1561-5ad90060-5554-47fd-ae8b-7c7104ca57d\" \/><\/p>\n<p>Alternatively, to get just the percentage disk space, you can also use <code>Get-Counter<\/code> and <code>\\LogicalDisk(*)\\% Free Space<\/code> to get all counter instances. You can do this locally, for the host of your instance &#8230;.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">xp_cmdshell 'PowerShell.exe -noprofile -command \"Get-counter -counter ''\\LogicalDisk(*)\\% Free Space'' | select -expand countersamples\"'<\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1561-16a3db12-c5a0-475a-aa0a-73ec88361432.png\" alt=\"1561-16a3db12-c5a0-475a-aa0a-73ec8836143\" \/><\/p>\n<p>&#8230;or for a remote server<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">xp_cmdshell 'PowerShell.exe -noprofile -command \"Get-counter -computername ObiWan -counter ''\\LogicalDisk(*)\\% Free Space'' | select -expand countersamples\"' <\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1561-91393337-15e4-432e-b1a3-f7837a464a0b.png\" alt=\"1561-91393337-15e4-432e-b1a3-f7837a464a0\" \/><\/p>\n<p>So can you do more than this and run scripts the same way? Well, no, because there is a limitation. You can&#8217;t use the &#8221; (double-quote) character, which is essential for PowerShell, because it is used in the command-line parameter to delimit the script-fragment being executed. To do this, you&#8217;ll need to save the script as a file and execute that.<\/p>\n<h1>Returning data from PowerShell<\/h1>\n<p>If you run a PowerShell CmdLet in <code>xp_cmdshell<\/code>, how do you get the data back into SQL as tabular data? We&#8217;ve shown you the output, but it is not immediately obvious as to how to read it. <code>Xp_cmdshell<\/code> actually returns a table consisting of a single column called &#8216;output&#8217;. You can insert it into a table using <code>INSERT..EXEC<\/code>, but <code>INSERT..EXEC<\/code> has certain restrictions. You cannot nest them and it cannot contain an output clause.<\/p>\n<p>However we can use this method to return an XML representation of the PowerShell objects being returned. All we then have to do is to shred it into a relational form and create a table.<\/p>\n<p>Taking a more refined version of the previous PowerShell command<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">DECLARE @XML_as_String VARCHAR(MAX)\r\nDECLARE @Percentage XML\r\n--create a table variable for the data to go into, preserving the order of insertion\r\nDECLARE @XML TABLE (TheXML VARCHAR(2000), theOrder INT IDENTITY(1,1) PRIMARY KEY)\r\n--insert the XML into the table, line by line\r\nINSERT INTO @XML(TheXML)\r\nEXECUTE xp_cmdshell '@PowerShell -noprofile -command \"Get-counter -counter ''\\LogicalDisk(*)\\% Free Space'' | select -expand countersamples | select @{Name=''Disk''; e={$_.InstanceName}}, @{Name=''%FreeSpace''; e={''{0:n2}'' -f $_.CookedValue}}|ConvertTo-XML -As string\" '\r\n--now assemble the XML as a string in the correct order\r\nSELECT @XML_as_String=COALESCE(@XML_as_String,'') + theXML \r\n  FROM @XML \r\n  WHERE theXML IS NOT NULL \r\n  ORDER BY theOrder \r\n--now save it into an XML variable \r\nSELECT  @Percentage = @XML_as_String\r\n-- the entity-attribute-value result that comes from the XML has to be pivoted\r\nSELECT MAX (CASE WHEN attribute='Disk' THEN VALUE ELSE '' END ) AS [Disk],\r\n       CAST (MAX (CASE WHEN attribute='%FreeSpace' THEN VALUE ELSE '' END )AS numeric(9,2)) AS [% Free Space]\r\nFROM        \r\n   (  \r\n   SELECT --shred the XML into an EAV table along with the number of the object in the collection\r\n     [property].value('(.\/text())[1]', 'Varchar(20)') AS [Value],\r\n     [property].value('@Name', 'Varchar(20)') AS [Attribute],\r\n   DENSE_RANK() OVER (ORDER BY [object]) AS unique_object\r\n   FROM @Percentage.nodes('Objects\/Object') AS b ([object])\r\n   CROSS APPLY b.object.nodes('.\/Property') AS c (property)\r\n   ) PowerShell\r\nGROUP BY UNIQUE_object<\/pre>\n<p>This will give the result<\/p>\n<pre>Disk                 % Free Space\r\n-------------------- ---------------------------------------\r\nc:                   91.38\r\nd:                   79.61\r\n_total               83.53\r\n(3 row(s) affected)\r\n<\/pre>\n<p>Which any DBA will recognise as data! What have we done here? We have chosen to create an XML representation of the report which was then returned to SQL Server line by line. We had to re-assemble it into an XML file and shred it in the way that we needed. This is laborious for an ad-hoc request but it makes a lot of sense for a scheduled monitoring job.<\/p>\n<h1>Running Scripts That has Posh Jobs on a SQL Agent Job<\/h1>\n<p>PowerShell can be run from the scheduler to do regular jobs such as ETL. Although this generally takes little more effort than testing it in the PowerShell ISE, just sometimes PowerShell gives you a culture-shock. Sometimes things happen that you don&#8217;t expect, even though they make sense when you think about it later. For example, I recently developed a script that created a lot of PowerShell Jobs. For some reason, when I ran it in the PowerShell Command-line console, it all worked fine. When I then ran it on the SQL Server scheduler, using a CMDExec jobtype, calling PowerShell, nothing happened: and there was no error message in Jobs History.<\/p>\n<p>The script invoked a process that retrieved all the windows updates applied to a list of servers in the past 24 hours, and saved the results into a SQL Server Table within a repository server. It was using runspaces, though what I&#8217;ll describe will be useful for anyone that is using background jobs. I was using PowerShell to create jobs that ran in parallel, one for each server I was getting information from. I was getting a list of servers from a file called &#8216;c:\\temp\\Servers.txt&#8217; and for each server name, I was starting a background job on the local computer. This job then obtained the windows update information for the server which was then filtered by a <strong>Where<\/strong> cmdlet for only those within the past day. The results were reported back to a <strong>tbl_WindowsPatches<\/strong> table in a SQL Server repository. The code is this:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-Content c:\\temp\\Servers.txt | % { \r\n\r\n  Start-Job -Name $_ -InitializationScript{Ipmo Functions -Force -DisableNameChecking} ` \r\n           -scriptblock {     \r\n            (    Get-WindowsUpdates -ComputerName $args[0] ` \r\n                                  | Where {$_.UpdateDate -ge ((Get-Date).adddays(-1))} ` \r\n                                  | select    CurrentDate, ` \r\n                                              ServerName, ` \r\n                                              UpdateClientApplicationID, ` \r\n                                              UpdateDate,` \r\n                                              UpdateDescription, ` \r\n                                              UpdateTitle\r\n            )   | Out-DataTable ` \r\n                | Write-DataTable   -ServerInstance R2D2 ` \r\n                            -Database SQLServerRepository -TableName tbl_WindowsPatches \r\n                                } -ArgumentList $_ \r\n} <\/pre>\n<p>You can download the <code>Get-WindowsUpdates<\/code> , <code>Out-Datatable<\/code> and <code>Write-DataTable<\/code> functions at the top of this article in the<code> Functions.psm1<\/code> file . In this case I have a module called <code>Functions<\/code> that join all these functions . As I am using PowerShell jobs and it runs in another runspace, these functions are not visible. So I need to explicit load them in , in the functions module, in the line<code> <br \/>\n$_ -InitializationScript{Ipmo Functions -Force -DisableNameChecking}<\/code><\/p>\n<p>Why should it have worked in the PowerShell console, but not when run from the SQL Server Agent? My first test was to create a .bat and run :<\/p>\n<pre>PowerShell.exe \"C:\\Temp\\Automation\\GetWindowsUpdates.ps1\"  <\/pre>\n<p>Ok. So what is happening ? Nothing was stored. My script was creating the jobs, each of which was running in its own independent runspace, and then it was closing the PowerShell session. The script seemed to run in an open command console, but not when it was closed immediately after the script was run. Were these separate jobs being closed prematurely when the parent session was closed?<\/p>\n<p>To check that this was the problem, I added, to the line in the .bat file that executed PowerShell, the parameter <strong>&#8211;<\/strong><code>noexit<\/code> so as to prevent the closure of the session&#8230;<\/p>\n<pre>PowerShell.exe -noexit \"C:\\Temp\\Automation\\GetWindowsUpdates.ps1\" <\/pre>\n<p>&#8230; and it worked. Why? My script was creating the jobs, each of which was running in its own independent runspace, and then closed the PowerShell session. The PowerShell jobs hadn&#8217;t been completed when the main session was closed, so nothing was returned to the table, but no error was raised. Why was the session closed before the jobs had completed? It was because the PowerShell jobs run in another runspace, but within the same session that was called. The session must not be closed until all PowerShell jobs finish.<\/p>\n<p>What do I need to do? All I have to do is to wait until all PowerShell Jobs are finished, it is as simple as that. I&#8217;d forgotten to add a &#8216;wait-job *&#8217;!<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-Job | Wait-Job | Out-Null  \r\nRemove-Job -State Completed<\/pre>\n<p>The final code is :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-Content c:\\temp\\Servers.txt | % { \r\n\r\n    Start-Job -Name $_ -InitializationScript{Ipmo Functions -Force -DisableNameChecking} ` \r\n                    -scriptblock {     \r\n            (    Get-WindowsUpdates -ComputerName $args[0] ` \r\n                                  | Where {$_.UpdateDate -ge ((Get-Date).adddays(-1))} ` \r\n                                  | select    CurrentDate, ` \r\n                                                ServerName, ` \r\n                                                UpdateClientApplicationID, ` \r\n                                                UpdateDate,` \r\n                                                UpdateDescription, ` \r\n                                                UpdateTitle\r\n            )     | Out-DataTable ` \r\n                | Write-DataTable   -ServerInstance R2D2 ` \r\n                            -Database SQLServerRepository -TableName tbl_WindowsPatches \r\n                                } -ArgumentList $_ \r\n} \r\nGet-Job | Wait-Job | Out-Null  \r\nRemove-Job -State Completed<\/pre>\n<p>I save this code into <code>C:\\Temp\\Automation\\GetWindowsUpdates.ps1 <\/code>on the server,<\/p>\n<p>The Command to run as a CMDExec SQL Agent Step is :<\/p>\n<pre>PowerShell.exe \"C:\\Temp\\Automation\\GetWindowsUpdates.ps1\" <\/pre>\n<h1>Outputting SQL Agent Job PowerShell in Job History<\/h1>\n<p>Although I&#8217;ve shown you how to get data from a PowerShell job that is running in a batch, there are times when all you need is a record of what a script did, so you can check afterwards<\/p>\n<p>If, for example, you have a SQL Agent PowerShell Job that deletes old files in a log shipping process, and you want to output a list of the files that were removed in the Job History you can just to use write-output. This script shows you what happens:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">$FilesRemoved = 'Files Deleted : ' \r\ngci \"c:\\test\\*.*\" | foreach { \r\n\r\n    $FilesRemoved += \"Name: {0}, \" -f $_.name \r\n    Remove-Item $_.fullname  \r\n\r\n} \r\nwrite-output $FilesRemoved<\/pre>\n<p>Then if you look at your Job History, you&#8217;ll then see the list.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1561-de9355b0-1a8e-4043-8e3f-47edf98dcdcd.png\" alt=\"1561-de9355b0-1a8e-4043-8e3f-47edf98dcdc\" \/><\/p>\n<p>The trick to get this to work with SQL Agent PowerShell Job, is that instead of using<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">    $FilesRemoved += Name: $($_.name)\"<\/pre>\n<p>you need to use format specifier :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">\"Name: {0}, \" -f $_.name<\/pre>\n<h1>Triggering PowerShell jobs with SQL WMI Alerts<\/h1>\n<p>This solution is using PowerShell scripts of course, SQL Server Jobs and a SQL WMI Alert<\/p>\n<p>Imagine that you have a download folder on a SQL Server host that has several files downloaded automatically by FTP. It is called FTPDownload. A file with a specific name is downloaded once a day. The contents of this file must be loaded into a staging table in another SQL Server.<\/p>\n<p>Firstly, lets create the Windows Query Language (WQL) query to monitor the specific file in a specific folder : for us the folder is <code>c:\\FTPDownload<\/code> and the file is FileImport.CSV<\/p>\n<pre>@wmi_query=N'Select * from __InstanceCreationEvent WITHIN 300 WHERE TargetInstance ISA ''CIM_DataFile'' AND  TargetInstance.Drive=''C:''and TargetInstance.path=''\\\\ftpdownload\\\\'' and TargetInstance.Name = ''c:\\\\ftpdownload\\\\FileImport.csv''', <\/pre>\n<p>To a complete explanation about the WMI and WQL I suggest you read the excellent ebook from my good friend and PowerShell Jedi Ravikanth Chaganti &#8211;<a href=\"http:\/\/www.ravichaganti.com\/blog\/?p=1979\">WMI Query Language via PowerShell<\/a><\/p>\n<p>Then Lets create the Job called IMPORTCSV with a PowerShell code called importCSV.ps1 on <code>c:\\FTPScripts<\/code> (you&#8217;ll need to get these CmdLets from SQLPSX)<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">try {\r\n   $DataImport = Import-Csv -Path \"c:\\FTPDownLoad\\FileImport.csv\" -ErrorAction Stop\r\n    $DataTable = Out-DataTable -InputObject $DataImport \r\n    Write-DataTable -ServerInstance YOURSERVER -Database YOURDATABASE -TableName YOURTABLE -Data $DataTable\r\n    $Msg = \"FileImport.csv successfully imported\"\r\n    Rename-Item -Path  \"c:\\FTPDownLoad\\FileImport.csv\" -NewName  \"c:\\FTPDownLoad\\FileImport_$(Get-date -format 'yyyyMMdd').csv\"\r\n    Write-Output $Msg\r\n} catch {\r\n      $ex = $_.Exception\r\n      Write-Error \"$ex.Message\"\r\n    throw \"Failure\"\r\n}<\/pre>\n<p>&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1561-1db166f0-1dd2-4fdc-b1b3-595e140bb788.png\" alt=\"1561-1db166f0-1dd2-4fdc-b1b3-595e140bb78\" \/><\/p>\n<p>Now it is time to create the SQL WMI Alert to monitor the arrival of this file in the FTP folder, based on our WQL :<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1561-71f992f3-5ccc-4fc7-9077-4ccebc58480b.png\" alt=\"1561-71f992f3-5ccc-4fc7-9077-4ccebc58480\" \/><\/p>\n<p>Now let&#8217;s set the response to the alert to execute the IMPORTCSV job<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1561-7cd1e2ad-168b-438c-98ce-49d366eddfd5.png\" alt=\"1561-7cd1e2ad-168b-438c-98ce-49d366eddfd\" \/><\/p>\n<p>Here is the code that creates the alert if you&#8217;d rather do it via TSQL and you already have the ID of the job that you wish to execute when the alert is fired.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">USE [msdb]\r\nGO\r\n\/****** Object:  Alert [CheckCSVFile]    Script Date: 8\/9\/2012 8:47:15 PM ******\/\r\nEXEC msdb.dbo.sp_delete_alert @name=N'CheckCSVFile'\r\nGO\r\n\/****** Object:  Alert [CheckCSVFile]    Script Date: 8\/9\/2012 8:47:15 PM ******\/\r\nEXEC msdb.dbo.sp_add_alert @name=N'CheckCSVFile', \r\n        @message_id=0, \r\n        @severity=0, \r\n        @enabled=1, \r\n        @delay_between_responses=0, \r\n        @include_event_description_in=0, \r\n        @category_name=N'[Uncategorized]', \r\n        @wmi_namespace=N'\\\\.\\root\\CIMV2', \r\n        @wmi_query=N'Select * from __InstanceCreationEvent WITHIN 300 WHERE TargetInstance ISA ''CIM_DataFile'' AND  TargetInstance.Drive=''C:''and TargetInstance.path=''\\\\ftpdownload\\\\'' and TargetInstance.Name = ''c:\\\\ftpdownload\\\\FileImport.csv''', \r\n        @job_id=N'990ef94a-a96d-41f2-809d-323c5e60d375'\r\nGO\r\n\r\n<\/pre>\n<p>And all is done . Every time that a file called Fileimport.csv is created on folder <code>c:\\FTPDownload<\/code> then, 5 minutes later, (the reason for the &#8216;WITHIN 300&#8217; clause in the WQL), the alert is fired and the job is then run. Why 300? Just to allow time for the file to arrive and be written to disk.<\/p>\n<p>If you have some problem with the file and the routine generates a error, the job will finish with no errors even you using Try-Catch. This is because the exit code is 0. The error will be recorder on the job history if you look for it, but that job should finish with an error being flagged to SQL Server Agent.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1561-f03bd79f-3b22-4be8-8f31-cb0561c0c16e.png\" alt=\"1561-f03bd79f-3b22-4be8-8f31-cb0561c0c16\" \/><\/p>\n<p>How to solve this ? Just add the line &#8216;throw &#8220;Failure&#8221; &#8216; into the catch block and then change the exit code to 1 :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">} catch {\r\n      $ex = $_.Exception\r\n      Write-Error \"$ex.Message\"\r\n    throw \"Failure\"\r\n}<\/pre>\n<p>and the Job will finish with an error :<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1561-096f9a13-b002-4608-bc85-ec9e0b0feb14.png\" alt=\"1561-096f9a13-b002-4608-bc85-ec9e0b0feb1\" \/><\/p>\n<p>Now that you&#8217;ve got that running, you can send an email using PowerShell or by the SQL job, informing whether the job was successful . it is up to you!<\/p>\n<p>For now that is it folks! I hope you guys liked it the Posh DBA series. Some cool stuff are coming .<\/p>\n<h2><strong>Acknowledgements:<\/strong><\/h2>\n<p>As usual, I cannot forget the awesome Jedi that is always helping this young Padawan and , of course, everyone that is needing help.<\/p>\n<p>My good friends Ravikanth Chaganti, Shay Levy, my editor Andrew Clarke and the mysterious Sir Phil Factor (thanks for the XML part, Phil) Sir Bob Beauchemin , my brother Mark Broadbent and all people that kindly give their time and knowledge to share.<\/p>\n<h2><strong>References<\/strong> :<\/h2>\n<ul>\n<li><a href=\"http:\/\/www.ravichaganti.com\/blog\/?p=1951\">Ravikanth Chaganti -Monitoring file creation using WMI and PowerEvents module<\/a><\/li>\n<li><a href=\"http:\/\/www.sqlskills.com\/blogs\/bobb\/\">Bob Beauchemin<\/a><\/li>\n<li><a href=\"http:\/\/tenbulls.co.uk\/\">Mark Broadbent -tenbulls<\/a><\/li>\n<li><a href=\"http:\/\/www.simple-talk.com\/author\/phil-factor\/\">Phil Factor<\/a><\/li>\n<li><a href=\"http:\/\/sqlvariant.com\/\">Aaron Nelson &#8211; SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V<\/a><\/li>\n<li><a href=\"http:\/\/sev17.com\/\">Chad Miller Sev17 SQL Server, PowerShell and so on<\/a><\/li>\n<li><a href=\"http:\/\/blogs.technet.com\/b\/heyscriptingguy\/\">Hey, Scripting Guy! Blog<\/a><\/li>\n<\/ul>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>PowerShell is worth using when it is the quickest way to providing a solution. For the DBA, it is much more than getting information from SQL Server instances via PowerShell; it can also be run from SQL Server as part of a system that helps with administrative and monitoring tasks. Laerte explains how.&hellip;<\/p>\n","protected":false},"author":221715,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4170,4364,4635,4150,4151,5651],"coauthors":[6819],"class_list":["post-1404","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-database-administration","tag-monitoring","tag-powershell","tag-sql","tag-sql-server","tag-the-posh-dba"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1404","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221715"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1404"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1404\/revisions"}],"predecessor-version":[{"id":73721,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1404\/revisions\/73721"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1404"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1404"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1404"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1404"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}