5

I'm quite new to PowerShell, and now I've found dbatools.io, where I would like to run a PowerShell task in SQL Agent like

$ExportPath = $env:TEMP + '\DriveSpace.csv'
$datatable = Import-Csv $ExportPath | Out-DbaDataTable
Write-DbaDataTable -SqlServer MyServer -Database Utils -InputObject  $datatable -Table dbo.FreeSpaceOnDiskDrive -AutoCreateTable

It starts, but it never finishes. I've also tried to have a task that is doing this:

Get-DbaDatabaseSpace -SqlServer MyServer -IncludeSystemDBs | Out-DbaDataTable | Write-DbaDataTable -SqlServer MyServer -database utils -Table dbo.DiskSpaceExample -AutoCreateTable

Same problem. It runs ok in Windows PowerShell ISE, but just hangs in SQL Agent. So now it has created a new table for me, and populated it with data, but the job just keep on running.

  • When I've seen this happen it's usually because Powershell was waiting for user input for one reason or another. – David Fowler Aug 25 '17 at 9:20
  • 2
    I'd suggest creating a bat file that invokes Powershell and executes your script. You can temporarily put a pause at the end of your bat file to ensure that your Powershell script runs successfully. When you've got the bat file running to your satisfaction, remove the pause command and modify your Sql Agent job to use cmdexec instead of Powershell. – Scott Hodgin Aug 25 '17 at 12:32
  • 2
    Try adding an "exit" to the end of the PowerShell script. It would seem that the shell is not exiting when the script is complete. – Tony Hinkle Aug 25 '17 at 15:48
  • @ScottHodgin; yes, I have tried with a bat file, and that did work, but I would rather do without a bat file, as it seems so much last-century. – Henrik Staun Poulsen Aug 28 '17 at 8:27
  • @TonyHinkle; yes, but for some reason I had to use [Environment]::Exit(0) to get it to work. – Henrik Staun Poulsen Aug 28 '17 at 8:28
5

The problem you are experiencing is with the PowerShell subsystem in SQL Server Agent. It is a bit flakely with using other modules becuase you are put in the context of the SQL Server PowerShell Provider (SQLPS.exe). So it works the same way as if you opened up sqlps.exe and then try to execute your code.

One thing to keep in mind with dbatools module is that it will conflict with both SQLPS and the sqlserver module that MS now maintains separate for SQL Server. Last I checked the main thing that it conflicted against was TEPP that we have in the module now, it just can't load that code. [Caveat: I'm a major contributor to this module.]

The dbatools module has custom types and styles built in so when you run the scripts under PowerShell host that also has SQLPS or the SQLServer module imported your results will vary.

To utilize dbatools in a SQL Agent step make sure you only use the CmdExec subsystem (step type) and then call PowerShell host to execute your code. If you do not want to maintain a file for each script you can put your code in a SQL Agent CmdExec step in the manner illustrated below, but more complex script it is easier to maintain via files.

USE [msdb]
GO

/****** Object:  Job [dbatools_example]    Script Date: 2017-08-30 8:53:15 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2017-08-30 8:53:15 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'dbatools_example', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [dbatools_command]    Script Date: 2017-08-30 8:53:15 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'dbatools_command', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'CmdExec', 
        @command=N'powershell.exe -ExecutionPolicy Bypass -Command "Import-Module dbatools; $server = ''manatarms''; Get-DbaDatabaseSpace -SqlInstance $server -IncludeSystemDbs | Out-DbaDataTable | Write-DbaDataTable -SqlInstance $server -Database db1 -Table dbo.FreeSpaceOnDiskDrive -AutoCreateTable"', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Running the job above gives me this in my database and table db1.dbo.FreeSpaceOnDiskDrive

enter image description here

2

You misunderstand how the SQL Agent works. The powershell is run under its own window outside the Instance.

This means until the operation reports back as finished or a failure error occurs, the powershell operation will sit in perpetuaty. If you were to attempt to close the session, it would hang indefinitely until you closed the shell. In a few cases, this might even be impossible if you do not correctly stop the right process that the shell is firing from.

For example, and don't do this, if you ran a powershell with the pause operation, your poor shell would just hang indefinitely since you do not have access to the console that the Agent opened the powershell from.

  • Use SQLCMD

Not only is this supported right in the docs, it will specifically close after the operation is finished. You can even set all kinds of steps, too, and documentation is rich.

  • Continue the use of powershell

Only, hard-code an exit path. Do not rely on an operation to return back to the caller. You make sure it does.

In fact, this is the number one rule of coding: Do not assume your code works unless explicitly guaranteed.

  • Use another method.

Whatever the approach: DAC, ODBC, bcp, connections, Integration Services (SSIS) connections

SSIS uses packages, great for repeating operations, too.

AND MORE...

  • I'll try to figure out how to code an exit path on Monday. – Henrik Staun Poulsen Aug 26 '17 at 7:21
  • 1
    It did not work with just typing "exit", I had to use [Environment]::Exit(0), to get it to work. Thank you for sending me in the right direction. – Henrik Staun Poulsen Aug 28 '17 at 8:22
  • This is not 100% accurate: "The powershell is run under its own window outside the Instance." It depends on what type of subsystem you are executing your code under. If you use a PowerShell type then it puts in the context of the SQL Server provider within SQLPS, this is the host that SQL Server created not PowerShell.exe. If you run CmdExec step then you are in the context of a command prompt and have to call PowerShell.exe yourself. – Shawn Melton Aug 30 '17 at 13:39
  • @Shawn.Melton I can see where the confusion can be. However, how else do we use the SQL Server Agent normally? The important point is that outside of guessing at the powershell opened by the agent, there is no good way to see the console. Did you test to see this? Or are you making assumptions about how the Agent fires off the subsystems. They are always external to the Instance. – clifton_h Aug 30 '17 at 18:01
  • @clifton_h no confusion here. – Shawn Melton Sep 1 '17 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.