Invoke SQLCMD doesn't work when used with xp_cmdshell

by TheGameiswar   Last Updated May 16, 2018 14:06 PM

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 '[email protected]+'
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



Answers 1


Highly recommended to use awesome dbatools Powershell module.

Just install this module:

Install-Module dbatools

Then use Get-DbaDatabaseSpace function:

# Get Db Free Space AND write it to table
Get-DbaDatabaseSpace -SqlInstance $instance | Out-GridView
Get-DbaDatabaseSpace -SqlInstance $instance -IncludeSystemDB | Out-DbaDataTable | Write-DbaDataTable -SqlInstance $instance -Database tempdb -Table DiskSpaceExample -AutoCreateTable
Invoke-Sqlcmd2 -ServerInstance $instance -Database tempdb -Query 'SELECT * FROM dbo.DiskSpaceExample' | Out-GridView

Or for multiply instances:

$allservers = "localhost\sql2016", "localhost\sql2017"
$allservers | Get-DbaDatabaseSpace -IncludeSystemDB | Out-DbaDataTable | Write-DbaDataTable -SqlInstance $instance -Database tempdb -Table DiskSpaceExample -AutoCreateTable

Also you can use very useful PowerBI template for this task: https://sqljana.wordpress.com/2018/04/30/sql-server-quick-space-file-layout-analysis-with-powershell-and-powerbi/

Konstantin Taranov
Konstantin Taranov
May 14, 2018 14:54 PM

Related Questions