Read file system directory & files with T-SQL

Question

Read file system directory & files with T-SQL

July-580968

SSC Veteran

Points: 243
More actions
January 8, 2008 at 5:00 am

#154046

I need to read a file system to extract and copy some file and directory. To do so I use some Extended Stored Procedures.
I use xp_dirtree to do this but I can't reach the server where I have files.
I use "\\MACHINE_NAME\SHARENAME" but the result set is empty (0 row(s) affected).
I try with xp_cmdshell but I have the same result.
Can someone help me?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Execute the following command which will give you idea about your database files. Execute the query with the respective database to find the related files.

select name, filename from sysfiles

Hope this is what are you looking for?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

July-580968 SSC Veteran Points: 243 More actions · Answer 2

I have to reach another server to copy a lot of file (100 files for 1 GB). I try with

EXEC xp_dirtree "\\machine\shrarename" but I obtain "system cannot find path specified". Is the code correct? Have I forget some configuration?

Thanks.

ziminski SSC Veteran Points: 221 More actions · Answer 3

First thing that comes to my mind is permissions. Where are you executing it from and do you have access as that user? I run from my workstation via query analyzer to server1 with the cmd EXEC xp_dirtree '\\server2\share' and it returns as expected. From my workstation I can access server2 as well. both servers use windows auth, which is how I am logged in on my wrkst.

Hope that helps.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 4

Sounds like your Service Account does not have permissions to the share. Do you have the service account running under a local account or domain account? Go to SQL Server Configuration Manager and open up the properties of your SQL Instance to check. You have to give the current account permissions or use an account with the needed permissions.

My blog: http://jahaines.blogspot.com

Nebojsa Ilic SSCommitted Points: 1735 More actions · Answer 5

One thing you can do (even if it's not a secure way) is to map this share using particular credential:

exec @Return = master..xp_cmdshell 'net use mapletter: \\server2\sharename pwd /user:domainname\username'

... and after work to delete map ...

exec @Return = master..xp_cmdshell 'net use mapletter: /delete'

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 6

Not sure if this will help but try to check if the file exists then if

it exists do the copy

declare @Path varchar(128) ,

@FileName varchar(128)

select @Path = 'C:\' ,

@FileName = 'myfile.txt'

declare @i int

declare @File varchar(1000)

select @File = @Path + @FileName

exec master..xp_fileexist @File, @i out

if @i = 1

declare @cmd varchar (100)

select @cmd = 'copy C:\myfile.txt C:\myfile1.txt'

exec master..xp_cmdshell @cmd

else

print 'not exists'

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

July (1/8/2008)
I have to reach another server to copy a lot of file (100 files for 1 GB). I try with
EXEC xp_dirtree "\\machine\shrarename" but I obtain "system cannot find path specified". Is the code correct? Have I forget some configuration?
Thanks.

SQL Server must be logged in as a user that can "see" the directory.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)