[VALIDATION]:: The 'Invoke-Sqlcmd' command was found in the module 'SqlServer' about azurestack HOT 14 CLOSED

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.

from azurestack.

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

from azurestack.

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

from azurestack.

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

from azurestack.

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!

from azurestack.

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

from azurestack.

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

from azurestack.

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 !!!!!!!!)

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

from azurestack.

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 ?

from azurestack.

azurestack\azurestackadmin

from azurestack.

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

from azurestack.

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!

from azurestack.

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

from azurestack.

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

Thanks!

from azurestack.