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

How to connect HTML Page with SQL Server

Connecting HTML page with SQL Server is very interesting and can be used very often. No postback, no page flickering and good performance indeed.

Here is the code:

<script language=”javascript” type=”text/javascript”>

function callonload() {

var container = document.getElementById(“myContainerDiv”);

var html;

var objConnection = new ActiveXObject(“ADODB.Connection”);

//var strConn=”Data Source=<SERVER>;Initial Catalog=<DATABASE-NAME>;User ID=<USERNAME>;Password=<PWD>;Provider=SQLOLEDB;”;

var strConn = “Provider=SQLOLEDB;Data Source=localhost;Trusted_Connection=Yes;Initial Catalog=Sales_DW;”

objConnection.Open(strConn);

var rs = new ActiveXObject(“ADODB.Recordset”);

var strQuery = “SELECT [CustomerID],[CustomerAltID],[CustomerName] FROM [Sales_DW].[dbo].[DimCustomer]”;

rs.Open(strQuery, objConnection);

rs.MoveFirst();

html = “<table cellpadding=10 cellspacing=10 style=’border-color:Silver;border-width:1px;border-style:solid;width:100%;’>”;

html = html + “<tr style=’height=15px;’><th>” + rs.fields(0).Name + “</th><th>” + rs.fields(1).Name + “</th><th>” + rs.fields(2).Name + “</th></tr>”;

 

while (!rs.EOF) {

html = html + “<tr ><td>” + rs.fields(0) + “</td><td>” + rs.fields(1) + “</td><td>” + rs.fields(2) + “</td></tr>”;

rs.movenext();}

html = html + “</table>”

 

container.innerHTML = html;

}

</script>

 

<div id=”myContainerDiv”></div>

Here Grid will be rendered where ever myContainerDiv Div control will be placed.

I have used trusted connection with Database, you can change its connection string according to your need.

This piece of code will work well with HTML/ASPX pages.

 

Get query from SPID

When number of queries are executing in SQL Server, we need some code to get that query from SPID which is taking time to execute

First get the SPID which is creating problem, then execte the following code for that SPID.

DECLARE @sqltext VARBINARY(128)

SELECT @sqltext = sql_handle FROM sys.sysprocesses

WHERE spid = 61

SELECT TEXT FROM sys.dm_exec_sql_text(@sqltext)

GO

Here, 61 is SPID. This will return the query being executed at this SPID.

To execute this code requires VIEW SERVER STATE permission on the server.

With this permission on DB Server, User can Read Dynamic Management Views and Functions

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

References:

http://technet.microsoft.com/en-us/library/ms181929(v=sql.110).aspx

How to find if File exists in File System or not using SQL Server Query

I was working in a Project in which different files were getting uploaded on the Server along with the db entry. Due to heavy load on Application and Database Servers I found uploaded file was missing but db entry was present. In order to identify if corresponding file exists, I got this code that has solved my problem.

DECLARE @result INT

EXEC master.dbo.xp_fileexist ‘D:\Docs\MyFile.jpg’, @result OUTPUT

SELECT @result

Here, if file exists in the path mentioned in red it will return 1 else 0.