从XP_CMDSHELL获取结果 [英] Get Results from XP_CMDSHELL

问题描述

我一直在网上搜索一些信息，看来从XP_CMDSHELL获取结果的唯一方法是将它们存储到临时表中.真的没有更简单的方法吗?

I have been searching the web some and it seems like the only way to get the results from XP_CMDSHELL is to store them into a temp table. Is there really no easier way?

从Experts交流中心:

From Experts Exchange:

否，xp_cmdshell不会从exe返回任何信息.并且您必须使用以下内容 语法，如果您不在master数据库中运行它.主..xp_cmdshell.你不得不 向您的用户授予在master数据库中执行此过程的权限.你将不得不 您的exe会自行插入信息，因为它无法将信息返回给 叫它.

No, xp_cmdshell will not return any information from the exe. and you have to use the following syntax if you are not in the master database to run it. master..xp_cmdshell. You will have to give your user permission to execute this procedure in the master database. You will have to have your exe insert the information its self because it can not return information to the process that called it.

然后...

虽然@result仅从xp_cmdshell中获取返回值，但您可以捕获结果 通过直接插入到表中来执行命令...像这样:

While @result only gets the return value from xp_cmdshell, you may be able to capture the results of the command by inserting directly into a table... something like this:

ymmv ...

set nocount on
declare  @filepath   varchar(255),
         @cmd        varchar(255),
         @rc         int

select   @filepath = 'c:\temp\'         
select   @cmd      = 'dir ' + @filepath + '~*.tmp'

create table #output (output varchar(255) null)
insert #output exec @rc = master..xp_cmdshell @cmd
select * from #output where output is not null
drop table #output

推荐答案

没有简单的方法可以从xp_cmdshell捕获STDOUT/STDERR反馈.至少有一种选择，但不能归类为更简单的方法:
作为命令的一部分，可以将命令的输出重定向到文本文件，然后使用OPENROWSET读取文本文件.

There is no easier way to capture STDOUT/STDERR feedback from xp_cmdshell; there is at least one alternative but it couldn't be classed as easier:
It would be possible to redirect the output of the command to a text file as part of the command, then read the text file using OPENROWSET.

顺便说一句，上面发布的脚本中至少有一个错误. xp_cmdshell 的文档指出，它以nvarchar()形式返回命令输出( 255).
此外，临时表应具有一个标识列，否则结果可能无法以正确的顺序显示:

BTW there is at least one error in the script posted above. The docs for xp_cmdshell state that it returns command output as nvarchar(255).
Also, the temp table ought to have an identity column, otherwise the results may not be displayed in the correct order:

...
create table #output (id int identity(1,1), output nvarchar(255) null)
insert #output (output) exec @rc = master..xp_cmdshell @cmd
select * from #output where output is not null order by id
drop table #output

这篇关于从XP_CMDSHELL获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！