Digicorp

Facebook has always been touted to have strong privacy controls.  To most extent it is true and it is much better than most of the other social networks. But as a biggest social network they do have loopholes which can be easily exploitable by atleast developers like us.

Today I am going to talk about one such exploit:

How to view albums of people who are not your friends?

So when we search for interesting people on Facebook most of the time you will find their profile having restrictions. Mostly you will be able to see their profile photo and will be able to send message to them. So to see their full profile or albums we have to send “add friend” request and if they approve then and then only we can see their photos and profile.

But here is one way to bypass this:

First you have to add developers application in Facebook which can be added from following link:

Link:  http://www.facebook.com/developers/?ref=sb

Then go to this link

http://developers.facebook.com/tools.php

It will open below page and choose you response formate and also method = photos.getalbum as shown in below image.

So after that enter facebook user id whose photos you want to see, in “uid text box” as shown in below image and then press “call method button”.

Get the facebook id from the address bar/url bar or find the “add as a friend” link in the web page and move your mouse cursor over it and you will get facebook id in the status bar as shown in image.

And you will get details of all the albums of that user if he or she have albums as shown in below image in right side box.

Just you have to copy link content from right box and paste it to another browser or tab and press enter and then you can see all photos from that album without adding the user as a friend.

Note: I have changed all the ids they are not real.

I hope with the help of this article and community, facebook will notice this bug and will resolve it.

So enjoy peeking on stranger’s albums.

Update: It seems that Facebook has resolved this bug now.

Here is the customized design that would give a new look to your radio buttons & check boxes.
You can use different images you show the checked/unchecked check boxes & selected/unselected radio buttons.

Here is how your customized Check box & Radio buttons will look.

customized checkbox

customized radio button

View Demo

You can change the images (the css has the image paths) to give that different look to your radio button & check box elements.

Browser Compatibility: Script is compatible & checked in Firefox, IE 6 7 & 8, Safari 2.0, Opera 9.0 & above & Google Chrome.

Click to download the .zip file with the script, css, html page & images; to give that much needed customized look to your form elements.

Script reference taken from: www.flog.co.nz

In this article I have explained how to use CSS and jQuery function together.

I will give one example in which I have used CSS for textbox and one jQuery function for validation like numeric value and maximum 3 character allowed.

Example:

Create a CSS for textbox in which height, weight, border, font, text-align etc. is defined.

<style type =”text/css” >

.txt1

{

text-align :center ;

width :60px;

height :22px;

border: 1px solid #000000;

font-family: verdana;

font-size: 10pt;

font-weight:bold ;

}

</style>

Write jQuery function for validation for numeric value and maximum length of the textbox. Bind this function to textbox stylesheet on keypress event.

<script type =”text/javascript” >

