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.

Search string in all DB Objects in all databases

Sometimes we need to search for a string in table names, Column Names, View definitions or Procedure definitions. By executing these queries one can safely replace the string in all of its dependent Objects.

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘%MyData%’

SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE VIEW_DEFINITION LIKE ‘%MyData%’

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ‘%MyData%’

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%MtData%’

The above queries will search the string in all the Databases on a Server.