How to Use xp_dirtree to List All Files in a Folder

Question

Post reply

How to Use xp_dirtree to List All Files in a Folder

george.sofroniadis

Old Hand

Points: 351
More actions
July 6, 2018 at 3:18 am

#372033

hello to everyone,
i use the following code to list a filenames in a temporary table.the code runs well when i use the master.sys.xp_dirtree '\\MNG01\Scans\',1,1;
but doesnt return result when i add in the path field  \05083-ΛΥΚΟΒΡΥΣΗ\ALL\ so the xp_dir_tree command is
master.sys.xp_dirtree '\\MNG01\Scans\05083-ΛΥΚΟΒΡΥΣΗ\ALL\',1,1; i dont know why this happened,this happen because the greek characters in the path?
any suggestions?
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
  DROP TABLE #DirectoryTree;
CREATE TABLE #DirectoryTree (
   id int IDENTITY(1,1)
  ,subdirectory nvarchar(512)
  ,depth int
  ,isfile bit);
INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree '\\MNG01\Scans\05083-ΛΥΚΟΒΡΥΣΗ\ALL\',1,1;

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

It could be the Greek character thing. Try this:

EXEC master.sys.xp_dirtree N'\\MNG01\Scans\05083-???????S?\ALL\',1,1;

John

george.sofroniadis Old Hand Points: 351 More actions · Answer 2

John Mitchell-245523 - Friday, July 6, 2018 3:40 AM
It could be the Greek character thing. Try this:
EXEC master.sys.xp_dirtree N'\\MNG01\Scans\05083-Î›Î¥ÎšÎŸÎ’Î¡Î¥Î£Î—\ALL\',1,1;
John

oh Unicode data!!!

thanks Doc !!have a nice weekend!!

george.sofroniadis Old Hand Points: 351 More actions · Answer 3

USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[scans_eiserxomena] Script Date: 07/09/2018 12:45:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[scans_eiserxomena] @path nvarchar(4000)

as
--set @path= N'\\MNG01\Scans\05105-F??????F???\ALL\'

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit);

INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @path, 1,1

select COUNT(subdirectory) from #DirectoryTree where isfile=1 and subdirectory like 'D%'

i wrote this stored procedure when i exute from sql management studio it runs perfectly!BUT MY purpose is to run from excel with a vba code and returns the result in a specific cell and when i call i t from excel there is no data to return !!any suggestions...??

sgmunson SSC Guru Points: 110639 More actions · Answer 4

george.sofroniadis - Monday, July 9, 2018 3:50 AM
USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[scans_eiserxomena] Script Date: 07/09/2018 12:45:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[scans_eiserxomena] @path nvarchar(4000)
as
--set @path= N'\\MNG01\Scans\05105-Î¦Î™Î›Î‘Î”Î•Î›Î¦Î•Î™Î‘\ALL\'
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;
CREATE TABLE #DirectoryTree (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit);
INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @path, 1,1
select COUNT(subdirectory) from #DirectoryTree where isfile=1 and subdirectory like 'D%'
i wrote this stored procedure when i exute from sql management studio it runs perfectly!BUT MY purpose is to run from excel with a vba code and returns the result in a specific cell and when i call i t from excel there is no data to return !!any suggestions...??

Could be execution context. Do you provide the same user id value to connect to SQL Server via VBA that you do via SSMS ?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)