Invoke SQLCMD não funciona quando usado com xp_cmdshell

Há vários problemas aqui:

O principal problema é que o shell CMD executa cada linha à medida que ela chega por meio de um retorno; ele não espera por um indicador de "fim de comando", como ;, nem tenta descobrir por meio de análise (como o SQL faz quando um lote é enviado), pois não há como saber se umreturn termina um "comando" ou não. Normalmente você pode usar ^para continuação de linha, mas isso não parece funcionar dentro de um parâmetro de entrada (pelo menos não um com aspas não fechadas).

Então, para isso, você precisa reduzir tudo a uma única linha. Isso é bastante simples, pois você pode substituir Carriage Return (caractere 13) por uma string vazia e Line Feed/Newline (caractere 10) por um espaço. Mas isso por si só não funcionará, pois as instruções do PowerShell precisam ser separadas por uma nova linha ou um ponto e vírgula. Atualmente, as novas linhas estão sendo usadas, mas se as substituirmos por espaços, serão necessários pontos e vírgulas.

1. Portanto, a Etapa 1 é anexar um ponto e vírgula a cada comando real do PowerShell. Isso provavelmente é uma boa forma em geral, da mesma forma que com o T-SQL.

2. A etapa 2 seria fazer as duas REPLACEchamadas na @psvariável para transformá-la em um script de linha única.

3. Você inseriu aspas duplas, usadas para criar a INSERTinstrução. Esses precisam ser escapados com uma barra invertida ( \).

4. Get-WmiObjectnão parece funcionar como você está tentando usá-lo aqui. Eu tive que adicionar -ComputerNamepara passar no nome de um sistema. Se eu deixar em branco, ele retornou as informações do sistema local, mas se você quiser retornar informações para servidores remotos, provavelmente precisará usar o -ComputerNameswitch e passar um servidor por vez. Isso provavelmente exigirá um loop externo adicional para lidar com vários nomes de servidor (e seria lá que você passaria @ServerNamespara ser dividido para iterar na lista).

5. "capacity" e "freespace" são realmente armazenados como strings no banco de dados? Caso contrário, você pode querer remover as aspas simples em torno desses "valores" na INSERTinstrução.

O seguinte pelo menos é executado. Você pode depurar a partir daí. Eu estava recebendo erros por não encontrar o módulo "Invoke-SQLcmd" ou algo assim.

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;

O problema pode ser os feeds de linha no comando que você está tentando executar. Considere a diferença entre os seguintes casos.

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

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

powershellPara executar scripts de várias linhas de xp_cmdshell, tente salvar o comando inteiro em um arquivo e, em seguida, fazer referência ao arquivo em uma chamada de linha única. Por exemplo, dado este texto...

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

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

...salvo como arquivoC:\temp\foo.ps1 , o seguinte deve ser bem-sucedido...

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

Existem várias maneiras de escrever texto arbitrário no sistema de arquivos usando T-SQL, mas a jogada mais inteligente provavelmente terá um arquivo estático e obterá o@servernames de outra chamada para o banco de dados - por exemplo, alterando isso ...

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

...em um script sql para isso...

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

... em um script powershell.

Altamente recomendado para usar dbatools incríveis Powershell.

Basta instalar este módulo:

Install-Module dbatools

Em seguida, use a função Get-DbaDatabaseSpace :

# 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

Ou para instâncias de multiplicação:

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

Além disso, você pode usar o modelo PowerBI muito útil para esta tarefa: https://sqljana.wordpress.com/2018/04/30/sql-server-quick-space-file-layout-analysis-with-powershell-and-powerbi/