Happy New Year 2010!

TSQL – Retrieve only numbers in a string

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.

/****** Object:  UserDefinedFunction [dbo].[fn_filternumbers]    Script Date: 11/27/2009 17:59:56 ******/

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

declare @Res nvarchar(255)
declare @i int, @l int, @c char
select @i=1, @l=len(@Src)
SET @Res = ''
while @i<=@l
   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


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.


C# – Retrieve only numbers in a string

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.


Ravana the Great on the Canvas

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.

Time Travel on the Web

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


Download Torrents without a Torrent Client

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)

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:


MS CRM 4 - Hiding System (Public) Views

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;


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

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.

SQL Server - Error Rebuilding Indexes in Online Mode

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,..."

Sri Lankan Cricket Team's Indian Tour 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)

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;


Reality of the Reality shows in Sri Lanka

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.

Cricket Commentators, Are they really expert?

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.

My Wedding Picture 31st August 2009

Find Modified date of a Stored Procedure

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

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);

Sanga and the team : We are proud of you guys

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

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.


BBC and Mahinda Rajapakshe

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!


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

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

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


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

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

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'

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

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

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

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.
Blog Directory - OnToplist.com