$(document).ready(function (){

$(“.txt1″).bind(“keypress”,function(){

var str = (event.srcElement).value;

var len1 = str.length;

if (len1 > 2)

{

event.returnValue=false;

if(!(event.keyCode==45||event.keyCode==46||event.keyCode==48||   event.keyCode==49||event.keyCode==50||event.keyCode==51||   event.keyCode==52||event.keyCode==53||event.keyCode==54||   event.keyCode==55||event.keyCode==56||event.keyCode==57))

{
event.returnValue=false;
}

});

});

</script>

Now apply the CSS to the textbox.

<asp:TextBox ID=”Textbox1″ runat=”server” CssClass=”txt1></asp:TextBox>

By applying this CSS on textbox user can only enter numeric value upto 3 characters and also we have a good looking textbox.

I hope this simple solution will help you somewhere in your project.

(more…)

I personally face this issues in many Store Procedures i.e. Cursor taking too much time to execute. I am sure many of you will be having similar problem.

I have worked on that issue and found following solution. It may not be perfect for cursor optimization but it does work in my case.

– Original Cursor Code —

Declare @fk_MedicationOrderId as Bigint
Declare @AdminTime as Bigint

declare CUR_MEDICATIONORDERID cursor FOR
select
fk_MedicationOrderId
from
tbl_medicationorderDetail_trn
where
Actiondate > Getdate()

OPEN CUR_MEDICATIONORDERID

FETCH NEXT FROM CUR_MEDICATIONORDERID
INTO @fk_MedicationOrderId

WHILE @@FETCH_STATUS =0
BEGIN

Select @AdminTime=AdminTime from tbl_medicationorderDetail_trn
where fk_medicationorderid = @fk_MedicationOrderId

Update tbl_TempMedicationOrderDetail_trn
set ActionTime = @AdminTime
set @AdminTime = ”

FETCH NEXT FROM CUR_MEDICATIONORDERID
INTO @fk_MedicationOrderId

END

CLOSE CUR_MEDICATIONORDERID
DEALLOCATE CUR_MEDICATIONORDERID

——————-

– Modified Cursor Code (faster) –

Declare @fk_MedicationOrderId as Bigint
Declare @AdminTime as Bigint

Declare Table for the fields you need in the cursor.

declare @IDList table (ID Bigint)

Insert into @IDList
select
fk_MedicationOrderId
from
tbl_medicationorderDetail_trn
where
Actiondate > Getdate()

while (select count(ID) from @IDList) > 0
begin
select top 1 @fk_MedicationOrderId = ID from @IDList

Select @AdminTime=AdminTime from tbl_medicationorderDetail_trn
where fk_medicationorderid = @fk_MedicationOrderId

Update tbl_TempMedicationOrderDetail_trn
set ActionTime = @AdminTime
set @AdminTime = ”

delete from @IDList where ID = @fk_MedicationOrderId

end

——————-

What I have done here is to convert “cursor” into “while loop”. It is just to get your data in any temporary table or table variable after that just delete the rows from that at end of loop.

It helps in improving performance, I have personally tried it in many stored procedures.

Let me know if you find any other useful way of improving performance.

Download code & SQL statements:

Introduction:

We usually face problem in Stored Procedures and other database object when we need to implement some complicated logic within it. We found inefficient performance when we try to implement complex logic & business rules in database objects. In many cases we found C# or VB classes more powerful to implement such things. Microsoft has launched a new concept to resolve such issues with SQL server 2005 called “CLR Stored Procedure”.

What is CLR Stored Procedure?

Now, let us understand CLR stored procedure. CLR as most of .Net programmer knows is Common Language Runtime and Stored Procedures are routine stored procedures of database. Thus, CLR Stored Procedures are combination of both. As we all know, Common Language Runtime is core .Net component. The Common Language Runtime is runtime execution environment which supplies managed code with various services like cross language integration, code access security, lifetime management of object, resources management, threading, debugging & type safety etc. So now, CLR Stored Procedures are .Net objects which run in the memory of database.

The very first usage of CLR Stored Procedures can be said accessing system resources. Accessing system resources could also be done using Extended Stored Procedures which are again database object like Stored Procedures, Functions etc. Extended Stored Procedures can do most of the things which a standard executable program can do. Then, why are CLR Stored Procedures? The very first advantage of CLR Stored Procedures is it is a managed object unlike Extended Stored Procedures, which are unmanaged objects. The common thing between them is both runs under database memory. In this way CLR Stored Procedures gives all the benefits of managed objects. Following screen explains memory allocation while execution of CLR Stored Procedure.

When should I use CLR Stored Procedure?

Extended stored procedures run in the same process space as the database engine, memory leaks, bugs etc., can affects the performance of database engine. CLR stored procedures resolves these issues as they are managed object and runs per specifications of Common Language Runtime. CLR Stored Procedures can replace a standard stored procedure that contains complex logic and business rules. CLR Stored Procedures takes benefit of .Net classes and thus makes easy to implement complex logic, calculation, intense string operations, complex iterations, data encryptions etc., that are difficult to obtain in standard stored procedures. Standard stored procedures are still best for data oriented tasks. CLR Stored Procedures not only includes stored procedures but also includes Functions, Triggers etc. CLR Stored Procedures are compiled one so gives better performance.

Benefits of CLR Stored Procedures:

1. Gives better results while executing complex logic, intense string operation or string manipulations, cryptography, accessing system resources and file management etc.
2. CLR Stored Procedures are managed codes so ensures type safety, memory management etc.
3. Better code management and provides object oriented programming capability thus enables encapsulation, polymorphism & inheritance.
4. Convenient for programmer as CLR Stored Procedures can be written in C#, VB or any other language that .Net Framework supports.
5. CLR Stored Procedures can also be used with Oracle 10g Release 2 or later versions.
6. Not convenient in all contexts for e.g. it should not be used to execute simple queries. In that case standard stored procedures give better results.
7. Deployment may be difficult in some scenarios.

Drawbacks of CLR Stored Procedures:

1. Not convenient in all contexts for e.g. it should not be used to execute simple queries. In that case standard stored procedures give better results.
2. Deployment may be difficult in some scenarios.

Standard Stored Procedures vs. CLR Stored Procedures:

You are the best judge when to use regular Stored Procedures and when to use CLR Stored Procedures. CLR Stored Procedures can be used in following scenarios.

1. When the program requires complex logic or business rules.
2. When the flow is CPU intensive. CLR Stored Procedures gives better results as they are in complied form and managed one.
3. The tasks which are not possible in TSQL, accessing system resources, cryptography, accessing web services etc.
4. In option of Extended Stored Procedures. One should always consider CLR Stored Procedures before going for Extended Stored Procedures.
5. An operation requires higher data safety.

Creating CLR Stored Procedure step by step:

Let us create one simple CLR Stored Procedure which fetches all the rows from one table of the database. I have attached whole list of SQL statement used for creating database, creating table, inserting dummy records in the table etc., under “SQL statements used in the demo” section.

Application development specification:

• IDE: Visual Studio 2008
• Framework: 3.5 with SP 1
• Language: C# 3.0
• Database MS SQL Server 2005 Express edition

Steps to create CLR Stored Procedure:

1) Open Microsoft Visual Studio >> Click on New Project >> Select Database Projects >> SQL Server Project.

