[VALIDATION]:: The 'Invoke-Sqlcmd' command was found in the module 'SqlServer'

[pameruoso]

Using the latest version of the script. This happens on the 1901 version too. Here is the command I'm using:

.\AzSPoC.ps1 -azureDirectoryTenantName "mytenant" -authenticationType AzureAD -downloadPath "C:\ASDKfiles" -ISOPath "C:\ws2016eval.iso" -asdkHostPwd 'P@ssw0rd1' -VMpwd 'P@ssw0rd123!' -azureAdUsername "username" -azureAdPwd 'password' `

the script seems running than crashes with the following:

VERBOSE: Loading module from path 'C:\ProgramFiles\WindowsPowerShell\Modules\SqlServer\21.1.18221\Microsoft.SqlServer.Assessment.Cmdlets.dll'. [4:35 PM]::[VALIDATION]:: The 'Invoke-Sqlcmd' command was found in the module 'SqlServer', but the module could not be l oaded. For more information, run 'Import-Module SqlServer'..Exception.Message

Launching manually invoke-sqlcmd: invoke-sqlcmd : The 'invoke-sqlcmd' command was found in the module 'SqlServer', but the module could not be loaded. For more information, run 'Import-Module SqlServer'.

Running manually import-module sqlserver: Import-Module : Could not load file or assembly 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The system cannot find the file specified. At C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18221\SqlServer.psm1:61 char:25

• ... $binaryModule = Import-Module -Name $binaryModulePath -PassThru

• 
  + CategoryInfo          : NotSpecified: (:) [Import-Module], FileNotFoundException
  + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.ImportModuleCommand

Hardware Windows server 2016 on dell poweredge 720

[mattmcspirit]

Hey - when you say 'the latest version of the script', do you mean the current master branch? And when you say '1901', do you mean the 1910 build of the ASDK?

This isn't an issue with my script, this must be something going on within your ASDK Host OS, as all my script is doing at that stage, is:

a) Installing the SQL Server Module from the PSgallery b) running a simple query

1) Have you installed anything on the host OS before running my script? Anything at all? 2) What is returned when you run Get-PSRepository 3) What is returned when you run Get-InstalledModule

Are you behind a proxy, or firewall which may be blocking access to certain sites?

Thanks, Matt

[pameruoso]

Using the last master branch. Tried 1901 too and same problem.

BTW I managed to solve with the following

Get-InstalledModule -Name SQLServer | Uninstall-Module -Force

Install-Module SqlServer -Force -Confirm:$false -AllowClobber -Verbose -ErrorAction Stop

Thank you

[mattmcspirit]

Thanks - must have been a transient issue when it first runs - this is what my script runs:

        Register-PsRepository -Default -Verbose:$false -ErrorAction SilentlyContinue
        Set-PSRepository -Name "PSGallery" -InstallationPolicy Trusted -Verbose:$false -ErrorAction SilentlyContinue
        if (!(Get-InstalledModule -Name SqlServer -ErrorAction SilentlyContinue -Verbose)) {
            Install-Module SqlServer -Force -Confirm:$false -AllowClobber -Verbose -ErrorAction Stop
        }

Sometimes strange things happen! Thanks for using the script!

[pameruoso]

Seems like it doesn't solve... the script asked me to reopen powershell and delede any folder starting with Azure from C:\programfiles\windowspowershell\modules. There were no Azure folder so i restarted again the script... and bam again

VERBOSE: Loading module from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18221\Microsoft.SqlServer.Assessment.Cmdlets.dll'. [6:48 PM]::[VALIDATION]:: The 'Invoke-Sqlcmd' command was found in the module 'SqlServer', but the module could not be l oaded. For more information, run 'Import-Module SqlServer'..Exception.Message

If I try to uninstall again it says that the module is in use so I delete manually the sqlserver folder from C:\programfiles\windowspowershell\modules.

I restart the script and again VERBOSE: Loading module from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18221\Microsoft.SqlServer.Assessment.Cmdlets.dll'. [6:48 PM]::[VALIDATION]:: The 'Invoke-Sqlcmd' command was found in the module 'SqlServer', but the module could not be l oaded. For more information, run 'Import-Module SqlServer'..Exception.Message

It's always complaining about Files\WindowsPowerShell\Modules\sqlserver\21.1.18221\Microsoft.SqlServer.Assessment.Cmdlets.dll'. Import-Module : Could not load file or assembly 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The system cannot find the file specified. At C:\Program Files\WindowsPowerShell\Modules\sqlserver\21.1.18221\SqlServer.psm1:61 char:25

• ... $binaryModule = Import-Module -Name $binaryModulePath -PassThru

• 
  + CategoryInfo          : NotSpecified: (:) [Import-Module], FileNotFoundException
  + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.ImportModuleCommand

[mattmcspirit]

So, the script works like this:

1) It installs the SQL Server PS Module - it needs this, as it uses a small SQLLocalDB to maintain progress of the script. My script simply checks if this is installed, and if it is not, it will install it:

        Register-PsRepository -Default -Verbose:$false -ErrorAction SilentlyContinue
        Set-PSRepository -Name "PSGallery" -InstallationPolicy Trusted -Verbose:$false -ErrorAction SilentlyContinue
        if (!(Get-InstalledModule -Name SqlServer -ErrorAction SilentlyContinue -Verbose)) {
            Install-Module SqlServer -Force -Confirm:$false -AllowClobber -Verbose -ErrorAction Stop
        }

You can install the SqlServer module yourself if you like - it doesn't really matter. My script will check if it exists, and if it does, it will not try to reinstall it.

2) Later, my script will run a step called 'CheckPowerShell' - this is where i check for Azure and Azs PowerShell - sometimes people go ahead and run this script on machines where they have already installed the incorrect/multiple versions of Azure/AzS PS, so i clean it up. You do not need to remove the SQlServer module from your system, or from C:\ProgramFiles\WindowsPowerShell\Modules - the script will only check for Azure*/AzS modules:

$psAzureModuleCheck = Get-Module -Name Azure* -ListAvailable | Where-Object {$_.Name -ne "AzureStackInstallerCommon"}
$psAzsModuleCheck = Get-Module -Name Azs.* -ListAvailable

If it finds them, it will clean them. It will not touch SQLServer module.

3) If it does clean up, it asks you to start a new PS window, close old ones, and then rerun the script. It will then install the correct AzureRM and AzS modules for you.

My advice right now would be as follows:

1) Close all PS Windows, and open a new one 2) Install SQLServer module manually as you did earlier. Check you can load the module by running import-module sqlserver -Verbose 3) Then run my script - it should detect SQLServer module is installed but should proceed without needing to make any changes. It will then go ahead and install the AzureRM/AzS modules during the 'InstallPowerShell' step.

Thanks, Matt

[pameruoso]

Ok new update.

1. closed everything

2. opened a single powershell (not administrative) with invoke-sqlcmd and worked

3. opened an administrative powershell with invoke-sqlcmd and failing.

4. uninstalled module sqlserver

5. installed module with install-module sqlserver -scope allusers -allowclobber -force

6. launch invoke-sqlcmd and import-module sqlserver is working

7. launch the script with administrative powershell

8. it works until it starts doing the jobs (finally :D !!!!!!!!)

9. 

10. 

11. 

failed. As you can see invoke-sqlcmd is working... this is driving me mad because like you said seems like a transitient stuff that's interrupting everything

[mattmcspirit]

OK, I haven’t seen this before, but if you can’t run the script as admin, it will fail.

Who are you logged in as to the ASDK host? Administrator, or azurestack\azurestackadmin ?

[pameruoso]

azurestack\azurestackadmin

[pameruoso]

seems running better now:

uninstalled sql server module and reinstalled an older version:

Install-Module -Name SqlServer -RequiredVersion 21.1.18080

Picked an old random version instead of using the last one from 5 days ago (21.1.18221)

Maybe something changed in that module that breaks everything.

Will update you as soon as it completes (fingers crossed).

Reading the changelogs seems like that with the new versions of the sqlserver module you've to use powershell core 6.X.X. indeed :

• Initial support for Invoke-Sqlcmd on .Net Core (-DisableCommand not supported yet). Not all sqlcmd command supported on PS5 are available yet. Requires PowerShell 6.2+.

[mattmcspirit]

That is strange, as i have a deployment from a few days ago on a 2002 ASDK that have the newest version of SqlServer module, and that worked fine

Also, while not all commands would work, you would assume you could import the module at least!

I'll do another deployment later and see if the same issue arises. Thanks for the update!

[pameruoso]

Are you using powershell core? or powershel 5.1 (the standard one)

[mattmcspirit]

Just the standard PS that comes on the ASDK Host OS. As soon as the ASDK is finished deployment, i run my script in an ISE. Nothing else is installed before I run my script.

[pameruoso]

By using the sqlserver pws module version 21.1.18080 the issue didn't come up anymore and the script completed successfully. I think we can close this definitely

[mattmcspirit]

Thanks - i'll monitor this going forward and hard-code the version if i see more issues.

Thanks!