Sql-server – Invoke SQLCMD doesn’t work when used with xp_cmdshell

powershellsql serversql-server-2012xp-cmdshell

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 

}

"

Best Answer

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;
Related Question