Script works in ISE but not SQL Agent job. Also works on other servers.

[bkhalliburton]

I have a script with dbatools commands that runs great in ISE. When I run it in a SQL Agent job I get this error.
The 'Write-DbaDataTable' command was found in the module 'dbatools', but the module could not be loaded
I do run it as a CMDExec type. It also works on other servers so I know it works. The service account running SQL Agent is a sysadmin. How to I identify what is different on this server from the other servers that keeps it from finding dbatools modules?

[twsavaleo]

Hi, this has nothing to do with DBAtools. :)

Your issue is Your user default path for powershell modules is different from what it is for SQL Server. It expects the modules to be installed @ C:\Program Files\PowerShell\Modules unless otherwise configured on SQL Server install.

Besides that, dont run Powershell as cmdexec. It just complicates error handling without offering an upside. Here's a snippet of how we execute powershell in SQL Server Agent.

Example;
$erroractionpreference = "Stop"
pwsh -Command {
$SqlInstance = Connect-DbaInstance -SqlInstance $server -Database $database -TrustServerCertificate;
$cmd=@"
select helloworld='Hello world'
"@
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $cmd
}

I.E. We're passing the command as a scriptblock to Powershell Core, and instructing to stop on errors, as powershell otherwise will report success even if the query fails.

SQL Server cannot execute powershell Core (pwsh) directly, so we setup the job step as powershell and tell that to execute pwsh.

Hopes this helps.

[bkhalliburton]

It is installed at this location and it shows in the environment variables . C:\Program Files\WindowsPowerShell\Modules\dbatools

[bkhalliburton]

Found the issue. dbatools.library was installed at the wrong location. Did an uninstall\reinstall and all works now.

[twsavaleo]

also, look at remoting regarding the "other servers" issue - Happy learning!