-------------------------------------------------------------------- Stored Procedure: sp_ListFiles -------------------------------------------------------------------- USE master GO CREATE PROCEDURE dbo.sp_ListFiles @PCWrite varchar(2000), @DBTable varchar(100)= NULL, @PCIntra varchar(100)= NULL, @PCExtra varchar(100)= NULL, @DBUltra bit = 0 AS SET NOCOUNT ON DECLARE @Return int DECLARE @Retain int DECLARE @Status int SET @Status = 0 DECLARE @Task varchar(2000) DECLARE @Work varchar(2000) DECLARE @Wish varchar(2000) SET @Work = 'DIR ' + '"' + @PCWrite + '"' CREATE TABLE #DBAZ (Name varchar(400), Work int IDENTITY(1,1)) INSERT #DBAZ EXECUTE @Return = master.dbo.xp_cmdshell @Work SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain IF @Status = 0 SET @Status = @Return IF (SELECT COUNT(*) FROM #DBAZ) < 4 BEGIN SELECT @Wish = Name FROM #DBAZ WHERE Work = 1 IF @Wish IS NULL BEGIN RAISERROR ('General error [%d]',16,1,@Status) END ELSE BEGIN RAISERROR (@Wish,16,1) END END ELSE BEGIN DELETE #DBAZ WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING(Name,40,1) = '.' OR Name LIKE '%.lnk' IF @DBTable IS NULL BEGIN SELECT SUBSTRING(Name,40,100) AS Files FROM #DBAZ WHERE 0 = 0 AND (@DBUltra = 0 OR Name LIKE '%%') AND (@DBUltra != 0 OR Name NOT LIKE '%%') AND (@PCIntra IS NULL OR SUBSTRING(Name,40,100) LIKE @PCIntra) AND (@PCExtra IS NULL OR SUBSTRING(Name,40,100) NOT LIKE @PCExtra) ORDER BY 1 END ELSE BEGIN SET @Task = ' INSERT ' + REPLACE(@DBTable,CHAR(32),CHAR(95)) + ' SELECT SUBSTRING(Name,40,100) AS Files' + ' FROM #DBAZ' + ' WHERE 0 = 0' + CASE WHEN @DBUltra = 0 THEN '' ELSE ' AND Name LIKE ' + CHAR(39) + '%%' + CHAR(39) END + CASE WHEN @DBUltra != 0 THEN '' ELSE ' AND Name NOT LIKE ' + CHAR(39) + '%%' + CHAR(39) END + CASE WHEN @PCIntra IS NULL THEN '' ELSE ' AND SUBSTRING(Name,40,100) LIKE ' + CHAR(39) + @PCIntra + CHAR(39) END + CASE WHEN @PCExtra IS NULL THEN '' ELSE ' AND SUBSTRING(Name,40,100) NOT LIKE ' + CHAR(39) + @PCExtra + CHAR(39) END + ' ORDER BY 1' IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR IF @Status = 0 SET @Status = @Return END END DROP TABLE #DBAZ SET NOCOUNT OFF RETURN (@Status) GO -- Listing 2 CREATE TABLE #Files (MyFile varchar(200)) CREATE TABLE #Lines (MyLine varchar(8000)) DECLARE @MyFile varchar(200), @SQL varchar(2000), @Path varchar(400) SET @Path = 'C:\Program Files\Microsoft SQL Server\MSSQL\' EXECUTE sp_ListFiles @Path,'#Files','%.txt',NULL,0 SELECT @MyFile = MyFile FROM #Files WHERE MyFile LIKE 'README%' SET @SQL = 'BULK INSERT #Lines FROM ' + CHAR(39) + @Path + @MyFile + CHAR(39) EXECUTE (@SQL) SELECT * FROM #Lines DROP TABLE #Files DROP TABLE #Lines --------------------------------------------------------------------