Hi All,
My Friend mail me this piece of code and
I found it very useful, so I am here to
share this gud one you all. This code is written by Mr. Brian Walker, senior database architect in an IS
department that uses SQL Server 2000 and the .NET Framework. Hope you all like it..
This SQL code
creates a system stored procedure named sp_ListFiles. The routine returns a
filtered list of files found in a specified directory. The sp_ListFiles stored
procedure uses the xp_cmdshell extended stored procedure that comes with SQL
Server.
There are
security implications with using that tool. Normally, only a systems
administrator can execute xp_cmdshell, but others can be granted permission.
The SQL Agent Proxy Account is another way to control access to the potentially
dangerous tool. See BOL for details. The SQL Server service account (or the SQL
Agent proxy account) must be able to read the files in the specified directory.
If the specified directory is on a network share, the account may need to be a
domain account.
The sp_ListFiles
stored procedure accepts five parameters. Only the first one is required.
The first
parameter is a path to a directory. The path must be accessible to SQL Server (the
service account or the proxy account).
The second
parameter is a table name in which to insert the file/folder names. It can be a normal user
table or a temporary table. If no table name is provided, the list is returned
as a result set.
The third
parameter is a filter for including certain names. Each name is compared to
the filter using a LIKE operator, so wildcards are acceptable. For example, the
value "%.doc" would include all Word documents.
The fourth
parameter is a filter for excluding certain names. Each name is compared to
the filter using a NOT LIKE operator, so wildcards are acceptable.
The fifth
parameter determines whether files or folders are listed. A value of zero (0)
returns files and a value of one (1) returns folders.
The following
example lists the folders within the Program Files folder on the SQL Server
box:
EXECUTE sp_ListFiles 'C:\Program Files\',NULL,NULL,NULL,1
The list of files returned by sp_ListFiles may
consist of files that need to be imported into the database. The BULK INSERT
statement works nicely for this purpose, but it does not accept a variable for
the parameter that specifies the file to import. Because of this limitation
it's often convenient to execute the BULK INSERT statement using dynamic SQL so
the file to import can be determined on the fly.
The SQL code in Listing 2 demonstrates this by
returning the contents of a README file (assuming a typical installation of SQL
Server). The lines of the file are not necessarily returned in order in this
example, but if an order not supported by the data itself is required it can be
accomplished with an identity column in the temporary table and a format file.
I hope you find this system stored procedure to
be useful.
The Code
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 '%<DIR>%')
AND (@DBUltra != 0 OR Name NOT LIKE '%<DIR>%')
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) + '%<DIR>%' + CHAR(39) END
+ CASE WHEN @DBUltra != 0 THEN '' ELSE ' AND Name NOT
LIKE ' + CHAR(39) + '%<DIR>%' + 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
Usage Example:
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
6/23/2005 4:46:11 AM: 
