Invoke SQLCMD doesn't work when used with xp_cmdshell

Question

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,Server names will be populated from a table and passed as a comma seperated list.Even passing one server also has the same result

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.

I am modifying the code and was told not to rewrite when asked

Please let me know if you need any further info

Repro:
Below is entire command from print,if you remove powershell.exe and -c ,below will run in powershell..

powershell.exe  -c "

$computers=Get-WmiObject -Class Win32_Volume 'servername'
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'
           ) 

" 

Invoke-SQLcmd  -query $insertquery -ServerInstance 'servername' -Database dbname 

}

"

Answer 1

There are several problems here:

The main issue is that the CMD shell executes each line as it comes in via a return; it does not wait for an "end of command" indicator such as ;, nor does it attempt to figure it out via parsing (like SQL does upon a batch being submitted) since there is no way to know if a return ends a "command" or not. Typically you can use ^ for line continuation, but that does not seem to work within an input paramter (at least not one with an unclosed quote).

So, for this you need to reduce everything to a single line. That is simple enough as you can just replace Carriage Return (character 13) with an empty string and Line Feed/Newline (character 10) with a space. But, that alone will not work since PowerShell statements need to be separated by either a newline or a semicolon. Currently newlines are being used, but if we replace those with spaces, then semicolons will be required.

1. Hence, Step 1 is to append a semicolon to each actual PowerShell command. This is probably good-form in general, same as with T-SQL.

2. Step 2 would be doing the two REPLACE calls against the @ps variable to transform this into a single-line script.

3. You have embedded double-quotes, used for creating the INSERT statement. Those need to be escaped with a backslash ( \ ).

4. Get-WmiObject does not seem to work as you are attempting to use it here. I had to add -ComputerName to pass in the name of a system. If I left it blank then it returned the local system info, but if you want to return info for remote servers then you will probably need to use the -ComputerName switch and pass in one server at a time. That will most likely require an additional outer loop to handle multiple server names (and it would be there that you pass in @ServerNames to get split for iterating through the list).

5. Are "capacity" and "freespace" really stored as strings in the DB? If not, you might want to remove the single-quotes around those "values" in the INSERT statement.

The following at least executes. You can debug from there. I was getting errors on it not finding the "Invoke-SQLcmd" module or something.

DECLARE @ServerNames NVARCHAR(4000);
SET @ServerNames = N'ALBRIGHT';

DECLARE @ps NVARCHAR(4000);
SET @ps = N'powershell.exe -noexit -c "

$computers=Get-WmiObject -Class Win32_Volume -ComputerName ' + @ServerNames + N';
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;

}

"';

SET @ps = REPLACE(REPLACE(@ps, NCHAR(13), N''), NCHAR(10), N' ')
PRINT @ps;


EXEC xp_cmdshell @ps;

Answer 2

The issue may be the line feeds in the command you're trying to execute. Consider the difference between the following cases.

-- case 1
execute xp_cmdshell N'powershell.exe -noexit -c "echo hello"';

-- case 2
execute xp_cmdshell N'powershell.exe -noexit -c "
echo hello
"';

To execute multi-line powershell scripts from xp_cmdshell, try saving the entire command to a file and then referencing the file in a single-line call. For example, given this text...

$srvs =@(
    'localhost',
    '127.0.0.1',
    '(local)'
)

$srvs | % {
   (Invoke-SqlCmd -ServerInstance "$_" -Query 'select @@servername srv').srv
}

...saved as file C:\temp\foo.ps1, the following should succeed...

execute xp_cmdshell N'powershell.exe c:\temp\foo.ps1';

There's a number of ways to write arbitrary text to the filesystem using T-SQL but the smarter play is likely to have a static file and to get the @servernames from another call to the DB - for example changing this...

$computers = Get-WmiObject -Class Win32_Volume '+@servernames+'

...in a sql script to this...

$conn = @{
    ServerInstance = $server
    Database       = $database
}
$servernames = (Invoke-SqlCmd @conn -query "select Name from server_list").Name -join ','
$computers = Get-WmiObject -Class Win32_Volume '$servernames' 

... in a powershell script.

Answer 3

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/