Technical Article

Get list of files from dir and put in table

,

This stored procedure reads the file names from a directory and stores just the names in a given table. It returns also the number of files present.

The sp uses xp_cmdshell and so permissions are restricted to sysadmins and SQLAgentCmdExec.

You can test with the following:

Create Table ##tmp2

(

fldxnvarchar(255)

)

Declare @FilePath nvarchar(2048)

, @tblName nvarchar(255)

, @FileCount int

Set Nocount on

Select @FilePath = 'C:\Test1'

, @tblName = '##tmp2'

, @FileCount = 0

EXEC master.dbo.sp_DirFiles

@FilePath

, @tblName

, @FileCount OUTPUT

Select * From ##tmp2

select 'Filecount = ', @FileCount

drop table ##tmp2

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DirFiles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_DirFiles]
GO

CREATE PROCEDURE dbo.sp_DirFiles
(  
   @FilePath  nvarchar(2048)   = NULL
 , @tblName  nvarchar(255)     = NULL 
 , @FileCount int   = 0 OUTPUT
)

AS 

/********1*********2*********3*********4*********5*********6*********8*********9*********0*********1*********2*****
**
**  $Archive$
**  $Revision$
**  $Author$ 
**  $Modtime$
**
*******************************************************************************************************************
**
**  $Log$
**
*******************************************************************************************************************
**
**Name: sp_DirFiles
**Desc: This procedure copies filenames from directory to specified table
**It returns the file count.
**NOTE: 
**
**
**
**Return values: 0 = Successful, error number if failed
**              
*******************************************************************************************************************
**Change History - All Author comments below this point.
*******************************************************************************************************************
**  AuthorDateDescription
**  ----------------------------------------------------------
**  NBJ31-Jan-2002Original - SP to get filenames into a table
******************************************************************************************************************/
Declare   @Err int
,@cmd nvarchar(3000)
,@cmd1 nvarchar(4000)
,@cmd2 nvarchar(4000)
Select @Err = 0

If @FilePath is Null
Begin
Return 1
End
If @tblName is Null
Begin
Return 2
End
Set nocount on

Create Table #tmp1
(
fld1nvarchar(255)
)
Select @Err = @@Error
If @Err <> 0 
Begin
Return @Err
End

Select @cmd = 'DIR /B /A-D ' + Rtrim(@FilePath) 
Insert #tmp1 Execute master.dbo.xp_cmdshell @cmd 
Select @Err = @@Error
If @Err <> 0 
Begin
Return @Err
End

EXEC ('Insert ' + @tblName + ' Select * from #tmp1 where Not(fld1 Is Null) and Not(fld1 = ''File Not Found'') ')

Select @Err = @@Error, @FileCount = @@rowcount
drop table #tmp1
If @Err <> 0 
Begin
Return @Err
End

Return 0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

1 (1)

Share

Share

Rate

1 (1)