Mock CommandNotFoundException: Could not find Command Invoke-Sqlcmd

[nohwnd]

          I have a similar issue. Here is the diagnotic log

Mock: Setting up default mock for Invoke-Sqlcmd.
Mock: We are in a block, one time setup or similar. Returning mock table from test block.
Mock: Resolving command Invoke-Sqlcmd.
Mock: Searching for command Invoke-Sqlcmd in the script scope.
Mock: Did not find command Invoke-Sqlcmd in the script scope.
Describing Test Script
[-] Describe Test Script failed
CommandNotFoundException: Could not find Command Invoke-Sqlcmd
Tests completed in 8.2s
Tests Passed: 0, Failed: 1, Skipped: 0 NotRun: 0

And here is where I call it

$params = @{
'ServerInstance' = $TargetServer;
'Database' = $TargetDatabaseName;
'Username' = $sqlCredential.UserName;
'Password' = $sqlCredential.GetNetworkCredential().Password;
'Query' = $script
}
Invoke-Sqlcmd @params

Originally posted by @ertanergunswapfiets in #1592 (comment)

[nohwnd]

@ertanergunswapfiets moved your issue here, from the closed issue. Can you plese post how you define the mock? And are you trying to mock in some module scope?

[ertanergunswapfiets]

First of all the script I am trying to test is running as a Runbook in Azure so it is not a function. I thought it was the problem and I put it it in a function to test it. here is the script file i am trying to test now script.ps1:

function Runbook {
  param (
      [Parameter (Mandatory= $true)]
      [string]$TargetDatabaseName,
      [Parameter (Mandatory= $true)]
      [string]$TargetServer
  )
Import-Module Az.Sql

$script = @"the script"

$sqlCredential = Get-AutomationPSCredential -Name "credentials"

$params = @{
  'ServerInstance' = $TargetServer;
  'Database' = $TargetDatabaseName;
  'Username' = $sqlCredential.UserName;
  'Password' = $sqlCredential.GetNetworkCredential().Password;
  'Query' = $script
}

Invoke-Sqlcmd @params

}

Originally the runbook funtion didnt exist so I just surrounded the actual script with a funtion.

Here is the test file script.Tests.ps1:

BeforeAll {
   $scriptFile = $PSCommandPath.Replace('.Tests.ps1', '.ps1')
   . $scriptFile
}

Describe 'Test Script' {
    BeforeAll {
        Mock Get-AutomationPSCredential -ParameterFilter { $Name -eq 'credentials' } -MockWith {
        $username = 'testUser'
        $password = ConvertTo-SecureString 'MySecretPassword' -AsPlainText -Force
        $credential = New-Object System.Management.Automation.PSCredential ($username, $password)
        return $credential
    }

    Mock Invoke-Sqlcmd -MockWith {  result=1 }
    
    It 'It should not return error' {
        Runbook "test" "test" | Should -Be 1
   }
}

I also tried Mock command with giving ModuleName CommandName without MockWith but i couldn't succeed.

This is the output of test:

> Invoke-Pester ./runbooks/AnonymiseDB.Tests.ps1 -Output Diagnostic                     
Pester v5.5.0                                           
Discovery: Starting test discovery in 1 test containers. 

Starting discovery in 1 files.
Discovery: Discovering tests in /deducted/runbooks/script.Tests.ps1 
Discovery: Found 1 tests in 12 ms 
Discovery: Processing discovery result objects, to set root, parents, filters etc. 
Discovery found 1 tests in 23ms.
Discovery: Test discovery finished. 
Running tests.

Running tests from '/deducted/runbooks/script.Tests.ps1'
Mock: Setting up parametrized mock for Get-AutomationPSCredential. 
Mock: We are in a block, one time setup or similar. Returning mock table from test block. 
Mock: Resolving command Get-AutomationPSCredential. 
Mock: Searching for command Get-AutomationPSCredential in the script scope. 
Mock: Did not find command Get-AutomationPSCredential in the script scope. 
Describing Test Script
[-] Describe Test Script failed
  CommandNotFoundException: Could not find Command Get-AutomationPSCredential
Tests completed in 163ms
Tests Passed: 0, Failed: 1, Skipped: 0 NotRun: 0
BeforeAll \ AfterAll failed: 1
  - Test Script

I have 2 functions/commands to mock, one is Get-AutomationPSCredential and other is Invoke-Sqlcmd. When I comment out one I confirm that the other also fails so issue is with mocking both.

[fflaten]

The original commands has to be available in your session for Pester to be able mock them, as it needs to see which parameters to copy etc. Usually you'd import the necessary modules/dependencies before invoking the tests.

If you don't have access to the modules containing Get-AutomationPSCredential and Invoke-Sqlcmd, you could add some fake functions inside BeforeAll so they exist prior to calling the Mock-command.

[johlju]

you could add some fake functions inside BeforeAll

@ertanergunswapfiets If you need stub function for Invoke-SqlCmd you can use the stub module ones here (to run unit tests with): https://github.com/dsccommunity/SqlServerDsc/tree/main/tests/Unit/Stubs

Or, create your own using the steps in the README.