February 18, 2015 at 1:18 pm
I have a powershell script that loops through a list of instances and using the SQLSERVER:\ drive retrieves a number of instance properties.
when running it on my workstation I receive the following warning and error when trying to connect to any server that has only had SQL Server 2012 installed. If it was upgraded to 2012 or if it is 2008/2005, then the connection works fine.
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'servername' failed with the following error: SQL Server WMI provider is not available on servername. --> Invalid namespace
cd : Cannot find path 'SQLSERVER:\sql\servername' because it does not exist.
At line:1 char:1
+ cd servername
+ ~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (SQLSERVER:\sql\servername:String) [Set-Location], ItemNotFoundExcep
tion
+ FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SetLocationCommand
RPC communication is open between my workstation and the servers and I can see that the connection is being established. The WMI namespace for sql server is installed and functioning as I can use it with the get-wmiobject cmdlet. The sql server wmi namespace is "root\microsoft\sqlserver" with subnamespaces of "root\microsoft\sqlserver\ServerEvents" and "root\microsoft\sqlserver\ComputerManagementXX". the XX appears to be the SQL version that is installed. (ie root\microsoft\sqlserver\ComputerManagement11" for sql server 2012). for servers that have been upgraded from 2008 to 2012 there is a computermanagement namespace for both 10 and 11.
The servers that do not work only have a namespace of root\microsoft\sqlserver\ComputerManagement11. While executing "cd SQLSERVER:\sql\servername" I ran a packet capture and I can see that the PoSH session is checking for the Computermanagement namespaces but is doesnt check for 11. It checks for ComputerManagement12, ComputerManagement10, and ComputerManagement. So this is where my problem lies.
My workstation only has SQL Server 2014 tools and have never had any previous version installed. If I connect from a server that has 2012 installed, things work fine.
So now to the questions. First, is this a bug in 2014 that the SQLPS module does not check for the computermanagement11 namespace? Second, does anybody know how to make it check for all namespace versions? (ie possibly install 2012 tools, or just the 2012 powershell tools/smo objects .
Thanks in advanced.
March 16, 2015 at 9:14 am
Did anything come of this? Weirdly I just hit it on my test lab today, even though at work in production I have a raw 2014 server that connects fine to a raw 2012 server.
March 16, 2015 at 10:07 am
Unfortunately I have not yet found a solution or tried to install 2012 tools to see if that resolves the issue. For your servers at work, check the available namespaces on both the 2014 and 2012 servers with the following Posh code.
Get-WmiObject -namespace "root\Microsoft\SQLServer" -class __NAMESPACE| where {$_.name -match "computermanagement"}|select name
I suspect that the 2012 box will have a computer management namespace other than computermanagement11.
March 17, 2015 at 6:25 am
Thanks for the update, I haven't checked the WMI query yet but yeah it turns out I was wrong. The machines at work have the same problem and I just never noticed before. Funny 🙂 Also funny that the SQL 2012 provider throws WMI errors when connecting to a 2014 server - likely the same kind of issue.
I can't believe Microsoft hasn't fixed this even though I found a few Connect items all pointing to the same thing. Extremely disappointing.
March 17, 2015 at 8:00 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply