主页 / dba / 问题 / 206678
Accepted
TheGameiswar
Asked: 2018-05-15 07:07:24 +0800 CST

与 xp_cmdshell 一起使用时，调用 SQLCMD 不起作用

  • 772

我正在尝试从一堆服务器获取磁盘空间报告并将它们插入到 sql 表中。

下面是我正在尝试做的示例，服务器名称将从表中填充并作为逗号分隔列表传递。即使传递一个服务器也有相同的结果

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

}

"';

现在我尝试将变量传递给 xp_cmdshell，如下所示

execute xp_cmdshell @ps;

在 SSMS 中调用时，上面返回 null，但在 powershell 中有效。

任何想法为什么？

以下是我尝试过的一些事情

1.Shell 和 ssms 都使用了相同的帐户（管理员） 2.
尝试了多种方法，例如导入模块
3.XP_CMDshell 有效，但这仅针对此查询返回 null
4.我尝试添加 -nowait，但事实并非如此也有帮助

我已经尝试从一天多的时间完成这项工作，但这不起作用..我必须使用 xp_cmdshell 因为不允许编写 ac# 应用程序。Bat 文件无济于事，因为服务器名称作为逗号传递单独的列表。

我正在修改代码，并在被问到时被告知不要重写

如果您需要任何进一步的信息，请告诉我

Repro：
下面是 print 的整个命令，如果你删除 powershell.exe 和 -c ，下面将在 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 

}

"
sql-server sql-server-2012

3 个回答

  1. Best Answer

    这里有几个问题：

    主要问题是 CMD shell 通过返回执行每一行。它不会等待“命令结束”指示符，;例如return不是。通常，您可以使用^续行，但这似乎不适用于输入参数（至少不是带有未闭合引号的参数）。

    因此，为此，您需要将所有内容减少到一行。这很简单，因为您可以将回车（字符 13）替换为空字符串，将换行符/换行符（字符 10）替换为空格。但是，仅此一项是行不通的，因为 PowerShell 语句需要用换行符或分号分隔。目前正在使用换行符，但如果我们用空格替换它们，则需要分号。

    1. 因此，第 1 步是将分号附加到每个实际的 PowerShell 命令。这可能是一般的好格式，与 T-SQL 相同。

    2. 第 2 步将对变量进行两次REPLACE调用，@ps以将其转换为单行脚本。

    3. 您嵌入了双引号，用于创建INSERT语句。这些需要用反斜杠 ( \) 转义。

    4. Get-WmiObject当您尝试在此处使用它时，它似乎不起作用。我必须添加-ComputerName以传递系统的名称。如果我将其留空，那么它会返回本地系统信息，但如果您想返回远程服务器的信息，那么您可能需要使用-ComputerName开关并一次传入一台服务器。这很可能需要一个额外的外部循环来处理多个服务器名称（并且您将在那里传递@ServerNames以获取拆分以遍历列表）。

    5. “容量”和“可用空间”真的作为字符串存储在数据库中吗？如果不是，您可能希望删除语句中这些“值”周围的单引号INSERT。

    以下至少执行。您可以从那里进行调试。我在找不到“Invoke-SQLcmd”模块或其他东西时遇到错误。

    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;
    • 5

  2. 问题可能是您尝试执行的命令中的换行符。考虑以下情况之间的区别。

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

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

    要从 执行多行powershell脚本xp_cmdshell，请尝试将整个命令保存到文件中，然后在单行调用中引用该文件。例如，鉴于此文本...

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

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

    ...保存为文件C:\temp\foo.ps1，以下应该成功...

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

    有很多方法可以使用 T-SQL 将任意文本写入文件系统，但更聪明的做法可能是拥有一个静态文件并@servernames从另一个调用 DB 中获取 - 例如改变这个......

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

    ...在一个 sql 脚本中...

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

    ...在powershell脚本中。

    • 4

  3. 强烈推荐使用很棒的 dbatools Powershell 模块。

    只需安装此模块：

    Install-Module dbatools

    然后使用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

    或者对于多个实例：

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

    您也可以为此任务使用非常有用的 PowerBI 模板：https ://sqljana.wordpress.com/2018/04/30/sql-server-quick-space-file-layout-analysis-with-powershell-and-powerbi/

    • 3

相关问题