World of Kamsy

Loosing can become a painful habit

2011-08-16T11:05:00.000+05:30

I was just watching our(Sri Lankan) Cricket team under performing in back to back ODI's. It was really painful to watch. Cant believe this was the team went so close to win the World cup few months back. It was more painful to watch how Sanga, Mathews struggle to just hit the ball to a gap and grab a single. Just simply everything is wrong.

No big expectations guys just score more than 250 in next three matches that's enough.

Stored Procedures

2011-03-15T17:23:00.001+05:30

I was surprised, however, when a Does Not Compete happened during an interview for a database developer position. My question was fairly straightforward: tell me about your experience with stored procedures.

“I’m very familiar actually,” the candidate answered, “I’m currently working at Home Depot because I’ve had a hard time finding developer work, and everything there has a procedure, and there are so many forms to fill out. If you want to use the forklift to help a customer, you have to get it approved by your supervisor.”

I wasn’t sure how to respond, and that was OK. He just kept going, and going and going, describing in painful detail what his day-to-day duties were. Finally, he finished with “and that’s my experience with Store Procedure.”

The interview didn’t go very far after that.

http://thedailywtf.com/

Microsoft Dynamics CRM 4 - How to change a custom attribute's datatype

2010-08-10T14:06:00.004+05:30

Yesterday I was trying to import a customization from one CRM 4 environment to another. There I faced an issue with same attribute being duplicated in both environments with two different data types. Due to a still unknown technical issue we can not delete any attributes from these environments. So the only option for me was to change the data type of the attribute in one environment to equal the both the data types. As the CRM 4 does not allow to change custom attribute types from the interface I had to do this from the back end (database).

Below is the query I use to do this;

UPDATE attribute
SET
attributetypeid = GUID of the data type
WHERE name = attributename

You can find the data type guid's from attributetypes table.

PS : You will also have to change the datatype of the perticular column in the perticular table manually.

How to make your PDF files speak in Acrobat Reader

2010-01-12T14:45:00.002+05:30

If you ever wondered how to make your PDF files speak, there is a very simple way of doing this. This has been there since the version 6.0 of Acrobat Reader.

First you have to press "Shift + Ctrl + V", this will enable Read Out Loud function in acrobat reader. Now you will see Read Out Loud menu item under View menu. In this you can select the reading options, alternativly you can start/stop reading using following key combination;

Shift + Ctrl + B- Read the whole document
Shift + Ctrl + V - Read the current page only
Shift + Ctrl + C- Pause
Shift + Ctrl + E- Stop

The voice will be your choosen voice in the text to speach section in the control panel. You can download alternative voices from here for free .

This is really cool and a very helpful feature.

PermLink

MS CRM 4 Form Printing - "This form has been changed and must be saved before printing"

2010-01-08T11:05:00.002+05:30

I have come across an issue with Printing a CRM 4 form today. The messege "This form has been changed and must be saved before printing" appeared when I tried to print a form even though the form data is already saved.
After digging into the problem I found that this perticular form had two custom controls (textbox and a command button), because of this CRM was assuming this form has unsaved (isdirty true) data.
There is a workaround to solve this issue by changing the printing javascript in the main form script file.
First you have to log in to the CRM applicaiton server and go to the following location;
":\inetpub\wwwroot\_static\_forms\controls"
In this location you can find a file named "form.crm.htc". Open this file and find the print function which should look like this;

function Print()
{
if (isDirty())
{
alert(LOCID_FORM_PRINT_DIRTY_MESSAGE);
}
else
{
var sId = _oSubmitForm.crmFormSubmitId.value;
openStdWin(prependOrgName("/_forms/print/print.aspx?objectType=" + _oSubmitForm.crmFormSubmitObjectType.value + "&id=" + sId + "&title=" + CrmEncodeDecode.CrmUrlEncode(parent.document.title)), "print" + buildWinName(sId));
}
}

You can edit the above function to skip the isdirty check for the perticular entity which has custom controls. This will bypass the above mentioned messege and will go directly to printing. Change would be to the if condition for isdirty checking which should be the following;

if (isDirty() && _oSubmitForm.crmFormSubmitObjectType.value != 112)

112 is the objecttypecode for Case, you can change this to your disered entities objecttypecode. Hope this will help you, please drop a comment if you have any issues.

Permlink

Happy New Year 2010!

2009-12-31T23:07:00.002+05:30

TSQL – Retrieve only numbers in a string

2009-11-27T18:05:00.001+05:30

With related to my earlier post which demonstrated how to retrieve only numbers in a string using C#, this is how you can do it using TSQL in SQL Server. This will be also become handy if you want to do this in a backend process (SP, Trigger, etc). This is a UDF which you can reuse in your TSQL codes.