2) You can choose reference of existing database connection or click on Add New Reference.

3) If you selects from existing references skit step 3 else add new database reference as shown in following image and click on Test Connection to test the connection.

4) On clicking on OK button, Visual Studio will ask you to enable SQL/CLR debugging on the selected connection. You can select “Yes” to enable debugging or “No” to disable the same.

5) Once the database reference and debugging option is selected, the project will be displayed in Solution Explorer. Select the project and right click on Solution Explorer >> Click on Add >> Stored Procedure.

6) Add new procedure from the installed templates as shown in following screen. Give proper name to it.

7) Once you select the template, it will create .cs file with the content shown in following image.

8 ) Add following code in the method already created. Pass “context connection=true” as connection string in the constructor while creating new SqlConnection. This CLR stored procedure is going to be the part of the database, so it will be the internal part of database so no need to connect database externally. So, no need to provide connection string that we usually provide in applications. Then Click on Build menu >> Click on Build Solution. Also click on Build menu >> Deploy solution. This will deploy the assembly to the database for which we have made connection initially.

9) Now, select the database >> Programmability. Right click on Stored Procedures >> Click on Refresh. The list of Stored Procedures should show one newly added stored procedure. Also right click on Assemblies >> click on Refresh. This should show newly added Assembly. Also, enable CLR Stored Procedure by following query.

sp_configure ‘clr enabled’, 1

Run following query to take effect or above query.

RECONFIGURE

Now, execute the stored procedure. It should give similar results shown in following screen.

SQL statements used in the demo:

Following is the queries to create database, create table, insert records in the table etc.

–Create a new databse for demo

