I am trying to get disk space report from bunch of servers and insert them into sql table..
below is sample of what i am trying to do
set @ps = 'powershell.exe -noexit -c "
$computers=Get-WmiObject -Class Win32_Volume '+@servernames+'
foreach($computer in $computers)
{
$pscomputername=$computer.pscomputername
$name=$computer.name
$capacity=$computer.capacity
$freespace=$computer.freespace
$Label=$computer.Label
$insertquery="
INSERT INTO [dbo].[temp_disksdata]
(
[servername] ,
[DiskName] ,
[Capacity(GB)] ,
[FreeSpace(GB)],
[label]
)
VALUES
(''$pscomputername'' ,
''$name'' ,
''$capacity'',
''$freespace'',
''$Label''
)
GO
"
Invoke-SQLcmd -query $insertquery -ServerInstance ''someserver'' -Database dbname
}
"';
Now i try to pass the variable to xp_cmdshell like below
execute xp_cmdshell @ps;
When invoked in SSMS,above returns null,but works in powershell..
Any ideas why ?
Below are a few things i tried
1.Same account(admin) is used in both shell and ssms
2.tried multiple things like import modules
3.XP_CMDshell works,but this returns null only for this query
4.I have tried to add -nowait,but that doesn't help as well
I have been trying to get this done from more than a day,but this doesn't work.. I have to use xp_cmdshell because writing a c# app is not allowed.Bat file doesn't help because server names are passed as a comma seperated list.
Please let me know if you need any further info