USE [DBNAME]
GO
/****** Object: UserDefinedFunction [dbo].[fn_filternumbers] Script Date: 11/27/2009 17:59:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_filternumbers]
(
@Src nvarchar(255)
)
RETURNS nvarchar(255)
AS
BEGIN

declare @Res nvarchar(255)
declare @i int, @l int, @c char
select @i=1, @l=len(@Src)
SET @Res = ''
while @i<=@l
begin
   set @c=upper(substring(@Src,@i,1))
    IF @c IN ('0','1','2','3','4','5','6','7','8','9')
        SET @Res = @Res + @c
   set @i=@i+1
end

return(@res)
END

You can use this function in the following manner;

select telephone, dbo.fn_filternumbers(telephone) from users

This will return telephone numbers by removing white spaces, characters and will only return numbers. (i.e +94 (475) 849 984 would return 94475849984)

Please feel free to drop a comment if you have any question on this.

PermaLink

C# – Retrieve only numbers in a string

2009-11-25T18:23:00.001+05:30

I wanted to retrieve only the number in a string to clean up some telephone numbers users have entered in a system today. So I came across with this nice Regular Expression it is really simple and most importantly it was the fastest method I could found. Here is the code;

public String returnonlyNumbers(String stringValue)
{
return string.Join(null, System.Text.RegularExpressions.Regex.Split(stringValue, "[^\\d]"));
}

The above code would return only the numbers in any given string. For example if you pass the string “+94 (084) 849748 489748” to this method it would return “94084849748489748”.

Hope someone would find this beneficial.

PermaLink

Ravana the Great on the Canvas

2009-11-23T16:25:00.004+05:30

These are some paintings of King Ravana by Dhammika Ravindra Dissanayaka. The history has been very rude on King Ravana, but Dhammika has seen him in another dimension. It is really a great work since we have very few texts about the costumes of that time. Here are few paintings from his Exhibition which was concluded in Lionel Wendt on 22nd November 2009.

PermaLink

Time Travel on the Web

2009-11-23T15:16:00.001+05:30

Time travelling is one of the most fascinating concepts in the world of science fictions. Is time travel possible in the real world? This is a question which most of us would have asked from our selves at least once in our life. Anyway it will be a question without a proper answer for another few decades or more.

At least it has become a reality for the web. Yes you can do travel through time in the web. For a example if you want to go to yahoo.com as it was in 1998, it is really possible with the Way Back Machine. The internet archive a non-profit organization is having the worlds biggest digital library of web archives. Apart from the way back machine they also do provide various other tools to analyze the web archive for researchers, students and general public for free.

So go ahead and play around with the Way Back Machine http://web.archive.org/collections/web/advanced.html

PermaLink

Download Torrents without a Torrent Client

2009-11-15T19:28:00.002+05:30

Yes it is! You read it correctly, you can now download Torrents as HTTP for free. KickassTorrents is one of the fastest growing Torrent search engines offers Http Torrent downloads for free. Apart from this magnificent feature this site is very user friendly and most importantly it is simple.
Hope most of you will enjoy this Torrent Search Engine in many more months to come.

When the Go gets Going (Google Go)

2009-11-12T22:05:00.005+05:30

Google the search engine giant has introduced its newest programing language called "Go". This is the second programing language Google has released in the recent past. In july this year they released the language called Simple.

Go is a system language like C, C++ but as they claim it is an extremely fast for development like Python. Go works with Google's open-source technology Native Client, designed for running native code in web-based applications, but it is not known yet whether Go will be used in the new Google operating system, Chrome.

Here is the URL for Go language's official website http://www.golang.org. You will find tutorials, samples, compiler downloads and many more in this site.

Here is a video showing Go on the Go:

Permalink

MS CRM 4 - Hiding System (Public) Views

2009-11-09T11:15:00.006+05:30

In CRM 3 when we want to hide a System view, we were use to hide it by sharing that view with a team which has no users.

But in CRM 4 Microsoft has removed this functionality of sharing views (Brilliant isn’t it). This leaves us no option to hide unwanted System views using CRM Customization. What is more painful is there is no way we can delete these system views either.

But as always there are two workarounds we will achieve this goal. Out of those two easiest and quickest one is updating the SavedQuerybase table in the MSCRM database. Even though it is not recommended by Microsoft this would be very beneficial when you are in a tight schedule. Now let’s see how we can do this.

First you need to find the particular view which you want to edit from the SavedQuerybase table. For that you can use the following query by changing the "systemviewname" to the particular view name you want to edit.

select * From SavedQuerybase where name like '%systemviewname%'

Then you must update the "isprivate" flag in this table to 1. The default is 0 which means the view is public. For the update you can use the following query. The 'systemviewname' must be changed to the exact name of your view which you could find using the earlier query’s result set (column name is “name”).

update SavedQuerybase
set isprivate = 1
where name = 'systemviewname'

This will remove that particular system view from user’s dropdown list.

Anyway there is another way of achieving this in a proper way by writing a CRM plug-in to change the SavedQuery entity. This needs some coding and of course deployment. Here are two great articles which will walk you through the process;

http://msdynamicscrm-e.blogspot.com/2008/02/hiding-view-in-crm-40-using-plug-in.html
http://mscrmuk.blogspot.com/2008/08/hiding-system-views-in-crm-40.html

If you are interested about learning more about CRM 4 development tricks, I strongly recommend buying following books;

Will Doomsday 2012 be a reality? - 10 Failed Doomsday Prophecies

2009-11-06T21:51:00.002+05:30

The whole world (At least most of us) is wondering about the Doomsday prophecy in 2012. The main reason is the end of Mayan's long calender is due on 21st of December 2012. I am not going to argue on whether it is false or not. Purely because of there are millions of articles on the web on this.

Anyway today I found a very interesting article in www.nationalgeographic.com on 10 failed such propecies in the history. What is more interesting is all these are not from very distance past. Just have a look, you may be able to have some breathing space if you were so scared of being wiped out in 2012.

APOCALYPSE PICTURES: 10 Failed Doomsday Prophecies -From National Geographic

SQL Server - Error Rebuilding Indexes in Online Mode

2009-11-04T11:19:00.006+05:30

In transactional Databases it is a must to have an index rebuilding job running frequently. Fragmentation of indexes is the major cause for any database driven system to perform slower or to crash. As modern database driven application's (Specially Web) are operated 24/7, it demands us to do this operation while the indexes are online.

But SQL Server does not support this operation if you have any LOB data type (text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml) column in your indexes. And this is the part of the error message your getting from SQL Server IDE;

"Online index operation cannot be performed for index 'cndx_PrimaryKey_Account' because the index contains column 'Description' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index,..."

For Clustered indexes if any of the fields in your underling table is in a LOB data type you can not rebuild that index in online mode.

For other indexes if any of the include columns in the index is in a LOB data type you can not rebuild the index in online mode.

In these cases you will have to rebuilt that particular index offline.

Apart from these two common causes following causes are also possible for failing Online Rebuild of an index;

1. XML index.
2. Index on a local temp table.
3. Initial unique clustered index on a view.
4. Disabled clustered indexes.

Please feel free to drop a comment if you have any questions or suggestions.

Sri Lankan Cricket Team's Indian Tour Schedule

2009-10-21T18:22:00.002+05:30

Schedule:

Nov 8: Sri Lanka Team arrives at Mumbai.
Nov 11-13: 3-day Match at Ahmedabad Vs Board President`s XI.

Nov 16-20: First Test at Ahmedabad.
Nov 24-28: Second Test at Kanpur.
Dec 2-6: Third Test at Mumbai.

Dec 9: 1st Twenty20 at Mohali (D/N)
Dec 12 : 2nd Twenty20 Match in Nagpur (D/N).

Dec 15: 1st ODI against India at Rajkot.
Dec 18: 2nd ODI against India at Vizag (D/N).
Dec 21: 3rd ODI Match against India at Cuttack (D/N)
Dec 24: 4th ODI against India in Kolkata.
Dec 27: 5th ODI against India in Delhi (D/N).

Dec 28: Team departs.

Retrive only the date part from a datetime (TSQL)

2009-10-21T11:26:00.006+05:30

This is one of the easiest ways to retrive only the date part from a DateTime value. Of course not some thing I invented but will be useful for others;

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

Reality of the Reality shows in Sri Lanka

2009-10-06T15:22:00.007+05:30

I was watching "Derana Dream Star" on last Sunday (3rd October) and it was a judgment day for 12 contestants. By watching it for sometime I felt very much uncivilized about being a viewer on such a heartless show. The presenter was torchering those 12 young artists by keeping them on the edge for more than 45 minutes. It appeared that their intention was to cover the 1 hour allocated show time rather than just declaring the winners. Also it appeared to me it was like a fun game for the presenter. But sadly in the receiving end of that were those poor contestants and their relatives. They were marketing their tears and gloomy faces in a pathetic way and clearly it crosses the boundaries of a civilised society.

As we all know these shows are being following the footsteps of American Idol. Also some world famous talent shows like Britain's Got Talent. Which I happen to follow while I was in UK during this Summer. And when it comes to declaring winners it was really straight forward, I can remember in the final they did it within a minute. No unwanted dragging and so professional. If we are copying form them why we are not adapting good things, which is a very big question mark for me.

I found that most of the reality shows (there are more than 10 at the moment in different channels) are doing this and it is really ugly. It is time to stop this nonsense game otherwise the day that a person die on one of these stages is not too far.

Britain's Got Talent 2009 Final Results Video
American Idol 2009 Final Results Video

Cricket Commentators, Are they really expert?

2009-09-24T16:28:00.002+05:30

We all know Cricket is a very unpredictable game. But have these so called expert commentators really understand that? Given the fact that most of them have played lot of cricket and matured with the game they should be seeing things beyond our view. Instead they predict things like armatures.

This actually starts with the pre-match discussion. They start by predicting the playing 11's and 8 out of 10 times they are wrong. Then comes the pitch report which is more disappointing. And through out the game they talk crap, One funny incident of such was, in a match between Sri Lanka and England in England, there was fielder placed in the deep point, Ian Botham was the commentator. He was saying " I cant understand why there is a deep point in place at this stage of the game". In the next ball batsman (Can't remember who he was) hit a shot straight to the throat of the deep point. And the poor fellow was wordless.

Before the beginning of T20 worldcup in England this year, Ian Chappel had tournament favourites India, Australia and South Africa. India and Australia were out even without making to the Super Eight. After that his favourites changed and it was South Africa who lost the Semi's to Pakistan. In the final he said it'll be Sri Lanka, which was actually Pakistan.

Now in the Champion trophy is also same thing is happening. The favourites are same teams, and South Africa lost their first game. They should understand Cricket is not predictable and stop being a joke to all the fans. Also they should be talking things that we don't understand about cricket by using their experience rather than trying to be Prophets.

My Wedding Picture 31st August 2009

2009-09-15T10:00:00.003+05:30

Find Modified date of a Stored Procedure

2009-07-10T15:45:00.002+05:30

This will be very helpful since the GUI doesnt show the modified date of a SP in SQL Server.

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = 'spname'

T-Sql NOT IN is not returning expected results in SQL Server

2009-06-30T15:48:00.008+05:30

I ran into this problem several times and when ever I encountered this problem I used to change my query to avoid this. But today I had sometime to dig into the root of this issue. Here are the problem, cause and the solution;

Lets take a simple example where you have a Customer table and a Invoice table (this is my favourite scenario since a POS system was my first ever commercial project). Invoice table has a foreign key to Customer table. Let say you have come across a situation where you want to find out customers who doesn't have any Invoices against them. You would probably write the following T-SQL (There are few other ways you can write this, but I know most of the SQL coders prefer IN and NOT IN);

SELECT customerName FROM customer WHERE customerID NOT IN (SELECT DISTINCT customerID FROM Invoice);

This may return values and sometimes may not. This is something make you feel very unsettling on a busy day. The problem is, this will work as far as customerID column in the Invoice table has no null values. If any single NULL value is there this will not return anything. Just simple as that :) anyway if you remove the nulls from the sub query this will work without a problem.

SELECT customerNAME FROM customer WHERE customerID NOT IN (SELECT DISTINCT customerID FROM Invoice WHERE customerID IS NOT NULL);

Alternatively you can use NOT EXISTS which will handle NULL values in a smarter way. Hope this will help, any question please feel free to ask.

Sanga and the team : We are proud of you guys

2009-06-22T00:27:00.005+05:30

When our cricket team looses, most of the times we used to criticise them. But this T20 World Cup final was one of the rare occasions we feel proud about the team despite the loss. The passion and the spirit the whole team showed through out the tournament was outstanding. There was always someone who stood up and took the responsibility either in batting or bowling. I am really proud of them and whole world would never forget specially how Dilshan, Sanath, Sanga, Mahela, Murali, Mendis, Mathews and Malinga played in this tournament. You all have made Sri Lanka proud. Cheers to all of you guys!!!!

How Indian Cricket fans can show their anger in a peaceful manner

2009-06-18T18:13:00.004+05:30

This is an innovative thinking from Nishi Narayanan from Cricinfo. But as we all know Indian supports are the last in the line to accept these kind of CIVILISED ways.

1. Send an anonymous tip to airport authorities regarding possible swine flu among the Indian team contingent. Getting them quarantined will be much more satisfying than breaking a few windows.

2. Hold a press conference. Have all aggrieved Indian fans stand on a stage behind Bishan Bedi, who will read out a hand-written letter telling the players what a bunch of wusses they are for not staging a walkout to protest the short-ball barrage by England.

3. Don't bawl or cry foul murder when reporters stick their mikes into your faces asking for reactions. Be cool. How do I feel now that India have exited the World Twenty20? A little constipated, but I think that's the Chinese takeout from last night.

4. Threaten to support England in the next tournament in that country. The concept of English grounds being filled by thousands of fans cheering for England is bizarre enough to mess the minds of the Indian players so much that they'll need John Buchanan to explain it to them.

5. Invite the players to parade in an open-top bus through the streets of Mumbai as a we-forgive-you gesture. If they survive the ride in the city's monsoons, organise a function for them to watch their post-match interviews where every "obviously" and "of course" is replaced by the sound of nails on a chalkboard.

6. Spread a rumour through the press that Shahrukh Khan plans to buy the whole team for his franchise. If the thought of playing for Kolkata Knight Riders doesn't depress them, the fear of fakeiplplayer leaking all their dirty secrets will surely get to them.

7. And finally, do as Gandhi would. Boycott products endorsed by cricketers. Don't buy shampoos, soaps, cellphones, colas, water, satellite TV, motorbikes, cars, clothes, electricity, chocolate, chips, air conditioners and refrigerators. That leaves books and maybe air. A refreshing and uplifting lifestyle. The Mahatma would approve.

LINK

BBC and Mahinda Rajapakshe

2009-06-04T21:20:00.003+05:30

I was reading the news in BBC website on yesterday's (03rd June 2009) celebrations in Colombo. It really made me feel better, purly because the tone that artical was written. It makes the sound of a nation who are really hurt. Mahinda you ROCKS. Keep it up!

http://news.bbc.co.uk/1/hi/world/south_asia/8080626.stm

Why Prabakaran's dead body is the key to end the War

2009-05-16T15:07:00.010+05:30

I personally was never a believer of the concept of "Every Tamil is a LTTE supporter". There are so many examples I could gather from Tamil friends I had and I have in Sri Lanka. I have now been in UK for 2 weeks and my belief has totally changed. I met few of my tamil friends, one of them is a very known one from the school times. We used to talk about LTTE, Elam and the tamil sinhala issue from those days. I always have put him among my POSITIVE examples to not to say EVERY TAMIL IS A LTTE SUPPORTER. I spent few hours with him talking about the current situation. I couldn't believe my ears that I am hearing all those stuff from him. He said this is never going to be over and he quoted a lot of examples such as "where are all these air crafts, weapons, ships" etc. From my heart I knew that he was trying to pretend this is not over. Anyway I was shocked by his change over, he believes they can start it all over and from his words I would suggest he will personally commit to that.

I was so upside down and couldn't believe how he is changed. He was here for more than 5 years and either that changed him or he kept his all the thoughts to him self and showed a different face in Sri Lanka.

Lets say it is an isolated case, I talked to two of other tamil friends of mine who always used to say Prabakharan is a killer, and there is no real issue for Tamils in Sri Lanka. But now they believe Prabakaran brought hope to sri lankan tamils to fight the struggles they faced from Sinhalase.

So is every tamil in Sri Lanka pretending that they hate LTTE? Do they like them secretly somewhere from inside their conscious? Given a chance to open up their own view in another country will they actullay support them? I am not sure about this, but I was so sure it is not when I was in Sri Lanka. But now I am totally confused.

After experiencing all these I had little bit of thinking on this. Then I thought what would happen if the SLA could not find Prabakaran, at least his dead body. It will add an inspirational mystery to his story like Hitlor's. Remaining leaders can easily use this to motivate others to say he is still alive and operating from somewhere. This will give HOPE to all the ealamist all over the world.I would suggest there are nearly half a million of them all around the world, may be more than that. If he manage to either escape or to destroy his body all the bloodshed our forces did will be in vain. There will be another VERSION of this again for sure. What I belive is finding Prabakaran Dead or Alive is the winning factor of this war.

Facebook, the modern age Detective Service

2009-03-31T10:29:00.006+05:30

This is a very interesting news which I read today. A boyfriend of a rape victim has started Facebook group called "Find the Sale rapist", where he is asking help from the Facebook community to help the police to identify the suspected rapist. He has included some CCTV images in this group. This is what he has to say about this

"I WANT THIS "SUSPECT" TRIED AND CONVICTED FOR WHAT HE DID TO MY FIANCEE AND TO STOP IT EVER HAPPENING TO ANYONE ELSETHIS "SUSPECT" IS INNOCENT UNTIL PROVEN GUILTY AT TRIAL A TRIAL I WANT TO HAPPEN!"

This is a very interesting move and apparently the first time the social networking site has been employed to identify a criminal.

And what is more interesting is the way Technology is contributing to a better society. Lets wish him best of luck.

Facebook group link

Read the full story here

Enable Remote Connections in SQL Server 2008

2009-03-20T11:05:00.009+05:30

Another annoying change to SQL Server 2008 tool set. I don’t know why Microsoft keeps shuffling things here and there. One basic principle of Software Engineering is the Consistency, and that is what never there with Microsoft Products. Every time they release a new version of any product, rather than learning new things users always have to spend lot of time learning how to do things they used to do with the earlier version.

Anyway I am writing this post to save others time. "SQL Server Surface Area Configuration Tool" the one shipped with SQL 2005 is no longer available with 2008. If you want to enable remote connections for your SQL Server 2008 after the installation you have to follow these steps.
1. Right click on the SQL Server 10.0 instance and go to properties, then go to connections tab. In that you have to check the box "Allow remote connections to this server" (See the screenshot). Optionally you can set the remote query timeout property which is in seconds. Click Ok button to save changes and close the window.

2. Then you have to open TCP/IP connections to your SQL Server. To do this you have to use the "SQL Server Configuration Manager" tool. You can open this tool by either typing "SQLServerManager10.msc" as a Run command or through the Programs menu as follows
SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager
In the right hand side click on
"SQL Server Network Configuration" -> "Protocols for MSSQLSERVER" -> Right click on TCP/IP in the right hand side list -> Select Enable.
Now you will have to restart the SQL Server service.

3. You can restart the service by either going into the Services in the control panel or from the SQL Server Configuration Manager screen itself. Following screen shows how to do it from the Configuration Manger tool. In the services you have to restart the service named "SQL Server (MSSQLSERVER)" service.

4. Now you may be able to access your SQL Server from a remote client or application if you are using Windows XP or a Server version. But if you are using Windows Vista you may still not be able to access your server if the Windows Firewall is enabled. To solve this issue, you have to enable SQL Server TCP/IP port which is 1433 in the windows firewall. To do this go to;
Control Panel -> Windows Firewall -> Click on Change Settings hyperlink -> Exceptions Tab -> Add Port -> Give any meaningful name for Name textbox -> In to the Port Number textbox 1433 -> Protocol should remain as TCP/IP -> Click OK.
It's recommended to restart the firewall.
These steps should solve your issue with remote connections.

Enable FileStreaming in SQL Server 2008

2009-03-16T14:28:00.003+05:30

I was trying to restore a database to a SQL Server 2008, and I got this error

System.Data.SqlClient.SqlError: FILESTREAM feature is disabled. (Microsoft.SqlServer.Smo)

Was bit confused, and after digging it out I Found that under default configuration of SQL Server 2008, FILESTREAM feature is disabled. Microsoft always has these kind of SMART default configurations with new releases. If you can remember SQL Server 2005 came with disabled Remote Connections in the first release. Then they enable it with the built in SP1 setup release. Anyway this is how you can make it enable,

Using the following TSQL is the easiest way,

EXEC sp_configure 'filestream_access_level', '2'
RECONFIGURE

0= Disabled (which is the default)
1= Enables FILESTREAM for Transact-SQL access
2 = Enables FILESTREAM for Transact-SQL and Win32 streaming access

Also you can use SQL Server Configuration Manager to do this,

1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the list of services, right-click SQL Server Services, and then click Open.
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4. Right-click the instance, and then click Properties.
5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
6. Select the Enable FILESTREAM for Transact-SQL access check box.
7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
9. Click Apply.

This will sort out all of your problems with file handling which you get under default configuration of the SQL Server 2008. Anyway later I found that we can change this default configuration at the time of the installation.

C# code to return last day of the month

2009-02-27T16:12:00.004+05:30

Sure this will be important one day for all the coders out there. A simple way of returning the last day of the current month;

1. public int getLastDateoftheMonth()
2. {
3. DateTime firstDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
4. DateTime lastDayOfMonth = firstDay.AddMonths(1).AddTicks(-1);
5. return lastDayOfMonth.Day;
6. }

Pretty simple approach, but sure will save lot of thinking time. Hope it'll be useful to someone.

On the way to work

2009-02-26T09:55:00.004+05:30

It was a just another working day. I got into a 122 bus from Maharagama and everything was quite normal till the bus came in to Wijerama Junction.

At Wijerama two people from a bus which was coming behind came in to our bus and started a argument with the Conductor of the bus which I was in. It has something to do with the regular races they have. It got little hotter and started a fight. Boy it was disgusting people in the bus were shocked. The conductor of the bus which I was in had a good share of hits from those two fellows, apparently those two were passenger (may be regular) of that bus. There was a Buddhist monk in our bus as well, he was really helpless together with all the other passengers.

Then again bus started to move and while the buses are going parallel they started scolding at each other and also throwing things between the buses. We, passengers were helpless and the fighter never gave a damn about us. It was a typical fast moving fighting scene from a movie, and I was living inside the movie. Again near a police station one of the fighters from the other bus came in to our bus and started a fight again, I rushed in to the police station and complained about the incident to the Police man who was at the gate. Understandingly he couldn't come because his priority was to guard the gate. And there was another policeman who is just coming to work, and he came with me. But he had no intention of cooling down the
situation, even infront of him the both parties were scolding at each other in filth. Then again buses started to move while the conductor of our bus calling to all of his friends for help using his mobile to kill his revivals. That is technology at its best use. And at last I managed to reach my destination.

It was like being in a Hindi movie. It simply had everything heroes, villains, action, adventure, jokers and little romance from the couple sitting in front of me. At the end I thought what a eventfull country this is, where else you can have such a eventfull way to work. I simply proud to be a Sri Lankan.

Why Mahela's Resignation is good for Sri Lankan Cricket

2009-02-24T17:09:00.007+05:30

Aftermath of Mahela's resignation from the Cricket Captaincy is not something I am surprised of, specially the media hype. What is most hilarious factor is same media people who criticized Mahela when he fails are now saying he should remain as the Captain. But this is not something new for Sri Lanka as we experienced these type of things with Arjuna and Marvan in the past.

Mahela is my favourite batsman, also the most gifted and talented batsman Sri Lanka produced after Aravinda de silva. The inning he played to see Sri Lanka trough against England in Australia was one of the best for me. Also the master piece of 2007 World Cup semi final against New Zealand. He has given lot to Sri Lankan Cricket there is no doubts about that. As a captain he has brought a lot for SLC as well. He has transformed a team which had lot of potential but less success in to a winning team in both forms of the game. Apart from that he has brought new culture in to the team which has ultimately provided us a winning team.

But it is really the time for him to quit the job. As Jayasooriya did in the past which ultimately gave the birth to two most successful captains Marvan and Mahela. Mahela is a very smart person who knows exactly what he is doing in and off the field. There are lots of talks about the political pressure and stuff. Regardless of those things if we concentrate on Cricket, Sri Lankan team really needs a change here. Mahela's era should end and team should start fresh for 2011 World Cup. If he delayed his call as it would have being a desaster for SLC which he understood perfectly. He knew if the team continue to loose more matches and if he continue his bad period with the bat, his farewell will be very much painful. Also the selectors will have to find a Captain in a rush and will not have time to build a new team. Now, even we loss few more matches we can take it as the transition period and build up. And also in Mahela's perspective this will give him time to comeback as a ODI batsman by only concentrating on his batting.

So hats off to this talented stroke player and one of the best captains we had, for not being selfish and giving 100% to his country and the game.

Creative Christmas Advertisements

2008-12-26T14:37:00.006+05:30

Here are few very creative Christmas advertisements,

Artificial Intelligance at its best : EpiSimdemics

2008-12-19T16:03:00.003+05:30

US researchers are focusing on creating a simulation of movements of all 300 million Americans. This is a huge project and they are going to use this to predict contagious diseases, such as a flu epidemic, might spread through different regions. That is the first step but there can be lots of hidden agendas here. Anyway it is a very interesting work, lets see how its going to come out in the near future.

Read Full Article Here : http://www.physorg.com/news148018856.html

Dooms Day 2012: Leaks Found in Earth's Protective Magnetic Field

2008-12-17T15:51:00.006+05:30

Does the Dooms Day 2012 is going to be a reality???? I just saw this news post in the web and according to that, This field (Earth's Protective Magnetic Field) is protecting our planet from severe solar storms. And it has leaks in some unexpected locations. So according to NASA the bottom line is "When the next peak of solar activity comes, in about 4 years, electrical systems on Earth and satellites in space may be more vulnerable".
In about 4 years is very much close to 2012 December 21st, which is the date of Doomsday according to Mayans mythology. And if these leaks will get wider and the impact of the solar storms can be more severe than just an electric blackout. (Am I seeing Crocodiles in the basin???) So hope its time to go back to your prayers guys.

Read the full article here : http://www.livescience.com/space/081216-agu-solar-storm-shield-break.html

Retrun row numbers from a TSQL query

2008-07-15T14:33:00.003+05:30

I came across a situation where I need to get the row numbers together with the result set. And finally found a very tricky way of doing it which I felt worth sharing;

SELECT col1,col2,(SELECT COUNT(*) FROM Table1 T2
WHERE T2.pkid <= T.pkid) AS rownumber
FROM Table1 T

And if you have a where condition it will return the original row number regardless of the no of the filtered rows. Hope this will be helpful to someone.

Second life : The revaluation of modern web

2008-07-14T15:07:00.005+05:30

If you have ever heard of Second life (http://www.secondlife.com/) you must know how exciting it is. When I came across this for the first time I felt like it will be the next generation of web. It has given a birth to the most lacking part of the web (virtual representation of the community).
For those who don’t know what it is this is how SL defines itself:
"Second Life® is a 3-D virtual world created by its Residents. Since opening to the public in 2003, it has grown explosively and today is inhabited by millions of Residents from around the globe. "
When you get registered there you can create an avatar which is basically virtual you. And you can build your own house according to your own design. You can buy stuff using Linden Dollars (SL currency) or start your own business. Also there are lots of events everyday you can participate from community service events to fashion shows. The most important thing is each and every person you meet there is actually a real person. And also for the people who do code can use Linden Script Language (LSL) to code a small game or a highly complex program. If you build something interesting you will have a good consumer market so you can earn money. And also you can exchange your Linden Dollars to USD at any stock exchange inside the SL.
This site has initialized in 2003 and now it has more than 1 million residences, so if you go there you can meet lots of people and if you are smart enough you can be rich or famous in the second life. There is a real chance that you may feel the second life is better than your first life. At the moment there are people who has addicted to this and spent most of their time in this virtual world and live less time in the real world.

JR the Savior for Sri Lankan Politicians

2008-07-11T10:53:00.003+05:30

There are lots of controversies regarding yesterday’s mass strike planned by opposition parties. But as a whole it was a failure even though Mr. Lal Kantha says it was a trial run. And once again Mahinda Rajapaksha government was able to come out of the trouble.
As far as I think people had 1980 July incident in back of their mind and that’s what made this a failure. So it’s another instance where JR's tactic has paid out for this generation of politicians. We have heard lots of people(even presidential candidates) blaming for Executive presidency as well but all these time each and every President has benefited from that. So the things he did still count well being for today’s politicians. He has done his part in the history for the politicians to survive for another decade or two. Also he has ruined our chances to develop as a country by making stupid decisions which we haven’t reverse even after more than 30 years. And we have no hope those will happen in the future as well. All together we are a hopeless nation with bunch of selfish politicians.

Cartoon by Dasa for Lankadeepa says it all

Ajantha Mendis : Rising of a Legend

2008-07-07T13:22:00.006+05:30

I was so fascinated to watch Medis in action yesterday against India. It was a while since we saw Sri Lankan Cricket team doing this well in a competition. Followed by Jayasooriya's brilliant innings in the upfront it was all Mendis show. Following are the few interesting comments I found :
"It was like you were playing something else, and the ball was something else. I won't really blame the batsman, we couldn't pick the deliveries. If you see our bowling, it was the best bowling line-up we could offer when we wanted one more extra batsman in the side. They tried their best and we could have got 274 but for the Mendis factor." - MS. Dhoni
"I wonder how good he was as a kid at carrom. Take the "red" boy!" - Crickinfo Live Scorer on Mendis
"We had never played him before. We had only seen videos and you can visualise and all, but he was difficult to pick out there in the middle. We never had any real reply against him." - MS Again
"A mystery spinner he has been thus far. The mystery is now out in the open and every batsman is out to solve it. The real challenge for Mendis, of uncertain categorization, to maintain that secrecy, begins now. " -Osman Samiuddin
"Dhoni, whose innings stood out for its sensible approach, was mesmerised even at the press-conference. For every question demanding explanations for the defeat, he the same answer: Mendis." - Sidharth Monga
'The 23-year-old plays his cricket for the Sri Lankan army. I suggest he's going to make a right officers' mess of hundreds of international batsmen's stumps in years to come" - Sportingo's Mark Rivlin

Dialog TV is on a role to suicide

2008-07-03T16:29:00.002+05:30

I am highly disappointed with DTV's channel selection. When the Zone Reality went off they have introduced a real crap channel called NDTV Good Times which is giving a very bad time. In contrast to the Zone Reality which is one of the very few quality English channels in DTV this is really heart breaking. Given the fact that there a hell of lot of Hindi crappy channel's and adding more to it when dropping quality ones, where are they really going towards. In my opinion they are going to fell in to earth.

Indian's run feast against Pakistan

2008-06-27T19:12:00.004+05:30

I was watching the sensational batting performance by Indian's yesterday over Pakistan. It was absolutely wonderful to watch. When Shewag and Suresh clearing the boundaries I was just wondering how big this ground is. But its Karachchi one of the biggest grounds. It was unbelievable, and there is no doubt that they have got some influence from IPL. And the other thing is 300 is no more a winning total in 50 over format. Everybody talks about T20 is killing 50 over cricket, but what I saw yesterday was 50 over cricket is improving from T20.
In the post match interview only I got to know that the Karachchi has 85 meter boundary and its so big. Anyway its wonderful batsmanship by Indians. Thanks a lot guys for the entertainment.

SQL Server 2008 RC 0

2008-06-26T16:25:00.003+05:30

SQL server 2008 RC 0 is now can be downloaded from Microsoft website. But when I see the new fetures list, what I felt was its not a big step forward. Nothing much I could found other than some great marketing words.
Anyway below is the some statistics about Microsoft exams on SQL server;

According to Microsoft, as of early 2008:
· 149,590 people have received the SQL Server 2000 MCDBA certification (to be phased out in early 2009)
· 24,939 people have received the MCTS: SQL Server 2005 certification
· 4,006 people have received the MCITP: Database Administrator certification;
· 1,596 people have received the MCITP: Database Developer certification
· 801 people have received the MCTS: Business Intelligence certification.

So it's clearly droping isnt it.

Problem of debuging ASP.NET in VS

2008-06-25T15:02:00.003+05:30

I came across with this issue while I was trying to debug a web service in VS. There are few things you have to look into if you come across something similar this.

1. Go to the properties of the particular website in IIS using inetmgr. Then go to the ASP.NET tab and set the .NET framework to the correct version.
2. In the directory security tab you have to check the Integrated Windows Authentication.
3. The other thing is related to IE browser. You have set Local Intranet authentication to either automatic login in the intranet zone or automatic login using uid and pass.

Hope this helps.

Nice Sri Lankan joke site

2008-06-18T19:06:00.003+05:30

I came across a nice joke site today url is http://www.udurawana.com . This is from that site

At a party a Mervin S sees a beautiful girl and invitesher to dancewith him...
Mervin S : Can I dance with you?
The Girl : I'm sorry I'm a lesbian..
Mervin S : It's ok. It's ok.. I'm a THOMIAN

There are lots of cool jokes like this in the site. Enjoy ..

Sri Lankan Wedding rituals in the 16th Century

2008-06-16T19:12:00.002+05:30

Sharp at the auspicious time his uncle brought the blade towards his cheek. He was going to have his first shave. He wore a grave look on his face, covered with the bristles of the first dawn of hair. He gulped once or twice and tried to stay still as his manhood was shaven off. He was seventeen. Tomorrow he would be getting married.
She stood on the pooruwa clad in the traditional Osari. Deep black eyes, young and innocent like the eyes of a doe, peered through the jewellery, worn to match those of the goddess Paththini. The black irises shone as brightly as the "thalla" on her forehead. As her uncle began to pour the water over their entwined fingers, she felt his arm brush against her. She glanced at him through the corner of her eye. She had not seen his face clearly yet. She only had a vague feeling that he was thin, had fair skin and curly black hair.
Lunch, called the "adara batha" (the meal of love), lasted for over three hours. The bride and the groom seated at the head of the table ate from the same plate. When he thought no one was looking, he pushed pieces of fish towards her. She took them timidly, but picked at her food. She found it an effort to mix the curries on her side of the plate and raise her hand towards her lips. The eyes of the entire table were on her, she thought.
The sound of the gunshots fired into the sky when she had first stepped onto the compound of her husband's house, still lingered in her ears as she was shown their bedroom. She sat at the edge of the bed and waited quietly for him. The black teak furniture gleamed in the light of the big kerosene lamp beside her. Outside, everything was quiet except for the never-ending chorus of a group of Citerns. He came in quietly and closed the door.
She turned towards him and saw his face for the first time. He had curly hair, a thin face and dark black eyes. He smiled down at her. She began to stand up. But he moved towards her, placed his hands on her shoulders and gently pushed her back on to the bed. Seating himself beside her, he raised her chin towards him. She looked into his eyes and saw the gentle sparkle in them. Her heart began to beat rapidly. Strange emotions began to engulf her. She buried her head on his shoulder. His hand moved towards her hair. Slowly he began to loosen the knot tied to the nape of her neck. She could feel his fingers tremble as they encountered the thick black stresses.
The chatter of two squirrels in the garden announced to the world a new day had dawned. The door opened and her mother-in-law came in. The girl lay on the bed, her thin, lithe body wrapped in a white sheet. She looked as radiant as the flowers in the sal tree outside. He stood at the window with his back turned towards the two women. She recollected the night she had spent with him, when he had known her as only a man could know a woman. Now she got up and began to get dressed as her mother-in-law held her clothes and the white sheets to the sunlight, and examined them thoroughly.
From the curve of his cheeks, she could see her husband was smiling to himself. And she knew the same smile would be on her mother-in-law's face as she took a coin from her bosom and tied it to the edge of the bed sheet. The coin meant the girl had proven her virginity. She would be accepted into the family as the wife of their youngest son.
Source : http://www.lankalibrary.com/

Fuel price hike

2008-06-13T13:26:00.006+05:30

The continues rise of fuel prices in Sri Lanka and around the world has made peoples life very uncomfortable. It's really affects largely to countries like us. Either you are rich or poor, it has some serious impact. So what are the practical solutions we can think of.
I think the best thing we can do at the moment as a country is to reduce the consumption. Government also has a big part to play here. If you look at a road at any time in Colombo what you will definitely notice is lots of cars, vans and big buses around. Some cars are just to satisfy one persons traveling requirement. Also buses lots of them, I think we have to go for a better public transportation than this. Trains are the best solution also a boat service. But I am very surprised why any of these government hasn’t take any initiatives towards this. Just imagine having a tunnel train service for at least Colombo city limit. This will definitely save lots of money and time for us. I think most of the people will use Public transport if it is up to that standard.
So without fighting over facts that we cannot change we have to think out of the box here. And implement these things asap. Otherwise we will be waiting till oil comes out of our sea forever.

Error installing SQL Server 2005

2007-10-03T17:10:00.001+05:30

I came across a problem while I was trying to install SQL Server 2005 on a virtual server. First Windows installer doesn’t allow to install through a remote connection. So I had to copy all the installation disks data to the server hard disk. And then when I tried to run the setup it gave me this error "There was an unexpected failure during the setup wizard". I was clueless and finally found that if it is installing from a network share or a hard disk the files must be properly structured. The server disk (disk 1) data must be copied to a folder with name Servers and the tools disk (disk 2) data must be copied to a folder with name Tools. These two folders must be in a one folder root as well. Then it worked fine :) Hope this will help ....

Related MSDN KB article http://support.microsoft.com/kb/916760

My Car

2007-08-15T12:47:00.002+05:30

This is my car its a Nissan Presea R11. Looks cute doesnt it :)

Nice Picture

2006-11-17T16:20:00.001+05:30

This picture was taken by Dominic Sansoni. It is really a cool photo. I am really not a photographer and I know nothing about the art behind it. But simply it did catch my attention from a single glance at it. Just drop in to his blog where he has lots of classic stuff.
http://dominicsansoni.blogspot.com

Ebooks

2006-11-17T16:14:00.001+05:30

Here is a good website which has heaps and heaps of ebooks. The specialty about the site is it has huge variety of ebooks nearly related to every field.

www.b213.net

Sri Lanka's Ethnic conflict

2006-09-01T10:46:00.001+05:30

First of all I must say I hate politics. But at the moment my county is at war because of political madness. Majority of the Colombo community is jubilant by the war, but for me no matter who gets most of the kills at the end of the day we have lost HUMANS. I really disappointed about the way things happening around me. What I seen every where is growing disappointment in Tamils, Sinhalese as well as Muslims. I believe this can drive us in to a major conflict than what we have now. One of my friend told me in England, inside of some Tamil dominated restaurants they have banned using Sinhala. Everyday both LTTE and SL Forces kill some people for both parties they have good solid reasons (everything is humanitarian missions) but what about the people who get stuck in between?

I have one of my cousin work in Sri Lankan Airforce Palali camp and I know how hard for their parents and relatives to bare it. Every news which says about LTTE attacks are brings them sleepless nights. I think some fundamentalists in down south of the Island does not feel this as they always cry out for blood.

Your comments are welcome.

Microsoft CRM 3 Report Customization

2006-08-31T14:51:00.001+05:30

When I was doing some customizations on standard CRM 3 reports I found that it is much difficult to edit existing complex Datasets. Visual Studio 2005 IDE itself does not support to edit these complex datasets in Query builder. So it is really a headache to edit the SQL statements instead. The really bad thing is if you manage to change the SQL statements (which is a nightmare) when you trying to save the dataset VS 2005 get stuck (most of the time) or will take lots of time (actually more than that).

So I went mad trying to do this tedious work. Finally found a work around which is worth sharing. Inside SQL Reporting services, report file(.rdl) is actually XML file which contains connection details, dataset queries, reporting fields and every detail which required generating the report. So what I did was editing this XML file, but one thing you should remember is never try to understand the XML file. Just find the required query you want to edit and change it and save the XML file. Now you can open the dataset it in the VS and modify the report.

Happy coding..

Cant view CHM files? Page can not be displayed ...

2006-08-17T15:24:00.001+05:30

I faced this problem today while I was trying to open an EBook. And also I found that every compiled HTML document have the same problem. So the solution was pretty simple but can drive you mad if you don’t find the solution soon.

Here we go.. If this appeared suddenly there is fair bit of chance you are having the same problem as I had. Because of the XP security updates it has blocked non secure execution so ultimately it has blocked my CHM files. The solution is pretty simple go to the properties of the file and click on the "unblock" button if you are lucky enough this will drag you out of the pit.

But if it is happening for only one or few files and some of the CHM's are working then you may be having # somewhere in CHM file path.

Eg:- "C:\Ebooks\c#\ebook.chm"

This want load until you removes the # mark.

Anyway there can be many more reasons but these two basically works. Comments are welcome

SQL Performance Optimization Tips

2006-08-16T17:13:00.001+05:30

I was working with this topic during last few days and found some intersting topics. So I hope all of you will benifitited from these stuff.

SQL Performance Optimization Tips

1. Restrict the queries result set by using the WHERE clause as much as possible.

By returning data you don't need, you are causing SQL Server to perform I/O it doesn't need to perform, wasting SQL Server resources. In addition, it increases network traffic, which can also lead to reduced performance. And if the table is very large, a table scan will lock the table during the time-consuming scan, preventing other users from accessing it, hurting concurrency.

2. Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns. Avoid using SELECT *

This can results in good performance benefits, because SQL Server will return to client only particular columns, not all table's columns. This can reduce network traffic and boost the overall performance of the query.

INSERT INTO dbo.my_table

SELECT * FROM dbo.another_table

Don’t use select * , ever in this type of queries.

INSERT INTO dbo.my_table(Numkey,Description,MoreStuff,Otherstuff)

SELECT atID,description,town,county FROM dbo.another_table

Using a column list, additional columns or change of column order will not cause a break.

3. Try to avoid using Server side cursors, whenever possible.

If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task.

Here are some alternatives to using a cursor:

Ø Use WHILE LOOPS

Ø Use temp tables

Ø Use derived tables

Ø Use correlated sub-queries

Ø Use the CASE statement

Ø Perform multiple queries

More often than not, there are non-cursor techniques that can be used to perform the same tasks as a SQL Server cursor.

If you have no choice but to use a server-side cursor in your application, try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance. This type of cursor produces the least amount of overhead on SQL Server.

If you are unable to use a fast-forward cursor, then try the following cursors, in this order, until you find one that meets your needs. They are listed in the order of their performance characteristics, from fastest to slowest: dynamic, static, and keyset.

If you need to perform a JOIN as part of your cursor, keyset and static cursors are generally faster than dynamic cursors, and should be used when possible.

When you are done using a cursor, don't just CLOSE it, you must also DEALLOCATE it. De-allocation is required to free up the SQL Server resources used by the cursor. If you only CLOSE the cursor, locks are freed, but SQL Server resources are not. If you don't DEALLOCATE your cursors, the resources used by the cursor will stay allocated, degrading the performance of your server until they are released.

4. To return the total table's row count, use alternative way instead of SELECT COUNT(*) statement. Can use “sysindexes” system table (View).

Because SELECT COUNT(*) statement make a full table scan to return the total table's row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use “sysindexes” system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*):

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2;

So, you can improve the speed of such queries in several times.

NOTE : -

There is one potential downside to using the sysindexes table. And that this system table is not updated in real time, so it might underestimate the number of rows you actually have. Assuming you have the database option turned on to "Auto Create Statistics" and "Auto Update Statistics", the value you get should be very close to being correct, if not correct. If you can live with a very close estimate, then this is the best way to count rows in your tables.

5. Try to use constraints instead of triggers, whenever possible.

Constraints are much more efficient than triggers and can boost performance. So, you should use constraints instead of triggers, whenever possible.

However, referential integrity constraints cannot:

· Cascade changes through related tables in the database

· Enforce complex restrictions by referencing other columns or database objects

· Perform "what if" analyses

Also, referential integrity constraints do not roll back the current transaction as a result of enforcing data integrity. With triggers, you can either roll back or continue the transaction, depending on how you handle referential integrity.

Constraints, including check, referential integrity and cascading referential integrity, perform better than triggers. Cascading referential integrity constraints are automatic updates and deletes on dependant objects.

6. Use table variables instead of temporary tables.

Table variables, such as local variables, have a well defined scope at the end of which they are automatically cleared.

Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.

Ø Table variables are completely isolated to the batch that creates them so no 're-resolution' has to occur when a CREATE or ALTER statement takes place, which may occur with a temporary table. Temporary tables need this 're-resolution' so the table can be referenced from a nested stored procedure. Table variables avoid this completely so stored procedures can use plan that is already compiled, thus saving resources to process the stored procedure.

Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.

In general, you use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table. In that case, you can create indexes on the temporary table to increase query performance.

*Note - The table variables are available in SQL Server 2000 only.

7. Try to avoid the HAVING clause, whenever possible.

The purpose of using HAVING clause is to filter the results returned by a GROUP BY clause. Since we can not use WHERE clause to restrict values of aggregate functions we must use HAVING clause.

When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without HAVING clause. This can improve the performance of your query.

This is because of the HAVING clause is performed after the aggregate functions and grouping take place. But when we use WHERE clause it restricts the results first and then performs the GROUP BY and aggregation so it is always improve the performance of the query.

SELECT city,sum(sales) FROM dbo.salesmaster

GROUP BY city

HAVING sum(sales) > 2000;

This query is fine where as you have no any option other than using HAVING clause.

SELECT province, city, sum(sales) FROM dbo.salesmaster

GROUP BY city

HAVING province = ‘Western’;

This query can be optimized by using WHERE clause

SELECT province, city, sum(sales) FROM dbo.salesmaster

WHERE province = ‘Western’

GROUP BY city ;

8. Try to avoid using the unnecessary DISTINCT clause, whenever possible.

DISTINCT is separated into 2 processes by SQL - one to reduce the rows and one to calculate the returned results.

Using the DISTINCT keyword means that redundant data is being pulled from the database and then discarded. Usually it is better to rewrite the query's FROM and WHERE clauses to use a subquery to filter the data correctly so that you only get back what you want.

9. Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.

This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.

So it is recommended as a practice to use “SET NOCOUNT ON” in every SP you write unless you require the no of rows effected to return.

10. Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.

This can improve performance of your queries, because the smaller result set will be returned. This can also reduce the traffic between the server and the clients.

The efficiency of Top 1 is usually the same as max() – but may depend upon the indexing, if a sort has to occur a worktable may be created – check with the query plan.

In the quest for unbreakable code and clarity if it is the max(imum) or min(imum) value that is required then use these functions

Select TOP 1 may return (logically) either the maximum or minimum value for a column, or some other non deterministic value.

Never use a TOP command without an order by statement

11. When deleting all the rows from a table use TRUNCATE instead of DELETE.

SQL's DELETE clause is more flexible than TRUNCATE TABLE because it allows you to specify which records to remove. In addition to that TRUNCATE TABLE does not log individual row deletes.

TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

You need to be db_owner, ddl_admin or owner of the table to be able to fire a TRUNCATE statement.

12. Index columns which are used in WHERE clauses.

Searching a value without an index will require full table scan (searching on each and every page files). This will produce a huge performance drop for larger tables. For optimizing such a query we have to avoid table scans by using indexes very intelligently.

Just like the reader searching for a word in a book, an index helps when you are looking for a specific record or set of records with a WHERE clause. This includes queries looking for a range of values, queries designed to match a specific value, and queries performing a join on two tables

Since index entries are stored in sorted order, indexes also help when processing ORDER BY clauses. Without an index the database has to load the records and sort them during execution.

Indexes are also useful for maintaining unique values in a column, since the database can easily search the index to see if an incoming value already exists. Primary keys are always indexed for this reason.

When you choose fields for create indexes consider the following points;

Ø Always try to choose smaller entities. Since larger index keys will cause the database to perform more disk reads, which limits throughput. Also index entries are often involved in comparisons, smaller entries are easier to compare.

Ø The most effective indexes are the indexes with a small percentage of duplicated values.

Indexes can be a huge lead to a very big performance drawback if used in careless manner. Here are some Index drawbacks;

Ø Indexes are a performance drag when the time comes to modify records. Any time a query modifies the data in a table the indexes on the data must change also.

Ø Indexes also use disk space. The exact size will depends on the number of records in the table as well as the number and size of the columns in the index. Generally this is not a major concern as disk space is easy to trade for better performance.

13. Minimize the use of not equal operations, <>, != or NOT. Try rephrasing the expression using ranges.

When we use not equal operators SQL Server has no any option other than run a table or index scan for responding to the query. Always we must try to avoid table or index scans to improve the performance. So it is a better practice to use ranges instead of <> or NOT.

Of course, you may not have any choice of which operator you use in your WHERE clauses, but sometimes you do.

Here are the key operators used in the WHERE clause, ordered by their performance. Those operators at the top will produce results faster than those listed at the bottom.

=
>, >=, <, <=
LIKE
<>

The lesson here is to use = as much as possible, and <> as least as possible.

14. DONOT start the name of a stored procedure with SP_.

This special prefix is reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working (unless if you use a existing name in system sp’s), what it can do is to slow down its execution ever so slightly.

The reason for this is that by default, any stored procedure executed by SQL Server that begins with the prefix "sp_", is first attempted to be resolved in the Master database. Since it is not there, time is wasted looking for the stored procedure.

If SQL Server cannot find the stored procedure in the Master database, then it next tries to resolve the stored procedure name as if the owner of the object is "dbo". Assuming the stored procedure is in the current database, it will then execute. To avoid this unnecessary delay, don't name any of your stored procedures with the prefix "sp_".

15. To assign values to variable use SET unless the value is being retrieved from a table.

Assigning a constant value to a local variable

SET @myvariable = 10

Assigning a local variable the value of another variable

SET @myvariable = @someothervariable

Concatenating string variables

SET @mystring = @mystring + @anotherstring + ‘ this is the end’

Assigning values from datasets

SELECT @myvariable = column from dbo.mytable where xxx = yyy

Handling TOP

SELECT TOP 1 @myvariable = column from dbo.mytable

Handling max ( min, count, sum etc. )

SELECT @myvariable = max(column) from dbo.mytable

16. Try to avoid using sub queries within SELECTS (Correlated Subquery).

A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. Therefore, the correlated subquery can be said to be dependent on the outer query.

SELECT CustName, Address, City, Country, PostalCode

FROM dbo.Customers

WHERE CustomerID IN (

SELECT CustomerID

FROM dbo.InvoiceMaster WHERE

dbo.InvoiceMaster.CustomerID = dbo.Customer.CustomerID

) ;

As you can see in the above query the inner query uses a value from outer query. This is called as Correlated Subquery.

A correlated subquery takes part of it's select from the outer query. In this way it is depenedent upon the outer query. Most times a correlated query has to execute once for every row in the outer query. So this can lead to a performance drop.

17. Always owner qualify your objects

Eg : - dbo.Customer

Look for objects that are not called with qualified owner names. Each time the query is run without qualified owner names on the objects referenced within the query, the Optimizer has to hold compile locks on system objects until it can determine if the objects are the same as the ones in the cached plan. Qualifying the owner name will solve this problem and help with performance and blocking problems.

So at last here are some links where you can find more .

http://www.sql-server-performance.com/vk_sql_best_practices.asp

http://www.sql-server-performance.com/gv_sp_views_selects.asp

http://www.sql-server-performance.com/misc_tips.asp

http://www.sqlservercentral.com/columnists/gvijayakumar/executionplans_printversion.asp

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1170220_tax301334,00.html?adg=301324&bucket=ETA#row

Have a laugh...

2006-03-27T11:11:00.001+06:00

This is a very cool blog which has lots of funny stories. So have a good laugh.....

http://coolclown.blogspot.com

Smart Client Applications a new era?????

2006-01-06T15:07:00.001+06:00

I am little facinated about MS smart client architecture. It's a real cool idea since it has inherited most of the advantages of both desktop and web application architecture. But again acording to my point of view designning a smart client applications require more effeort but if you have good design on hand it is really a cool process. If this will get developed more efficiently the day we are running a rich GUI applications on distributed architecture is no longer a dream.

Happy New Year 2006

2005-12-30T10:08:00.001+06:00

New year, new hopes, new goals I am always facinated at this time coz I feel like re-borned. Do u? Feel like doing some crazy things to celebrate this festival time. Anyway even next year we have to code and debug till we die thats our life but when it's come to party we must enjoy. Last weekend I met a person who is around late sixties so I asked him "what do you feel about a new year". He said "Life is all about dreams if you dont have any dreams you will feel life is boring and new year is a good time to forget your failures and start over with new dreams". So guys make new goals and make a good plan. All da best for 2006.

Merry Cristmas

2005-12-28T15:56:00.001+06:00

It's party time for me. I just tought of get rid of all the work and go wild of fun. Anyway it is about to step in to another challenging year in life. Last few weeks were very tough on me. I had to migrate an old application to a new server and it was really a tough time since there was NO DOCUMENTATION what so ever. hummm... you want belive me I had to do lots of modification with out a trace. Sooooo it is time for some fun. Wish u all a Merry Cristmas and a very very happy new year.

Life ..... Any explanations

2005-12-07T14:21:00.001+06:00

What is life and what is this world.... quite a bad question to ask from someone but have you ever think what is this all. I am thinking most of the time but I cant even nearly think a acceptable answer. If anybody have an answer please help me.

Linking Servers in SQL Server

2005-11-30T11:05:00.001+06:00

Sometimes it is very important to link your SQL Server with antthor one since it is the best solution for some situations. This is very detailed article about How to link two SQL Servers and also how you can link an external datasources (Eg. Orcale, MS Access, Spredsheet, etc.)

Click here to read the article
By Neil Boyle

To SP or not to SP in SQL Server

2005-11-21T15:36:00.001+06:00

This is sometimes a real big challenge we face during the development. This article have some real important stuff you should consider before taking your decision.

To SP or not to SP in SQL Server
by Douglas Reilly on MS SQL Server

Why Sealed Classes?

2005-11-16T10:08:00.001+06:00

Why Sealed Classes?

The main purpose of a sealed class to take away the inheritance feature from the user so they cannot derive a class from a sealed class. One of the best usage of sealed classes is when you have a class with static members. For example, the Pens and Brushes classes of the System.Drawing namespace.

The Pens class represent the pens for standard colors. This class has only static members. For example, Pens.Blue represents a pen with blue color. Similarly, the Brushes class represents standard brushes. The Brushes.Blue represents a brush with blue color.

So when you're designing your application, you may keep in mind that you have sealed classes to seal user's boundaries ;).

By Mahesh Chand @ www.c-sharpcorner.com

Some really nice articles on Sealed Classes and Methods in .NET.

http://www.csharphelp.com/archives/archive158.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/csref/html/vclrfsealed.asp
http://www.c-sharpcorner.com/Code/2002/Sept/SealedClasses.asp
http://www.lemanix.com/nick/archive/2005/10/19/3178.aspx

Access dialog results from the parent form in c#

2005-11-10T10:50:00.001+06:00

This is something very important for a developers who is migrating from VB to .NET. Even though this is explainning using C# it is not much ditingush from VB code.

Lets say you are having two Windows forms called Form1 and Form2 in your project. Form1 will be your parent form and Form2 will be the Dialog form.
Form1 will have a Command button and a Label control to display the input from the Dialog form(Form2). Dialogform(Form2) will have a textbox and two command buttons.
In the Form2 change the text properties of two buttons as OK and CANCEL.
Set OK buttons DialogResult property to OK and CANCEL buttons property to Cancel.
Set Modifier property of the textBox1 in the Form2 to Public or Internal. This will allow the parent form(In this case form1) to access the text value of the dialog form.
Write following code segement on click events of both buttons (Or create one method and change the delegation)

this.Close();

In the Form1 Command buttons Click event write the following code

Form2 nfrm = new Form2();
if(nfrm.ShowDialog() == DialogResults.OK)
{
this.label1.Text = nfrm.textBox1.Text;
}

It is pretty simple and if you have any further quetions, corrections, suggetions please contact me.

By Kaminda Berugoda

Combining JavaScript with ASP.NET Web forms

2005-11-10T08:27:00.001+06:00

ASP.NET provides a new paradigm for developing Web-based applications. This includes a set of server-based controls that are analogous to HTML form elements such as text boxes, buttons, and so forth. The problem with these controls is the need to call the server. JavaScript provides an alternative for many tasks that negates the need to go to the server. Let's take a closer look at combining the power of JavaScript with ASP.NET.

Read the full article in click here

Microsoft release SQL Server 2005, VS .NET 2005 & BIZ TALK 2006

2005-11-09T16:27:00.003+06:00

The next generation of software development platform

What's New in SQL Server 2005?

What's New in VS.NET 2005?

Andy Mc's .NET Framework FAQ

2005-11-09T16:05:00.001+06:00

The .NET Framework FAQ was first posted in July 2000, and is regularly updated. It covers the fundamentals of the .NET Framework including assemblies, garbage collection, security, interop with COM and remoting. Newcomers to the .NET framework may wish to read the FAQ from top to bottom as a tutorial. More experienced practitioners may prefer to consult the contents list for topics of particular interest.

This FAQ was inspired by discussions on the DOTNET mailing list. The list has now been split into several DOTNET-X lists - for details see http://discuss.develop.com/.

Christophe Lauer has translated the FAQ into French. Royal has translated the FAQ into Chinese.

If you like this FAQ, you might be interested in my C# FAQ for C++ Programmers.

Click here to visit the site

Sri Lanka .Net Forum

2005-11-09T15:57:00.001+06:00

A nice place to put your questions.