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

(

fldx nvarchar(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.
*******************************************************************************************************************
**  Author	Date		Description
**  -------	--------	-------------------------------------------
**  NBJ		31-Jan-2002	Original - 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
	(
	fld1	nvarchar(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)