How to get FileName and TimeStamp of the file from a directory without using xp_cmdShell

Question

How to get FileName and TimeStamp of the file from a directory without using xp_cmdShell

SQLCurious

SSCrazy

Points: 2339
More actions
March 18, 2015 at 3:31 pm

#296169

I want to write a stored procedure or a simple T-SQL snippet that takes a parameter "Directory Path" and gives me the name and datetimestamp associated with each of the files within that directory. I can not use xp_cmdshell since it is not allowed. CLR integration is not enabled on the server either.
Is there any other way at all to achieve this directly from SQL Server using T-SQL?

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

SQLCurious (3/18/2015)
I want to write a stored procedure or a simple T-SQL snippet that takes a parameter "Directory Path" and gives me the name and datetimestamp associated with each of the files within that directory. I can not use xp_cmdshell since it is not allowed. CLR integration is not enabled on the server either.
Is there any other way at all to achieve this directly from SQL Server using T-SQL?

Microsoft has killed just about every useful thing to do what you want from T-SQL. You CAN get the file names using code similar to the following...

EXEC xp_dirtree 'C:\Temp',1,1;

... where "C:\Temp" is either a drive\path to the files ON THE SERVER or is a UNC to a share on some other box. The first "1" says to "list only the current level" and the second "1" says to "list files as well as directories".

There used to be an xp called xp_GetFileDetails that would return the kind of file info you're looking for but that's one of the useful tools that they killed because they didn't want to support it and had no use for it themselves.

Can you use "OLE Automation" or is that locked down, as well? I've got a dandy proc for that even if the SP_OA* stuff makes it a bit slow on really large directories.

As a bit of a sidebar, it's a real shame that MS doesn't make file interrogation a lot easier to do from T-SQL. In that light, it's also a real shame that many companies have so much FUD about high-value tools like xp_CmdShell and CLR. Disabling xp_CmdShell provides virtually no extra security and it cuts out some mighty tools. I have to tell you that it's really handy to be able to have procs run PowerShell directly.

--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)

SQLCurious SSCrazy Points: 2339 More actions · Answer 2

Thanks, Jeff. I tried using xp_DirTree but it only gets me the file names. I need to sift through the files based on the file types and their create/modified times to make some decisions. That is what is really killing me 🙁

I have failed hard to convince that xp_CmdShell is not as bad a security loophole as it is thought to be.

Thanks, anyway, for your quick response.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

SQLCurious (3/18/2015)
Thanks, Jeff. I tried using xp_DirTree but it only gets me the file names. I need to sift through the files based on the file types and their create/modified times to make some decisions. That is what is really killing me 🙁
I have failed hard to convince that xp_CmdShell is not as bad a security loophole as it is thought to be.
Thanks, anyway, for your quick response.

A work around is to write a job to use a "CmdTask" and have the T-SQL call the job. The job would do the directory work for you and dump it to a table for you to work from after a delay to make sure the job completed or checking to make sure the job completed.

Again, though... will they allow the use of OLE Automation?

--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)