CREATE DATABASE DbForClrDemo

–Use database

USE DbForClrDemo

–Create table for CustomerSalesInformation

CREATE TABLE [dbo].[CustomerSalesInformation](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) NOT NULL,

[Sales] [decimal](18, 2) NOT NULL DEFAULT ((0)),

CONSTRAINT [PK_CustomerSalesInformation] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

–Insert dummy data to CustomerSalesInformation table

INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (’Virat Kothari’, 50000)

INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (’Dhruval Shah’, 5000)

INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (’Urvish Sheth’, 15000)

INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (’Rakesh Bajania’, 25000)

INSERT INTO [dbo].[CustomerSalesInformation]([Name], [Sales]) VALUES (’Dhaval Shah’, 150000)

–Enable CLR Stored Procedure in database

sp_configure ‘clr enabled’, 1

–Run following statuement to take effect of above statement

RECONFIGURE

–Now execute our CLR Stored Procedure. Remember “ClrDemo” is name of our Stored Procedure

EXEC [dbo].ClrDemo

Conclusion:

CLR Stored procedures are very simple and can be used in most complex scenarios.

Bibliography:

1. MSDN (http://msdn.microsoft.com/en-us/library/ms131094.aspx)
2. Code project
3. http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1251402,00.html

Reference : Virat Kothari (www.viratkothari.wordpress.com)

This Article is just an introduction to .Net Remoting. We will see in which situation .Net Remoting is useful and technical view of .Net Remoting.

What is .Net Remoting?

In General, Remoting is communication between two application domains. One application domain can communicate with other application domain with help of some kind of medium. This is how .Net Remoting works.

Now let’s see how .Net Remoting is useful in our real life scenario.

Suppose there is one company name ‘ABC‘.

It main office is in Ahmedabad city location. And there are some branches are in Ahmedabad city only but in different areas. And Outside Branches also there may be in different cities like Surat, Baroda, and Mumbai etc.Now all the branches are connected with main office and all the transaction done through main office only. So all the branches in all the places should connect with main office and communicate their data also.

So in this kind of situation .Net Remoting is very useful technology.

.Net Remoting works on three tier Application.

1.    Client
2.    Interface
3.    Server

In this Client side, generally UI is there where user can directly interact with the application. They put some input and get some output in any format like reports, graphical or any.

And in Server side all the business logic is there. If user input some data then all the transaction is done in server side. And last one is interface. Interface is medium to connect server and client. So many clients are connected with one server only through interface.

For example,
In above situation of ‘ABC’ company.

Server application is installed in Ahmedabad main office only and all other branches clients are installed. So all clients are connected with server means Ahmedabad main office. In main office there is main server is there and database is also there.

So when one client application sent some request through interface then that will come into the main server machine and then if they want to some data from data base then go to database and get it back to server application and back data to the client. If data is huge then data base server is installed in different machine other than different machine where server application is installed so transaction gets very fast.

Now we understand how .Net Remoting works internally.

DCOM is replace by .Net Remoting. By using .Net remoting, we can make remote object calls which lies in different application domain.

What is Application Domain?

Before we start to understand .Net Remoting, we just go through what is Application Domain?

Generally process used their owned security boundaries. In the Process there are number of application can run and one process has its own virtual memory and does not effect or overlap to other processes virtual machines. So one process can’t crash with other process. So same thing this concept used in .Net and more over ahead of this, concept of Application Domain are used. In Application Domain, Multiple Application can run in same process without influencing each other.  In that, if one application domain throws errors it does not affect or stop whole process and not effect with other application domain. To invoke method in an object running application domain .Net Remoting is used.

General Concept of .Net Remoting and its Architecture

Remoting has at least three sections:
•    Interface
•    Client
•    Server

in .NET Remoting, the client does not call the methods directly. A proxy object is used to raise methods on the remote object. All the public methods that are defined in the remote object class are also available to be called from clients.

This method of calls is called Message. These messages are serialized using formatters and then send message through channel from client. Thus Client channel communicate server channel and there server channel receive message and de serialised through server formatter and sent to the server remote objects.Generally .Net Remoting can be used across any protocols.

Any objects are created outside to the boundaries of Application Domain then it is called remote object to the AppDomain.  These remote objects can access by references or by value.

What is marshalling and different kind of Marshall?

Marshalling is used when object are converted. So these objects can travel throughout the network by channel and formatter.
There are two types of marshalling in the .net remoting.

•    Marshall-By-Ref
•    Marshall-By-Values

In the MBR, create proxy to communicate with other side of the network or remote object and create ObjRef instance that itself serialized.

ObjRef Returns by all Marshall () methods and it contains location of remote object, Hostname, Port Number and Object name etc.

For example:

MarshalByRefObject obj  =(MarshalByRefObject)RemotingServices.Connect(typeof(Ilogin), “tcp://LOCALHOST:65101/ITestEndPoint”);

// Set a reference to the service. After this, you use testRemoteReference
// just as if it was a local instance of ITestImplementation.

loginreference = obj as Ilogin;

Whereas in MBVs objects, Copy of the object is created on the other side (i.e. server side). The object to marshal is stored in to a stream, and a stream is used to crate copy of the object.

So Marshalling is a process of packing object and a call and its arguments made by an AppDomain on a remote object into a unit that’s easily transferable across the network. In other we can say that Marshalling is process to transfer message from one place to other means its destination. Once marshal reached its destination, an unserialized process is taken place and returns its original data by this.

Difference between MBV and MBR

MBVs is that they get serialized with all the private and public data members. But that may be cause problem if a client intercepts the serialization/de-serialization phase to harm application.
While MBRs, only clients decide which objects can travel across there network. He doesn’t have to load entire copy of the object if he wants to call some functions or some procedures only. And MVB execute in its original native places only where it governed by CLR. So Application May misbehaviour in some cases but that is happened vary rarely.

In .Net Remoting there are two ways to create remote objects.

Server activated objects (SAO)
Client activated objects (CAO)

SAO has two modes named ‘Single call’ and ‘Singleton’.

Single call is an object that object created with every method all or server trip. So all these objects are stateless. And after use of that object it is destroyed and every time creates new objects. Single Call objects are useful in scenarios where the objects are required to do a finite amount of work.

While in other side, in Singleton the object is created only once and the object is shared with all clients. So this kind of objects is used for share object through out the application. So singleton objects are useful in many applications like chat application etc.

CAO is not stateless compare to SAO. Client holds proxy to the server object on the client side which is created on server object on server side.

Example of Single call and Singleton:

Singleton:

// Register the interface and end point.
//
// Each service has a unique endpoint and this endpoint is how a client
// accesses a specific service on a remote server.

Type ilogin = Type.GetType(”login”);
RemotingConfiguration.RegisterWellKnownServiceType
(ilogin,”IloginEndpoint”,WellKnownObjectMode.Singleton);

Singlecall:

// Register the interface and end point.
//
// Each service has a unique endpoint and this endpoint is how a client
// accesses a specific service on a remote server.

Type ilogin = Type.GetType(”login”);
RemotingConfiguration.RegisterWellKnownServiceType
(ilogin,”IloginEndpoint”,WellKnownObjectMode.SingleCall);

In next article we will see more about .Net Remoting like Channels and Formatters, Object Activation and Lifetime and simple example of .Net Remoting .

In last post, we identified what issues can arise if we keep going with the design shown in Etymology Of Decorator Pattern. Lets take a quick look at what fundamental design problems exists in the design that we have already discussed.

In the existing design, we have all the possible toppings of Pizza hard-coded into the Pizza class. Which are, in fact, individual Object in the real world. So in a way, they are Add-on objects that can be used to decorate our pizza. If we want to add new item as toppings, we need to modify the class. Ideally, we should be able to add any new topping “without modifying the class”, hence we come to know Open-closed principal.

“Classes should be closed for modification and open for enhancement.”

Now question is how to achieve this. Let’s note down what approach we need to follow to get maximum flexibility in design (maximum in terms of toppings and calculating its cost) in creating one FreshVeggiePizza with Paneer and Olives.
1. Take plain Pizza object.
2. Decorate it with Paneer.
3. Decorate it with Olives.
4. Call cost() method, which will be delegated to “Add-on” or “Decorator” objects also to add up their cost.

Read More >>

As we have discussed how to build the rendered control earlier, it is one step ahead in building custom controls. Earlier we have built the custom control starting from scratch. If you don’t want to start from scratch when building a custom control, you can build a composite control. When you create a composite control, you create a new control from existing controls.

Every ASP.NET control has a Controls property that represents all of its child controls. If you add child controls to a control, then the child controls are automatically rendered when the parent control is rendered.

Lets start from the beginning, Creating NumericTextBox (Only accepts Numeric values, else it give error message through validator.) is shown below. Create new VB Web Application Project and let’s name it NHSWebApplication. After Creating Web Application project just right click on App_Code folder (if not in solution then first create folder named App_Code)  and add one class file named  NumericTextBox.vb.

Note: Any code added to the App_Code folder is compiled dynamically.

The Code of NumericTextBox.vb file is as shown below

Namespace NHSWebApplication

Public Class NumericTextBox

”Inherit the control from Base Class CompositeControl

Inherits CompositeControl

”Declaring TextBox Web Control

Private NumericTextBox As TextBox

”Declaring ReqularExpressionValidator Web Control

Private NumericExpressionValidator As RegularExpressionValidator

”Overriding CreateChildControlMethod

Protected Overrides Sub CreateChildControls()

”Instanciate TextBox Control

NumericTextBox = New TextBox

”Assigning ID to text box control

NumericTextBox.ID = “txtNumericTextBox”

”Adding control

Me.Controls.Add(NumericTextBox)

”Instanciate RegularExpression Control

NumericExpressionValidator = New RegularExpressionValidator

”Assigning ID to RegularExpressionValidator Control

NumericExpressionValidator.ID = “vdtNumericExpressionValidator”

”Assigning Basic Properties to RegularExpressionValidator

NumericExpressionValidator.ControlToValidate = “txtNumericTextBox”

NumericExpressionValidator.ValidationExpression = “[0-9]*”

NumericExpressionValidator.ErrorMessage = “Invalid Inputs”

NumericExpressionValidator.Display = ValidatorDisplay.Dynamic

”Adding Cotnrol

Me.Controls.Add(NumericExpressionValidator)

End Sub

End Class

End Namespace

Here Class NumericTextBox is inherited from the base class CompositeControl. Two webcontrols Textbox and RegularExpressioValidator are declared.

Typically one has to override a control’s CreateChildControls() method. This method is called when a control builds its collection of child controls. In this method the objects of Textbox and RegularExpressionValidator are initialized and the some of the properties are set in order to get desired output.

”Assigning ID to text box control

NumericTextBox.ID = “txtNumericTextBox”

”Assigning ID to RegularExpressionValidator Control

NumericExpressionValidator.ID = “vdtNumericExpressionValidator”

”ID of controls that needs to be validate

NumericExpressionValidator.ControlToValidate = “txtNumericTextBox”

” Regular Expression that only accepts numeric values (0 to 9)

NumericExpressionValidator.ValidationExpression = “[0-9]*”

” Informative Message that needs to display on wrong input

NumericExpressionValidator.ErrorMessage = “Invalid Inputs”

Now just build your application and then add new Web Form. Let’s say TestCustomControl.aspx. Open its design view or HTML view and see the Toolbox. You will find the custom control named NumericTextBox. Just drag it on Design view or html code. See sample code of ASPX Page below.

<html xmlns=”http://www.w3.org/1999/xhtml” >

<head runat=”server”>

<title>Testing Composite Control</title>

</head>

<body>

<form id=”form1″ runat=”server”>

<div>

<cc2:NumericTextBox ID=”NumericTextBox1″ runat=”server” />

</div>

</form>

</body>

</html>

It will render the Textbox in browser with RegularExpressionValidator. The regular expression restict user to input non-numeric values. If any non-numeric values entered in textbox then it will show message ‘Invalid Inputs’. Here in this example, the message is kept static. You can always make it dynamic by creating properties of composite control and passed it from HTML.

By extending the properties of existing web control you can create more helpful composite controls. Hope this article will help to understand building custom composite control.

Following is the script that I used in one of my recent projects. I had to create an rounded corner box with css (no images) & it had to be compatible in Firefox, IE 7, Safari & Chrome. I tried using different java-scripts & css coded available on “The net”.

Finally, I succeeded with it (combining the best code lines from all of them) & amazingly, without using any javascript .

Here is the code:

XHTML Required:

CSS Required:

and you are done..

You can click here to see the demo of above example.

Rounded corners done in this style will work on the following types of elements:

• floated elements
• absolutely positioned elements
• fixed width elements
• percentage width elements

Where The Rounded Corners Don’t Work

You can’t use this exact style with a fixed height or with padding on the container element. But you can solve that problem by creating a second container element that assigns your height or padding.

This technique doesn’t work on IE 5.x for Windows.

There are also some problems(not much visible) with text-indent in Internet Explorer.

I hope, this will be a useful piece of XHTML/ CSS code; for you guys out there who had to slice the images everytime the developer asked for an dynamic box with rounded corners.

People are judged from their questions and not their answers. I received wonderful question the other day.

How sqlcmd can be used along with currently running query script posted on your blog?

Please read following two posts before continuing this article as they cover background of this article.

SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor

SQL SERVER – Find Currently Running Query – T-SQL

If due to long running query or any resource hogging query SQL Server is not responding sqlcmd can be used to connect to server from another computer and kill the offending query. SQL Server provides a special diagnostic connection which allows administrators to access SQL Server when standard connection is no tpossible. Except very extreme situations dedicated administrator connection (DAC) is always successfull.

Let us see how we can use DAC using sqlcmd.

Connect SQL Server using sqlcmd and -A option which will establish DAC.

C:\>sqlcmd -S localhost -U sa -P dev -d master -A

For our test purpose run following query which overload server and probably make it unresponsive. If your configure is not strong I strongly suggest not to run following query.

Following query is just for test purpose and not part of solution.

USE AdventureWorks
GO
SELECT *
FROM Person.Address
CROSS JOIN Person.Address a1
CROSS JOIN Person.Address a2
CROSS JOIN Person.Address a3
CROSS JOIN Person.Address a4
CROSS JOIN Person.Address a5
CROSS JOIN Person.Address a6
CROSS JOIN Person.Address a7
CROSS JOIN Person.Address a8
CROSS JOIN Person.Address a9
GO

Once successfully connected it will provide prompt 1> enter following T-SQL query which will give SessionID of currently running query along with its elapsed time.

SELECT
req.session_id,
req.status,
req.total_elapsed_time
FROM sys.dm_exec_requests req
WHERE status = 'running'
AND req.total_elapsed_time > 1

Our previously running query gave session id 52 in on my server. The session id may be different for each SQL Server. Once the session id is figured out it can be killed using KILL [SessionID] command. Always make sure to type command GO after each complete query.

KILL 52

Once above query has run it will kill our example long running query and give following error.

Msg 233, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)

sqlcmd is really useful utility of SQL Server and it comes in handy when server is not responsive. I strongly suggest to bookmark this article as it can come to rescue when nothing works and SQL Server is unresponsive.

Reference : Pinal Dave (http://blog.SQLAuthority.com)