TSQL-Get Windows Folders Files in table with file parameters

Question

Post reply

TSQL-Get Windows Folders Files in table with file parameters

khurramsaifi

SSC Enthusiast

Points: 183
More actions
July 29, 2010 at 9:05 am

#223848

Comments posted to this topic are about the item TSQL-Get Windows Folders Files in table with file parameters

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

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

JON RICE SSC Enthusiast Points: 187 More actions · Answer 1

Getting error: Msg 197, Level 15, State 1, Line 19

EXECUTE cannot be used as a source when inserting into a table variable.

Msg 170, Level 15, State 1, Line 27

Line 27: Incorrect syntax near 'try'.

Msg 170, Level 15, State 1, Line 36

Line 36: Incorrect syntax near 'try'.

Msg 156, Level 15, State 1, Line 40

Incorrect syntax near the keyword 'END'.

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 2

On the first attempt I received the message:

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

I turned that feature on & it worked fine.

Michael Newell SSCrazy Points: 2802 More actions · Answer 3

This is a very useful script. However, there is really no need to use a cursor here. The code can be simplified just by deleting rows which do not contain a date within the first 10 characters and then processing the remaining set.

DECLARE @FilePath varchar(200);

SET @FilePath = 'dir C:\temp\*.pdf /-C '

--EXEC master.dbo.xp_cmdshell 'dir /?'

--EXEC master.dbo.xp_cmdshell 'dir c:\*.txt /-C '

DECLARE @Files TABLE (FileName varchar(200), FileSize varchar (20), DateCreated datetime);

DECLARE @Dir TABLE (FileInfo varchar(1000));

INSERT INTO @Dir

EXEC master.dbo.xp_cmdshell @FilePath;

DELETE FROM @Dir WHERE ISDATE(SUBSTRING(FileInfo, 1, 21)) = 0;

INSERT INTO @Files (DateCreated, FileSize, FileName)

SELECT

DateCreated = CONVERT(datetime, SUBSTRING(FileInfo, 1, 21)),

FileSize = SUBSTRING(LTRIM(SUBSTRING(FileInfo, 21, LEN(FileInfo))), 0,

CHARINDEX(' ', LTRIM(SUBSTRING(FileInfo, 21, LEN(FileInfo))))),

FileName = SUBSTRING(LTRIM(SUBSTRING(FileInfo, 21, LEN(FileInfo))),

CHARINDEX(' ', LTRIM(SUBSTRING(FileInfo, 21, LEN(FileInfo)))),

LEN(LTRIM(SUBSTRING(FileInfo, 21, LEN(FileInfo)))))

FROM @Dir;

SELECT * FROM @Files;

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 4

I would not use a CURSOR here, well anywhere really.... What I would do if you needed to iterate through a logic then I would use a TABLE VARIABLE.

There is however no need for iteration as stated here.

Good little script however.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

May 18, 2016 at 1:15 pm

#1880007

Thanks for the script.