Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL-Get Windows Folders Files in table with file parameters Expand / Collapse
Author
Message
Posted Thursday, July 29, 2010 9:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 15, 2010 9:09 AM
Points: 3, Visits: 4
Comments posted to this topic are about the item TSQL-Get Windows Folders Files in table with file parameters
Post #960790
Posted Thursday, July 29, 2010 12:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 4, 2013 9:42 AM
Points: 107, Visits: 34
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'.
Post #960938
Posted Friday, July 30, 2010 6:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:54 AM
Points: 1,516, Visits: 2,722
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.
Post #961315
Posted Saturday, August 7, 2010 10:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 8:13 PM
Points: 2,080, Visits: 603
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;
Post #965563
Posted Friday, August 13, 2010 6:54 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:27 AM
Points: 143, Visits: 641
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
Post #969327
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse