I am trying to use the SqlServer PowerShell module, available on the PowerShell Gallery.

After installing, either to -Scope CurrentUser or in an admin session, doing Import-Module SqlServer only results in three commands being imported into the session:

PS C:\Users\nreiling> Get-Command -Module SqlServer

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Alias           Decode-SqlName                                     21.0.17262 sqlserver
Alias           Encode-SqlName                                     21.0.17262 sqlserver
Function        SQLSERVER:                                         21.0.17262 sqlserver

This is in spite of the fact that doing (Get-Module -Name SqlServer -ListAvailable)[0].ExportedCommands.Values lists out all of the commands that are supposed to be included:

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Cmdlet          Add-RoleMember                                     21.0.17262 SqlServer
Cmdlet          Add-SqlAvailabilityDatabase                        21.0.17262 SqlServer
Cmdlet          Add-SqlAvailabilityGroupListenerStaticIp           21.0.17262 SqlServer
Cmdlet          Add-SqlAzureAuthenticationContext                  21.0.17262 SqlServer
Cmdlet          Add-SqlColumnEncryptionKeyValue                    21.0.17262 SqlServer
Cmdlet          Add-SqlFirewallRule                                21.0.17262 SqlServer
Cmdlet          Add-SqlLogin                                       21.0.17262 SqlServer
Cmdlet          Backup-ASDatabase                                  21.0.17262 SqlServer
Cmdlet          Backup-SqlDatabase                                 21.0.17262 SqlServer
                                        ...
Cmdlet          Test-SqlSmartAdmin                                 21.0.17262 SqlServer
Cmdlet          Write-SqlTableData                                 21.0.17262 SqlServer
Function        SQLSERVER:                                         21.0.17262 SqlServer
Alias           Decode-SqlName                                     21.0.17262 SqlServer
Alias           Encode-SqlName                                     21.0.17262 SqlServer

I tried installing an older version of the SqlServer module, as well as tried a different user account on my machine. Lots of reboots, new PS sessions, etc. Running Import-Module in verbose/debug mode doesn't throw any errors, it just imports only the function and aliases; none of the Cmdlets. The problem factor seems to be this particular Windows system, which is Windows 10 Pro 1803.

It is also worth noting that I'm only trying to use this module because the sqlps module that comes installed with SQL Server Management Studio stopped working on this machine. I don't know why that was the case; my guess was relating to a Windows or SSMS update, but in order to get to the point here where at least the SqlServer module installs without errors and warnings about clobbering, I removed that module from my $PSModulePath. Something else that broke seemingly at the same time was that my WSMan TrustedHosts list was reset (to blank), causing issues with PowerShell remoting that I had solved previously (this machine is not domain-joined).

How in the heck do I troubleshoot this?

  • Are you merely using this Windows 10 machine as a client for client level access, etc? Or does this Windows 10 machine have critical DBs on it that's used as a server? It's interesting that the sqlps stopped working but hopefully this is just a client machine rather that being used as a critical server since it is a Windows 10 machine rather than Windows Server. This sounds like a doozy though but too bad you couldn't try to get the sqlps working instead. If you don't have much luck here, don't forget about dba.stackexchange.com if needed though. – Pimp Juice IT Jun 3 at 0:04
  • @PimpJuiceIT Right, I only have the management tools installed on this machine. So far this smells like a PowerShell problem and not a SQL Server problem, which is why I didn't post in DBA.SE. I think I would like to test installing/importing a PS module that also uses compiled Cmdlets instead of just functions and see if that works, but I'm not sure where to look for that. – NReilingh Jun 3 at 0:08
  • What version of PowerShell are you running by the way? I assume 5.0 but might as well confirm I suppose. You can run ... $PSVersionTable – Pimp Juice IT Jun 3 at 0:11
  • 5.1.17134.48 — pretty sure that's the current stable version. – NReilingh Jun 3 at 0:12
  • I just tested this and I'm not getting the same result as you. I did run PowerShell ISE as administrator, and then installed it via Install-Module -Name SqlServer but I've already had the NuGet package installed or whatever for other modules I've installed that way rather than downloading manually, etc. if that even matters any. It also seems that I did not have to Import-Module SqlServer next though and just went right to Get-Command -Module SqlServer and it displayed all of them. – Pimp Juice IT Jun 3 at 0:21
up vote 2 down vote accepted

The source of the problem seems to be a broken installation of .Net 4.7.2 that occasionally happens with installing the 1803 Windows 10 Update.

The only way I've managed to resolve this is with a Windows 10 Repair Install. This reinstalls all the core OS files and configuration but does not remove your Apps or data. But you should still make sure backup any important documents before doing it.

Others have found success with:

You may want to try these steps before doing the Repair Install of Windows. They didn't work for my situation.

  • 1
    Amazing. The Repair Install worked just great — thanks so much for your efforts in figuring this out. – NReilingh Sep 8 at 0:38

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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