Thursday, December 10, 2009

Using Xpaths in Sql server

When we were using Sql 2000, we had very less functionalities available for xml operations. After Sql 2005, xml operations are very easy and make it very simple to handle. We have new datatype XML which allows to do lot on XML on sql server side. I am just going to take a simple example of a table which will save the xml data and how to fetch using conditions.

Ok. Here is our table. Let us call it as Contents

CREATE TABLE [dbo].[Contents](
[ContentID] [int] NOT NULL,
[ContentData] [xml] NULL,
[ContentCreatedDate] [datetime] NULL

We have column ContentData which is of type xml. I am going to insert few records to this table now.

insert into Contents values (1,'<customer><info id="1" fname="Subbu" lname="P"> <address address1="#444 bangalore" phone="12345"></customer>',GETDATE())

insert into Contents values (2,'<customer><info id="2" fname="Ram" lname="T"> <address address1="#111 Mumbai" phone="12345"></customer>',GETDATE())

insert into Contents values (3,'<customer><info id="3" fname="Raj" lname="M"> <address address1="#222 Pune" phone="12345"></customer>',GETDATE())

insert into Contents values (4,'<customer><info id="4" fname="Ravi" lname="R"> <address address1="#333 Mysore" phone="12345"></customer>',GETDATE())

Now let’s do a select from Contents table. You will get below result set.

You can see all the data inserted and the xml. Now lets say you need to pick the data based on the xml value. Let’s consider you need to find all the Customers from Pune. How to retrieve the data?

Select *,ContentData.value('(/Customer/Address/@Address1)[1]','varchar(20)') as Address from Contents where ContentData.value('(/Customer/Address/@Address1)[1]','varchar(20)') like '%Pune'

So here is our result set

We have passed the Xpath /Customer/Address/@Address1 to the value method which will process the xml and provide the result set. The value method takes two parameters.

With XML, we can also pass information between sps without worrying about the paramenters which gives lots of flexibility on design. I hope this small piece gives a brief idea on how to use xml and xpaths on your sql.

Tuesday, December 8, 2009

How to delete duplicate records on table without primary key

Deleting from the table which has a primary key is straight forward. If we have to delete a record from a table which does not have any primary key, then how do we delete it?
Lets take an example of table

CREATE TABLE [dbo].[Employee](
[Name] [varchar](50) NULL,
[DeptId] [int] NULL

Now I will insert few records to this table

Insert into Employee values ('User1',1)
Insert into Employee values ('User2',1)
Insert into Employee values ('User1',1)

So now we have 2 records with User1 and one record with User2. Lets try to delete the User1 from above table using below query

delete from Employee where Name='User1'

(2 row(s) affected)

But I just wanted to delete only one record. Here is how we can do it

delete from Employee where Name='User1'

(1 row(s) affected)

SET ROWCOUNT Causes SQL Server to stop processing the query after the specified number of rows are returned. For more details you can check

In Sql 2005 and above we can also use Top 1 in our Delete query.

Monday, December 7, 2009


IDENT_CURRENT is new built in function introduced in SQl 2005. By looking at the function name it looks very similar to SCOPE_IDENTITY.

I had one of the sp which was using IDENT_CURRENT and it was all working fine till I came across a problem which was reported last week. The sp which was returning Identity value had returned same idenity value for 2 sessions which occurred at the same time. Yes. the IDENT_CURRENT considers only last identity for the specific table in any session and any scope.

So if you have stored procedure running different sessions which is returning IDENT_CURRENT, there is a chance that you might get the latest inserted identity value from another session.

Here is the exact definition and difference between SCOPE_IDENTITY, IDENT_CURRENT, @@IDENTITY

•IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

•@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

•SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

So be careful when you use above functions. If you need a identity of the current scope in current session, you should use SCOPE_IDENTITY. If you need latest identity of a specified table no matter what is the session then you should use the IDENT_CURRENT

Thursday, December 3, 2009

Thread safe Singleton

I was just wondering if the singleton classes are threadsafe. I found some explanations how to make it threadsafe. Here are my findings

Let’s take an example of singleton class

class MyDocument
private static MyDocument _instance;
protected MyDocument()

public static MyDocument GetInstance()
if (_instance == null)

_instance = new MyDocument();
return _instance;

public string GetData
return "From Singleton class";


Now my above class will work fine. Can you guess what might happen if we use this on multi threading? While one thread is processing on _instance = new MyDocument();
Another thread can be on if (_instance == null) which is still true, so there is chance of getting two instances. So how to resolve this?

Here are some of the steps we can use

Modify your code something like this

class MyDocument
private static MyDocument _instance = new MyDocument();
protected MyDocument()

public static MyDocument GetInstance()

return _instance;

public string GetData
return "From Singleton class";


So now we are not checking for null and returning only the object. One more option that can be used as below

class MyDocument
private static MyDocument _instance;

private static object objectLock = new object();

protected MyDocument()

public static MyDocument GetInstance()
if (_instance == null)
lock (objectLock)
if (_instance == null)

_instance = new MyDocument();
return _instance;

public string GetData
return "From Singleton class";


In the above class, the _instance object creation part is put inside lock.The lock keyword marks a statement block as a critical section by obtaining the mutual-exclusion lock for a given object, executing a statement, and then releasing the lock. For more info on Lock you can check

Friday, November 27, 2009

XLINQ and Lambda Expressions

I had used LINQ recently in one of the projects but I had very less knowledge while developing it. But I am sure it is one of the very good add on for the programming which makes many of our life so easy especially while working with xml and DB. Most of the places I had used the LINQ query but I just tried using the same thing using the Lambda expressions.

If you are new to XLINQ, you might want to go through xlinq first. I take an example as below and explain how to obtain the same results using XLINQ and Lambda expressions.

My xml
string xmldoc = "<customer><item id=\"1\" status=\"N\" name=\"Item 1\"><item id=\"2\" status=\"O\" name="Item 2\"><item id=\"3\" status=\"O\" name=\"an item\"></customer>";

Loading xml to Xelement
XElement objElement = XElement.Parse(xmldoc);

Ok now my xml is loaded and ready to use. Lets say if I need to take the items with Status “O” and order by Name, if we have to do it using XMLDocument(in earlier version) it would require many lines of code and debugging. The LINQ made it very easy. I would simply query as below

IEnumerable oldItem = (from c in objElement.Elements("Item")
where (string)c.Attribute("Status") == "O"
orderby c.Attribute("Name").Value
select c);

The oldItem list will have all the items with Status “O” order by “Name”. Just parse through oldItem and you will know it for sure

foreach (XElement obj in oldItem)

Now let’s do the same thing using lambda expression

IEnumerable oldItemLamda = objElement.Elements("Item").Where(d => d.Attribute("Status").Value == "O").OrderBy(d => d.Attribute("Name").Value);

It looks pretty straitght forward while the above looks like someone needs to have query skill. The expression d => d.Attribute("Status").Value == "O" is the predicate. Have you observed the new operator used here ? "=>" is called as Lambda operator which is introduced in .net 3.0. The expression on right side is called lambda expression. An expression lambda returns the result of the expression and takes the following basic form:

(input parameters) => expression

So we have the Xelement filter with “O” status and Order by name. Lets make sure we have the right elements selected

foreach (XElement obj in oldItemLamda)

The results from the above query and the lambda expression are same.

There are many methods available which expects the predicate and returns the result based on the expression.

You can get more information on Lambda expression on

Thursday, November 26, 2009

Working with Object Oriented Javascript

There are many situations we use javascript on web application. Most of us use it for client side validation or to load some dynamic content on client side. There are many things we can achieve using javascript. Here I thought of explaining some basics of object oriented approach to work with javascript.

First thing you shoud keep in mind is that object oriented in javascript is not like any other progamming languages like C#, Java, C++ where we are referring to instances of classes or structs. There is nothing like creating class on javascript. We can assume that its a collection of name value pairs or dictionary with string keys. Simple way to express that as we create a function and assign that to a variable. Here is how the sample Javascript class file will look like


function Myclass(){
this.myPublicvar = '';
var method1 = function()
if(arguments.length > 0){

alert("parameter length" + arguments.length);
alert("inside the method " + this.myPublicvar);

this.mypublicMethod = method1;

function alertme(){
var myObj = new Myclass();
myObj.myPublicvar = 'Sample';

function testoverloading()
var myObj = new Myclass();
myObj.mypublicMethod(1,'Test parameter');


<input onclick="alertme()" value="Click here" type="button">
<input onclick="testoverloading()" value="Try Overloading" type="button">

In above case, we have a function created as Myclass which will act as a class. There is a method defined inside Myclass method1. The method1 is assigned to a public variable myPublicMethod.

When you create a object of Myclass, you can invoke the method by using myObj.mypublicMethod(); As you can see in the above example it has one of the public variable defined myPublicvar. Now you must have understood the use of "this" keyword inside the function Myclass.

We can also achieve function overloading. In above scenario, we have one more button “Try Overloading” which will invoke a method testoverloading(). This will show how the overloading works. One of the feature of javascript that makes it very flexible that,we can pass any number of parameters and we can get them as parameter array. You can find the same in above example. The same public method mypublicMethod is invoked with different parameter. The alert will show the number of parameter passed.

When you are working with complicated logic on client side this will help us a lot. Hope this piece of information will give some basic idea on object oriented approach on javascript programming.

You can get details on

Wednesday, November 25, 2009

Foreign key constraints and Delete records

In many cases, while designing the database, we consider primary key and foreign key. Some of the cases, the data will be never deleted form the system and we use only soft delete which is nothing but keeping another column to indicate whether the row is active or not.

But some of the cases, we might want to delete the record but it will also have the reference in other table.

I will take an example of a table customer and items and itemlocation

create table itemlocation


locationid int primary key,

locationdesc varchar(100)


create table Items


ItemID int primary key,

ItemName varchar(100),

ItemPrice int,

locationid int constraint Item_location_FK references itemlocation(locationid)


create table customer


CustID int primary key,

CustomerName varchar(100),

ItemID int constraint Cust_Item_FK references Items(ItemID)


insert into itemlocation (locationid,locationdesc) values (1, 'Bangalore')

insert into itemlocation (locationid,locationdesc) values (2, 'Mysore')

insert into Items (ItemID, ItemName,ItemPrice,locationid ) values (1,'T shirt', 100, 1)

insert into Items (ItemID, ItemName,ItemPrice,locationid ) values (2,'Shoes', 500, 1)

insert into Items (ItemID, ItemName,ItemPrice,locationid ) values (3,'Shirt', 500, 2)

insert into customer(CustID, CustomerName, ItemID) values (1,'Subbu',null)

insert into customer(CustID, CustomerName, ItemID) values (2,'Ravi',1)

insert into customer(CustID, CustomerName, ItemID) values (3,'Raj',2)

insert into customer(CustID, CustomerName, ItemID) values (4,'Sam',3)

Now we have all the tables and data. If anyone has to delete the item, they need to delete referring customers first otherwise system will throw an error

delete from Items where ItemID=1

Msg 547, Level 16, State 0, Line 1

The DELETE statement conflicted with the REFERENCE constraint "Cust_Item_FK". The conflict occurred in database "ReportServer", table "dbo.customer", column 'ItemID'.

The statement has been terminated.

In some cases we can use delete cascade which will not throw this error and delete all referring records too. Here is what happens when we use delete cascade in our case

alter table customer drop constraint Cust_Item_FK

alter table customer add constraint Cust_Item_FK

foreign key (ItemID) references Items(ItemID)on delete cascade


Ok. Now you delete the same item and see what happens

Delete executed fine without any errors but you have also lost the customer record. If you look at customer table, the customer with ItemId 1 is deleted.

Now try the delete cascade on item table as below

alter table Items drop constraint Item_location_FK

alter table Items add constraint Item_location_FK

foreign key (locationid) references itemlocation(locationid)on delete cascade


After the table altered execute below statement

delete from itemlocation where locationid=1

Now see what happens, You have lost all the items and customers linked with location 1. We need to be very careful when we using delete cascade. All the data are lost.

To over come this problem we can use a new feature introduced in Sql 2005 and above on delete Set Null option.

Lets change our constraint something like this

alter table customer drop constraint Cust_Item_FK

alter table customer add constraint Cust_Item_FK

foreign key (ItemID) references Items(ItemID) on delete set null

Now lets try the delete statement as below

delete from Items where ItemID=1

Now the cstomer table has data as below

CustID CustomerName ItemID

----------- --------------- -----------

1 Subbu NULL

2 Ravi NULL

3 Raj 2

4 Sam 3

The Itemid which is deleted is affected on customer table but the customer is assigned with Null for the deleted item. We can also use on delete set default which will update default value while deleting referenced data.

This is one of the very good feature added on Sql which will definitely help many of us while deleting referenced data. So be careful to add this when you use delete cascade next time.

Friday, October 30, 2009

Altering table on Sql 2008

When I started working on Sql 2008, whenever I modify table using Management studio, it was throwing an error "Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created". I was little surprised since this is kind of basic functionality and what am I missing to modify the table.

After a while I found that there is a new setting added which needs to be disabled. Here is the steps to get this work

Go to Options

Go to Designers node deselect "Prevent saving changes that require table recreation"Once you save this setting, the modify table will work fine. Just thought it would help someone who is going to start using sql 2008 and dont have to run around this problem :-)

Wednesday, October 28, 2009

Dynamic Queries with SQL

There has been many situations we had to use dynamic queries. There are different ways to write a dynamic queries. I would like to point out some of the advantages and disadvantages of using dynamic queries.

Most of the time the common mistake using dynamic queries is that we use EXEC sp_executesql and pass the query directly by replacing the values. This works fine but if we have any parameters that we need to use in query will be passed as part of the query and not as a parameter. This would internally keep seperate cache in sql server each time when you run the queries.

EXEC sp_executesql N'SELECT * FROM employees e where e.Employeeid=100'

This can be fine tuned as below

EXEC sp_executesql N'SELECT * FROM employees e where e.Employeeid = @id', N'@id int',@id=100

The sql will keep the query on canche and when the query invioked next time same execution plan will be used. In the earlier case, the sql will create seperate execution plan each time we run the query.

One of the other disadvantage using dynamic queries without passing the values parameters is that it might lead to SQL Injection. When the values are appended with sql and passed to sp_executesql it can lead to injection. Lets take an example with the above query

if the @id is passed to sp as "0 or 1=1" then the dynamic sql after replacing will be

EXEC sp_executesql N'SELECT * from employees e where e.employeeid =0 or 1=1'

This will return all the employees. This will be a high chance when we pass the parameter string. In the case of int it may throw error while passing value to sp itself. But all these can be avoided if we use parameterized dynamic sql.

I found a detailed article on dynamic sql

Strange Problem with Cold Fusion

I get to work on Cold fusion sometimes. I just wanted to state a strange problem we had faced recently. This was not the case since the day it was set up. The service started sending duplicate emails whenever a email is sent from cold fusion code all of a sudden.

Not all emails were duplicating but some of them did. While researching on the issue, from the log found that multiuple threads trying to send the same email

The log entry was like this

"Information","scheduler-1","10/27/09","16:05:20",,"Mail: 'New request in Queue'
From:'' To:'' was successfully sent using [IP]"

"Information","scheduler-3","10/27/09","16:05:20",,"Mail: 'New request in Queue' From:'' To:'' was successfully sent using [IP]"

One of the setting on cold fusion administrator site allows us to set maximum delivery threads. By default this will be set to 10. Just updated this to 1 and monitored for sometimes. This seems to be fixed the problem. Strange bug!!!!


Tuesday, October 20, 2009

Loading data as XML from DB

In most of the applications, the data will be loaded as Dataset or datareader from Sql server from the back end DB. If we are going to look at service oriented approach, the services normally return xml. Most of the time we get data from SQl and convert to xml and send it. We could get the data directly from Db itself.

From the stored procedure here is my select statement

This statement selects the data from Department table which has columns DeptID,DeptName,IsActive

DeptID AS [Department!1!DeptID],
ISNULL(DeptName, '') AS [Department!1!DeptName],
FROM Department (NOLOCK)
WHERE IsActive = 'Y'

The first two columns Tag and PARENT are meta coulmns. for more details on these tags you can check

The xml returned would be

<Department> < DeptID="1" DeptName="Administration"/> < /Department >

Loading the Xml Data using C#

SqlConnection dbConn = new SqlConnection(connectionstring);
dbCommand.Connection = dbConn;

XmlReader dbReader;
dbReader = dbCommand.ExecuteXmlReader();

StringBuilder str = new StringBuilder();

while (dbReader.ReadState != ReadState.EndOfFile);
return str.ToString();

Above statement will return the xml data returned from DB as a string format which can be returned from service directly.

But it is always a good practice to bind the data returned from DB to a schema for the datatype check and perorm the validation. The above method returns xml but does not have any infromation on the schema of the data. So this may not be suitable in all the situations.

Monday, October 19, 2009

ARITHABORT and Xml Datatype on SQL

While using XML datatype on Sql 2005 or 2008, I got into a strange problem. The sql queries used work perfectly when I run it on query analyzer but it used to fail when I put that on a stored procedure. The sql was using xml datatype and it was performing some xml operations.

After researching, I found that if we use xml datatype, we need to set ARITHABORT ON.
This is the requirement as stated here

"Currently, there is a reqiurement that ARITHABORT must be ON when you use XML methods and XQuery. However, as long as the compatibility level is >= 90,ANSI_WARNINGS on implies ARITHABORT ON, so this check is not needed. Many client APIs connect by default with this setting off, but ANSI_WARNINGS on,so users need to take extra precautions to deal with this and it can causeconfusion and misery. For instance, I recently encountered a case where someonerun into his Agent job failing because of this"

So make a point to set ARITHABORT ON whenever you use xml datatype on your stored procedures.

Sunday, October 11, 2009

Accessing wsHttp WCF service from outside domain

The wsHttp binding of WCF service uses windows authentication, so using the service outside the domain is not a straight forward method.

Well, I had the WCF services created and deployed on my app server when I tried accessing it from other domain, it started throwing security exception, that's when I came to know the actual problem. The client credentials were not set when accessing it from the outside.

The easy way I found and implemented by keeping a service account which is a domain user. I have kept that user details in my configuration which is encrypted on client side and used as below

AdminService.AdminServiceClient objSvc = new AdminService.AdminServiceClient();

objSvc.Endpoint.Address = new System.ServiceModel.EndpointAddress(endpointaddress);
objSvc.ClientCredentials.Windows.ClientCredential.UserName = username;
objSvc.ClientCredentials.Windows.ClientCredential.Password = password;
objSvc.ClientCredentials.Windows.ClientCredential.Domain = domain;

While creating the wcf service client, assign username, password and domain which belongs to domain. After updating the clientccredentials, the client started working fine.

Saturday, October 10, 2009

Configuring Identity IIS Impersonation for WCF

One of the common scenarios on most of the application would be accessing network shared folder for any files. With WCF, I had little problem figuring out how to access the network path since the IIS work thread is running under network services. The Impersonation would not work for WCF.

I was using wsHttp binding and I have used a domain user to impersonate all the calls from the client side, even then the impersonation was not in effect. One of the change I tried on IIS was by changing identity of Application pool. For this I have created new app pool for my application. On the application pool simply change the identity to domain user account.

When changing the application pool identity to domain account, I found a problem, the application pool was getting shut down.When I check the event viewer, it was logged as

The identity of application pool, 'MyServices' is invalid. If it remains invalid when the first request for the application pool is processed, the application pool will be disabled. The data field contains the error number.

For more information, see Help and Support Center at

and on the security tab there was one more log
Event Type: Failure Audit
Event Source: Security
Event Category: Logon/Logoff
Event ID: 534
Reason: The user has not been granted the requested
logon type at this machine

When I searched for these errors, I got to know that the domain user which is configured for Identity should be added as IIS_WPG users group on the local group. Once the user is added to group, everything started working fine.


Sunday, June 7, 2009

Maintain Type using typed dataset..

Most of the applications when we need to deal with the datatypes for the values, we use class objects and pass it on to different layers. But if we need to handle this with XML, then we need to use typed dataset.
I have used a WCF service which will return the xml string as string to the client, which makes it compatible for any application to consume. The client need to know the datatype. The typed dataset helped me to get the datatypes of the xml returned from the service.
creating a typed dataset
public class MyDataSet : DataSet
public MyDataSet()
private void BuildTable()
DataTable table;

DataColumnCollection columns;
DataColumn dtCol;
this.DataSetName = "MyDataset";
table = new DataTable("Table");

columns = table.Columns;
dtCol = new DataColumn("col1", typeof(System.Char));

dtCol.ColumnMapping = MappingType.Attribute;

dtCol = new DataColumn("col2", typeof(System.Int16));
dtCol.ColumnMapping = MappingType.Attribute;

When we load the data from database to above dataset, the data will be tied with the datatype. When we get the xml from the dataset, this will return in below format

The same xml can be loaded to dataset as below

MyDataSet MDataSet = new MyDataSet();
MDataSet .ReadXml(new StringReader(xmlData));

The dataset will be loaded with the xml and retain the datatype which can be used on the client side. If the xml is loaded with Dataset object, the datatype will not be recognized and the data will be loaded as varchar.

Setting your own Banner for the installer...

When we create Set up project, by default it shows some banner.We can change the banner to custom image.

Here are the steps to follow

Right Click on the project -> View -> User Interface

Now select the installer form, go to properties

Select your image as BannerBitmap. This image will be shown on the installer.

Monday, April 27, 2009

SQL Service Broker on Sql 2005 or 2008

I have been working on Sql Service broker since last few days. I have heard lot about it but I have never tried it before. When I started working on it, I felt it is very simple and easy to use.

I could use it in my application which enabled easy implementation of asynchronous programming. Initially I have tried using it on Sql 2005 and then later used it on Sql 2008. Sql 2008 has not much enhancements on this feauture but has fixes which I had faced on SQl 2005. I had very tough time getting service broker to work after restoring the database but this was very straight forward on Sql 2008.

We need to enable Service broker on the database where we are planning to use it
This may require Server restart sometimes because this statement takes lot of time if the DB is in use and sometimes it may fail. After enabling broker service, you need to create master key encryption


The session key is used to perform the conversation and to secure the session key, the encryption is used.

Once the encryption key is set, lets start setting up the service broker

Create message type


Create Contract

Create Queue

Note: The stored procedure OnReceiveSaveMessage will be activated when there is a message received on ReceiveSaveQueue. I would suggest to add the activation once you test the stored procedure. It is very difficult to debug the stored procedure if there is any error. And if the stored procedure fails more than the count specified on MAX_QUEUE_READERS, the Queue will get disabled.

Reading the records form Queu
select * from ReceiveSaveQueue

Creating the service

CREATE SERVICE [ReceiveSaveService] AUTHORIZATION [dbo] ON QUEUE [dbo].[ReceiveItemSaveQueue] ([ItemContract])