How to use path having more than 128 character in 'xp_cmdshell' DIR command

Question

I am trying to get list of files in a directory using SQL Server path having more than 128 character. It isn't working.

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

This gives the following error:

The identifier that starts with 'dir D:....' is too long. Maximum length is 128.

Answer 1

The key to this issue is the wording of the error message (emphasis added):

The identifier that starts with

An identifier is a name of an object or item within SQL Server, not a literal string. For example, master, dbo, and xp_cmdshell are all identifiers.

So, you have two options:

Simple fix (but not the best practice)

The issue being with identifiers is a clue that points to the SET QUOTED_IDENTIFIER ON line. Just changing the ON to be OFF will allow this to work. However, you will get an error if there is a space in your path and/or filename pattern (e.g. C:\Program Files (x86)\ ).

Best practice fix (that is still pretty easy)

Use single-quotes instead of double-quotes around the shell command. And since you are creating the command in Dynamic SQL, it needs to be two single-quotes in both instances. So, ...xp_cmdshell ''dir ... /b''...

If there are any spaces in the path itself, then you need double-quotes around the path: ...xp_cmdshell ''dir "..." /b''...

Hence, the full syntax would be:

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

Putting it into the the full original code, along with a long path name and an extra SELECT and PRINT to see what is going on, you get:

DECLARE @FilePath varchar(256)='C:\Users\Solomon\AppData\Local\Microsoft\HelpViewer2.0\TableOfContentsFilterCache\VisualStudio11\en-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;

Running that gets no errors. The value of @Query, shown in the "Messages" tab, renders as:

master.dbo.xp_cmdshell 'dir "C:\Users\Solomon\AppData\Local\Microsoft\HelpViewer2.0\TableOfContentsFilterCache\VisualStudio11\en-US\this_is_a_long_file_name.*" /b'

Now, prior to the changes that I suggested, there was an error. The output from the original code (with the same test values) is:

"Results" tab:

129

"Messages" tab:

master.dbo.xp_cmdshell "dir C:\Users\Solomon\AppData\Local\Microsoft\HelpViewer2.0\TableOfContentsFilterCache\VisualStudio11\en-US\this_is_a_long_file_name.* /b"

Msg 103, Level 15, State 4, Line 1
The identifier that starts with 'dir C:\Users\Solomon\AppData\Local\Microsoft\HelpViewer2.0\TableOfContentsFilterCache\VisualStudio11\en-US\this_is_a_long_file_n' is too long. Maximum length is 128.