Import-Module : The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory

Question

Well, hello there!

I'm working on a Script to get the Sql Job History and need to use the "SqlServer" Module. It's installed but I can't import it due to the Error Message above. When I got to the Modules Path, the Folder "SqlServer" exists and isn't empty. Don't get the problem here.

Thanks in advance!

Solution

I just ran into this on SQL Server 2017 (on Windows Server 2016) and found your question in the process. I then went and fired up our older SQL Server 2014 (Windows Server 2012) machine and found the same issue. Here's a couple options everyone can try to save some time (as I realize the question is kind of old and I'm assuming the OP found a solution already):

1) At a powershell command run: Install-Module -Name SqlServer (you might need -AllowClobber parameter)

The thing here is that module is installed to the following path on both 2012 and 2016 servers:

C:\Program Files\WindowsPowerShell\Modules\SqlServer (rather than Microsoft SQL Server\120\Tools\PowerShell\Modules)

2) Another option is to try the SQLPS module by changing in your script:

Import-Module -Name SqlServer

To:

Import-Module -Name SQLPS

You will find SQLPS in the Microsoft SQL Server directory structure here:

C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules

My script works with both SQL modules now, but the output actually looks better with SQLPS at this point...

You can find more on all of the above at this link