如何在"xp_cmdshell"DIR 命令中使用超过 128 个字符的路径

Question

我正在尝试使用具有超过128个字符的SQL Server路径在目录中获取文件列表。它不起作用。

DECLARE 
@FilePath varchar(256)='D:...',--Path with a length more than 128 char
@SourceFiles varchar(100)='Test123456789*.txt',
@Query varchar(1000)
If Object_Id('tempdb.dbo.#FirstTable') Is NULL
    CREATE TABLE #FirstTable (Name varchar(256))
SET QUOTED_IDENTIFIER ON
SET @Query ='master.dbo.xp_cmdshell "dir '+ @FilePath + '' + @SourceFiles +' /b"'
INSERT #FirstTable exec (@Query)
select * from #FirstTable
truncate table #FirstTable

这给出以下错误：

以'dir d：....'开头的标识符太长。最大长度为128。

Answer 1

此问题的关键是错误消息的措辞（添加了强调）：

以

开始

AN 标识符是SQL Server中的对象或项目的名称，不是字面字符串。例如，master，dbo和xp_cmdshell都是标识符。

所以，您有两个选择：

简单修复（但不是最佳实践）

标识符的问题是指向SET QUOTED_IDENTIFIER ON行的线索。只需将ON更改为OFF即可允许它起作用。但是，如果路径和/或文件名模式中有一个空间（例如 c： program Files（x86） ）。

最佳练习修复（这仍然很容易）

使用单样而不是围绕shell命令的双报价。而且，由于您在动态SQL中创建命令，因此在这两种情况下都必须是两个单击。因此，...xp_cmdshell ''dir ... /b''...

如果路径本身中有任何空格，则需要围绕路径的双引号： ...xp_cmdshell ''dir "..." /b''...

因此，完整的语法将是：

SET @Query ='master.dbo.xp_cmdshell ''dir "'+ @FilePath + '' + @SourceFiles +'" /b''';

将其放入完整的原始代码中，以及一个长路径名和额外的SELECT和PRINT，以查看正在发生的事情，您得到：

DECLARE @FilePath varchar(256)='C:UsersSolomonAppDataLocalMicrosoftHelpViewer2.0TableOfContentsFilterCacheVisualStudio11en-US',--Path with a length more than 128 char
@SourceFiles varchar(100)='this_is_a_long_file_name.*',
@Query varchar(1000);
IF (OBJECT_ID(N'tempdb.dbo.#FirstTable') IS NULL)
BEGIN
    CREATE TABLE #FirstTable (Name VARCHAR(256));
END;
SET QUOTED_IDENTIFIER ON;
SET @Query ='master.dbo.xp_cmdshell ''dir "'+ @FilePath + '' + @SourceFiles +'" /b''';
SELECT LEN(@FilePath + '' + @SourceFiles);
PRINT @Query;
INSERT #FirstTable EXEC(@Query);
  SELECT * FROM #FirstTable;

运行没有错误。@Query的值，在"消息"选项卡中显示，呈现为：

master.dbo.xp_cmdshell 'dir "C:UsersSolomonAppDataLocalMicrosoftHelpViewer2.0TableOfContentsFilterCacheVisualStudio11en-USthis_is_a_long_file_name.*" /b'

现在，在我建议的更改之前，有一个错误。原始代码的输出（具有相同的测试值）为：

"结果"选项卡：

129

"消息"选项卡：

master.dbo.xp_cmdshell" dir c： users solomon appdata local local microsoft microsoft helpviewer2.0 tableofcontentsfiltercache visualstudio11 en-en-us en-en-us

msg 103，15级，状态4，第1行
以'dir c： users solomon appdata local local microsoft helpviewer2.0 tableofcontentsfiltercace visualstudio11 en-us this_is_is_i_a_a_long_file_n''开头的标识符。最大长度为128。

Answer 2

只是为了乐趣，我创建了一个目录C:TempTemporary folder with an unecessarily long name just to be an example etc etcTemporary folder with an unecessarily long name just to be an example etc etc（163个字符），并放置了一堆test*.txt文件。

以下脚本没有您描述的问题。

DECLARE @file_path NVARCHAR(256)='C:TempTemporary folder with an unecessarily long name just to be an example etc etcTemporary folder with an unecessarily long name just to be an example etc etc';
PRINT LEN(@file_path); -- prints 163
DECLARE @src_files NVARCHAR(100)='test*.txt';
DECLARE @dir_cmd NVARCHAR(4000)='DIR "'+@file_path+''+@src_files+'" /b';
CREATE TABLE #dir_table(name NVARCHAR(256));
INSERT INTO #dir_table(name) EXEC xp_cmdshell @dir_cmd;
SELECT*FROM #dir_table; -- result is a bunch of test*.txt files
DROP TABLE #dir_table;