CODEWITHSUNDEEP
sqlsql-serverpowershell
deepti
deepti

Reputation: 729

Getting error in SQL Server executing get-wmiobject

I am trying to execute following PowerShell query in SQL Server 

xp_cmdshell "get-WmiObject Win32_LogicalDisk -AV-RISCVMSQL114 | Format-Table -Property DeviceID,FreeSpace,Size"

After executing this, I am getting error:

'get-WmiObject' is not recognized as an internal or external command, operable program or batch file.

I want to execute this command in all SQL Servers. It's getting executed on SQL Server 2008, but on SQL Server 2012, it's throwing that error due to version or some PowerShell component is not installed.

Can you to please look at the code and let me know

Upvotes: 2

Views: 1171

Answers (1)

Richard
Richard

Reputation: 7000

Try: 

xp_cmdshell 'PowerShell.exe -noprofile -command "get-WmiObject Win32_LogicalDisk -AV-RISCVMSQL114 | Format-Table -Property DeviceID,FreeSpace,Size"'

If this doesn't work logon to the server open PowerShell with administrator rights. Type Get-ExecutionPolicy and check that Execution Policy is set to Unrestricted. If it isn't then enter the following, then type 'A' (Yes to All): 

Set-ExecutionPolicy Unrestricted

If this still doesn't work try upgrading to at least PowerShell V3. But if this is not possible you can try repair your corrupt PowerShell V2 WMI repository with: 

winmgmt /verifyrepository

If repository is found to be inconsistent run Winmgmt /salvagerepository and Winmgmt /resetrepository and verify after each. If it is still inconsistent try the following steps:

  • Go to Task Manager>> Services >> Open Service >> Windows Management Instrumentation service, and change the start up type to Manual.

  • Stop the WMI service:

    net stop winmgmt

  • Rename the repository folder (C:\Windows\System32\wbem\Repository) to some else 'Repository_OLD'.

  • Start the WMI service:

    net start winmgmt

  • Set the service back to auto start.

Failing Re-register all of the dlls and recompile the .mofs in the Wbem directory with the following .bat script and then reboot. 

@echo off
sc config winmgmt start= disabled
net stop winmgmt /y
%systemdrive%
cd %windir%\system32\wbem
for /f %%s in (‘dir /b *.dll’) do regsvr32 /s %%s
wmiprvse /regserver 
winmgmt /regserver 
sc config winmgmt start= auto
net start winmgmt
for /f %%s in (‘dir /s /b *.mof *.mfl’) do mofcomp %%s

Upvotes: 1

Related Questions