Get list of files in File System using SQL Server

This article is to get list of file exists in some file location in File System. Some time back I found one problem, some images were getting uploaded from my web application. To cross check if the number of files uploaded matches with data entries, I need to have list of files uploaded in my SQL Server Database, So that I could have comparison between the numbers. We can do this by getting the list using command prompt and saving it in txt file and then importing it in SQL Server or by directly calling shell command in SQL Server.
CodeProject
1. First configure database to execute shell command:

2. Create table to insert the File Names:

3. Execute the following command to insert the file names in the table or you can execute the command directly without INSERT command to get the result in query window itself:

Here, ‘DIR‘ is the command to list down the files and folders ‘ E:\‘ is the path ‘ /A-D‘ is the attribute to filter the list.

Now you can compare myFileList table with the actual file names and count uploaded. You can have a look on various other attribute options available along with this shell command.

 

Reference for shell commands:

http://msdn.microsoft.com/en-us/library/foo18935cf4-b320-4954-b6c1-e007fcefe358.aspx

Create Excel file with dynamic file names using BCP in SQL Server 2008 R2

We can export data returned from SQL Server query or Procedure to Excel or txt/csv format with dynamic file names or path using SSMS Data Export or SSIS Package or by using BCP command as discussed in this article.

Configuration required to execute shell commands:

EXEC sp_configure ‘show advanced options’, 1

RECONFIGURE WITH OVERRIDE

GO

EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1

RECONFIGURE WITH OVERRIDE

GO

declare @filePath varchar(500), @ssql varchar(max)

select @filePath=‘D:\Excel\Data_’+ CONVERT(varchar,getdate(),105) + ‘.xls’

set @ssql=‘EXEC xp_cmdshell ”BCP “exec MyDB.dbo.usp_GetExcelData 1” queryout “‘ + @filePath + ‘” -c -S 172.24.1.11 -T”’

exec(@ssql)

 Here MyDB.dbo.usp_GetExcelData denotes SQL procedure name to be executed.

The path can have from 1 through 255 characters.

The data file can contain a maximum of 263 – 1 rows.

For more information on BCP command options to import and export data into various formats, Please refer:

http://msdn.microsoft.com/en-IN/library/ms162802.aspx

This feature is very useful, in case some exportable reports are required and data is heavy. So instead of putting load on web server, one can create the excel file by calling such proc in a job and saving file creation time.

Njoy 🙂