Post reply

extended stored procedures

  • July 28, 2003 at 8:36 am

    #162111

    How can I get a list of all the files that exists in the folder from QA? (extended stored procedures?)

  • July 28, 2003 at 8:42 am

    #467292 

    

    EXEC master.dbo.xp_cmdshell 'dir c:\MSSQL7'

    Of course, replace c:\MSSSQL7 with whatever directory on the server you want...

  • July 28, 2003 at 8:51 am

    #467295

    Thanks for your help!

  • July 28, 2003 at 9:05 am

    #467298

    If I do this way I get additional information: date, time, size….

    Is there any way to get only file names?

    I have to load into table just file names. How to process this output information?

    Will the name always starts from fixed position?

  • July 28, 2003 at 9:18 am

    #467301

    Exec master.dbo.xp_cmdshell 'dir d:\MSSQL7 /b'

    the '/b' switch should return only the filenames (and subdirectory names) from the directory...

    To list only the files

    Exec master.dbo.xp_cmdshell 'dir d:\MSSQL7 /b/a-d'

    good old DOS commands....

  • July 28, 2003 at 9:23 am

    #467302

    OR, another way, just to show you examples...

    

    CREATE TABLE #temp (OutputText NVARCHAR(1000))

    --

    INSERT INTO #temp

    EXEC sp_executesql N'exec master.dbo.xp_cmdshell ''dir c:\MSSQL7\Binn'''

    --

    SELECT RIGHT(OutputText, CHARINDEX(' ', REVERSE(OutputText))) FROM #temp

    WHERE PATINDEX('%<DIR>%', OutputText) = 0 -- Make sure no directories

    AND CHARINDEX('/', SUBSTRING(OutputText, 1, 5)) > 0 -- Make sure has a date

    --

    drop table #temp

  • July 28, 2003 at 9:28 am

    #467307

    It works! Thanks!

  • July 28, 2003 at 9:34 am

    #467308

    thanks for that info Jpipes...I'm going to use it for my requirement of getting filenames and dates...this is better than my previous "parsing mechanism"....

  • July 28, 2003 at 11:03 am

    #467323

    no problemo. just a script I thought of this morning...worked great (surprisingly...)

    --

    Cheers

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

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