2

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.

TT.
  • 15,774
  • 6
  • 47
  • 88
Husen
  • 1,541
  • 10
  • 14

2 Answers2

2

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.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • If we use single-quotes than it will not considere as one command in xp_cmdshell so, it will show error as "The system cannot find the path specified." – Husen Nov 23 '16 at 10:44
  • @Husen The xp_cmdshell stored procedure expects a VARCHAR or NVARCHAR parameter. That is why you need to put single quotes around the complete `DIR` command. Since you are formatting this command within a VARCHAR itself, you need to double those, i.e. put two single quotes to get one single quote in the final string. – TT. Nov 23 '16 at 11:23
  • I have tried all the permutations and combinations in this. Please check in a local environment and then suggest syntax. – Husen Nov 23 '16 at 12:08
  • @Husen I had provided that syntax, but in case it was difficult to put it back into the code, I have updated my answer with the full script. I also added a full explanation of the error and a second (though not as good) option. – Solomon Rutzky Nov 23 '16 at 13:37
  • @srutzky Read it... I wonder why the OP thought he needed an explicit `QUOTED_IDENTIFIER` set to `ON`. I don't think it's necessary for what he wants to do. – TT. Nov 23 '16 at 13:41
  • @TT. My guess is that it was an attempt to get the double-quotes to work. – Solomon Rutzky Nov 23 '16 at 13:47
1

Just for the fun of it I created a directory C:\Temp\Temporary folder with an unecessarily long name just to be an example etc etc\Temporary folder with an unecessarily long name just to be an example etc etc (163 characters) and placed a bunch of test*.txt files in it.

The following script does not have the problems you are describing.

DECLARE @file_path NVARCHAR(256)='C:\Temp\Temporary folder with an unecessarily long name just to be an example etc etc\Temporary 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;
TT.
  • 15,774
  • 6
  • 47
  • 88