7

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.

1

4 Answers 4

7

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...

8
  • I'll try to figure out how to code an exit path on Monday. Aug 26, 2017 at 7:21
  • 2
    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. Aug 28, 2017 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.
    – user507
    Aug 30, 2017 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, 2017 at 18:01
  • @clifton_h no confusion here.
    – user507
    Sep 1, 2017 at 18:03
1

These did not work for me:

  • exit
  • Exit-PSHostProcess

One that worked is:

  • [Environment]::Exit(0)
1
  • yes, that is what I used, as per my comments above. As far as I remember (its been 3 years, and I'm doing Azure SQL DB now), I ended up using batch files. It was a lot easier for my successor to understand how to maintain. Aug 31, 2020 at 19:00
0

Here is an example of copying a database from one server to another.

You must use the (cmd) type for the Agent job

powershell.exe -ExecutionPolicy Bypass -Command "Copy-DbaDatabase -Source ServerA -Destination ServerB\InstanceName -WithReplace -Database DatabaseToCopy -BackupRestore -SharedPath ""\\backuphostname\Backups"""

You also must have DBA tools installed on the server. Using Powershell just run these commands: Install Chocolatey:

Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))

Install DBA Tools:

choco feature enable -n useFipsCompliantChecksums
Choco install dbatools
1
  • It would probably help if you said something about how this fixes the issue mentioned in the question above in some detail. For instance, why do you need to install Chocolatey? Also, the accepted answer above already suggests to use a CmdShell step instead of using a Powershell step.
    – Hannah Vernon
    Dec 14, 2020 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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