Monday, November 30, 2009

My Experience with TEDxChennai - Nov 29th 2009

TEDxChennai happened in IIT Madras on November 29th 2009. This is the first time I am attending one such event as the cost (Rs. 1028) was affordable for me :)

If at all you haven't known, a ticket in TEDIndia was approx Rs. 1 Lakh and 10 thousand. It seems its 1/3rd cheaper than what it used to cost in California (approx 3L phew).

Overall it was an mind blowing experience and I loved every moment of it. Post lunch sessions were power packed more interesting when compared with pre-lunch session.

In particular I liked these sessions:

1. Romulus Earl Whitaker - a wildlife conservationist and founder of the Madras Snake Park!

He should be called as 'Snake Man'. The snaps he showed and the work he explained made my jaw drop.

2. Sri. N. Vittal - ex-Central Vigilance Commissioner, ex-Secretary to Government of India in the Ministry of Information Technology, Chairman of the Telecom Commission and Secretary to the Department of Telecommunications. He has been columnist for the Economic Times as well.

Charisma is the capacity to simplify & glorify - N Vittal


His speech was hilarious and at the same time very powerful. He spoke about his career path and how he used to send questionnaire and understand the people who work and who don't :) He spoke about how Corruption is eating our country and asked us all to think how to resolve it from the grassroot level.

Gandhiji used the Judo technique,turning the enemy's strength against him.- N Vittal


3. Dr. Santhosh Babu - IAS. MD ELCOT, & Director e-Governance & CEO, TNeGA, Govt. of Tamil Nadu. I wish each one of us especially Govt Staff thinks and acts like the way he is doing!

4. Satyabrata Dam - He worked as a professional Submariner (Indian Navy) specializing in underwater navigation and weapon control for 22 years. He has conquered almost all peaks in the world. He say

Check out his blog to know more about him http://satyabratadam.blogspot.com/

I felt like it wasn't a speech or presentation at all. It was kind of walking along with Sathya in his memory lane. The pictures he showed were all awesome and I really envy this guy now.

Few of his philosophies are:

a. Every time you reach one peak, u start thinking about the next BIG peak.
b. Pure happiness is only achieved after suffering some great hardships
c. There are many EVEREST's in everyone's life & we all climb them in our own unique ways
d. People in mountains all over the world own nothing but are willing to share everything

Any big mountain big or small has to be climbed one step at a time - Satyabrata Dam


5. R. Madhavan - is an IITian, turned farmer who married his passion for farming with his engineering background. Really inspiring speech and the way he explained how well we can do in our agriculture department were really good. I am sure almost all who were present there gasped when he spoke but whats next? How do we take it forward from here to help him/our farmers better? Its something which we need really think!!

6. Kavita Baliga - her speech was very emotional and it is really inspiring to know how she has shaped up her life. She taught us again to live a life that will have an positive affect on others. She is an amazing singer. Check out www.kavitabaliga.com to know more about her.

7. Jeeva Raghunath - a professional story teller. She brought the kid in each one of us out. Was really good.

Don't kill the Child in you as you grow up - Jeeva Raghunath


Couple of sessions went as a Marketing pitch and I wish those were avoided as they weren't in TED standard.

Next year the event is planned for 10/10/10 (fancy date :) )

Friday, May 15, 2009

How to find when SQL Server was last started?

When ever SQL Server is (re)started ‘TempDB’ gets recreated. So the below query should give us the time of when ‘SQL Server’ was last started.

Select create_date from sys.databases where [name] = 'tempdb'

When a SQL Server starts all the tasks which it initiates makes an entry in sysprocesses table with its ‘login time’. That said, all we need to do is find the least login_time which has got recorded in that table!

1. Select min(login_time) from master.dbo.sysprocesses
OR
2. Select login_time from master.dbo.sysprocesses where spid = 1
OR
3. Select login_time from sys.dm_exec_sessions where session_id = 1

Technorati Tags:

Tuesday, May 12, 2009

‘The Downfall’ of AGILE Hitler...

This one is hilarious :) I couldn’t stop laughing after watching this short cooked up movie.

If you are in IT for atleast one year then you would love this as much as I did :)

Arithmetic overflow error converting IDENTITY to data type int.

Today I ran into the below error:

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.

Arithmetic overflow occurred.

As the error suggests in the table the max limit of INT (2147483647) has reached.

For the sake of people who haven't seen this error before let me simulate it.

Create table MaxInt
(
  sno int identity,
  Fname varchar(30)
)
Go

---Inserting my first record
Insert into MaxInt values ('Sample')

/* huh i can't insert billions of records like this and show :) Let me Reseed to last few numbers */
DBCC CHECKIDENT ( MaxInt, ReSeed, 2147483646)

--Try inserting a record. It would
Insert into MaxInt values ('Vadivel')

/* Trying to insert another one .... now you would see the error mentioned above as we have reached the max of INT already */
Insert into MaxInt values ('Vadivel')

Select * from MaxInt

Sno

Fname
1 Sample
2147483647 Vadivel

At this instance my first aim was to make the app up and running at the earliest and then think of the rest! :)

So i decided to reseed the identity column to -2147483648 and have the values count upward towards zero.

DBCC CHECKIDENT ( MaxInt, ReSeed, -2147483648)
Go

Insert into MaxInt values ('Vadivel')
Insert into MaxInt values ('Vadivel')

Now we can insert another 2 Billions records into the table.

[Obviously this is just the idea. Before using it please test against your application code and whether it is fine with having negative values in the table etc.,]

That said, Once we reach the Max limit of INT this is not the only solution we have. Other options which we need to consider are:

1. Convert the column from INT to BigInt datatype
2. Other version of the given solution is to start the number from 0 and make it count downwards as –1, –2, –3 etc.,

Related article: http://sql-server-performance.com/Community/forums/p/7430/43219.aspx

Technorati Tags: ,,

Sunday, May 03, 2009

Google’s ROBO …

Only yesterday I came to know that we can add webtoim@gmail.com to our Gtalk contact list and start chatting with that BOT.

I understand that now, instead of Googling and digging Wikipedia for the correct answer, you can take assistance from the WebToIM Bot that is designed to answer such questions.

Few of my observations:

1. Almost for all questions it starts answering as ”hmm…” which looks odd to me.

2. Very often even for basic questions (or) for questions for which I would get the answer by googling is throwing me
“Sorry. I couldn't understand what you are asking. Please rephrase.”

3. I tried the following queries “Whats your age” it said sorry. I rephrased it as “What is your age” it again said sorry.

Finally I asked “How old are you?” and it answered
hmmm...
3 years and 9 months old


4. I got this response quite often as well

hmmm...
I found multiple ways to translate that.


But why can’t it display the different ways it is talking about. I guess this answer doesn’t help anybody!

5. I tried “Who is the author” and got this as the response

norman liebold, Stuart Payne, Stephen King, Steven Pinker, Andy Mabbett, Göran Tunström, Selma Lagerlöf, Johann Wolfgang Goethe, Matt Ruff, J. R. R. Tolkien, Richard P. Crandall, Arthur Hugh Matthews, bee wilson, Bareschius, Longinus and Charles Dickens

6. Next I tried “Sachin Tendulkar” and got this pathetic response!!

hmmm...
Sachin Tendulkar is the (born 1973), the [[India]]n [[cricket]] player

Related URLs:

1. http://www.webtoim.com/
2.
http://www.trueknowledge.com/

Technorati Tags:

Microsoft Surface Computing …

Check out these videos to understand about Microsoft Surface computing. Both the videos are really cool I loved it thoroughly.

 

Blocking unwanted sites using your HOST file…

For past few days my laptop got quite a few malwares / virus. I somehow got it cleaned using few free anti-virus softwares / with the help of various articles in the Internet.

During the course of research!!! I stumbled upon a very interesting article on how to make use of your HOST file to protect your machine. Check out http://www.mvps.org/winhelp2002/hosts.htm

This Host file for Windows, has entries to block ads, banners, 3rd party Cookies, 3rd party page counters, web bugs, and even most web browser hijackers. This is accomplished by blocking the internet connection to malware sites.

But one thing which I have noticed as of now is my laptop has become slightly slower after replacing my old HOST file with the new file given in that site.

I opened the HOST file and OMG there are hundreds of malware/ad related website listed there. I am not sure how they managed to collect all these address :)

Technorati Tags:

Avoid using functions in WHERE Clause!!

Thumb Rule is as much as possible avoid using FUNCTIONS within WHERE Clause of your SQL Query.

Let me show you some very basic examples to substantiate this theory.

--Table schema
Create table tblDateExample (dtStartDate datetime)
Go

-- Populate dummy records into the table
INSERT tblDateExample VALUES ('2009-04-15 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-14 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-13 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-12 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-11 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-11 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-11 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-11 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-10 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-09 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-08 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-07 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-10 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-10 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-10 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-10 12:00:00.000')

-- Create an Index
Create index IXStartDate on tblDateExample ( dtStartDate )
Go

Using Functions within WHERE Clause

SET SHOWPLAN_TEXT ON
Go

Select dtStartDate from tblDateExample where Convert(Varchar(8), dtStartDate, 112) = '20090411'

SET SHOWPLAN_TEXT OFF
Go

Checkout its execution plan, as expected it doesn’t make use of the Index fully

|—Index Scan (OBJECT:([Testbed].[dbo].[tblDateExample].[IXStartDate]),  WHERE:(CONVERT(varchar(8),[Testbed].[dbo].[tblDateExample].[dtStartDate],112)=[@1]))

Rewrite the same query without a function in the WHERE Clause

SET SHOWPLAN_TEXT ON
Go

Select dtStartDate from tblDateExample where dtStartDate >= '20090411' AND dtStartDate < '20090412'  

SET SHOWPLAN_TEXT OFF
Go

The execution plan shows us that it uses Index Seek.

|—Index Seek (OBJECT:([Testbed].[dbo].[tblDateExample].[IXStartDate]), SEEK:([Testbed].[dbo].[tblDateExample].[dtStartDate] >= CONVERT_IMPLICIT(datetime,[@1],0) AND [Testbed].[dbo].[tblDateExample].[dtStartDate] < CONVERT_IMPLICIT(datetime,[@2],0)) ORDERED FORWARD)

That said, there are instances where we can still make use of Index Seek in spite of using Functions within the WHERE Clause. Check out the below query for an example.

Select dtStartDate from tblDateExample
where
Convert(datetime,dtStartDate,101) > Convert(datetime,'2009-04-11',101) and
Convert(datetime,dtStartDate,101) < Convert(datetime,'2009-04-12',101)

The execution plan for the above query:

|—Index Seek (OBJECT:([Testbed].[dbo].[tblDateExample].[IXStartDate]), SEEK:([Testbed].[dbo].[tblDateExample].[dtStartDate] > CONVERT(datetime,[@1],101) AND [Testbed].[dbo].[tblDateExample].[dtStartDate] < CONVERT(datetime,[@2],101)) ORDERED FORWARD)

On trying to understand the reason for this behavior i noticed there is no real conversion happening in the above query. i.e., Both the Converted type and the original column type are the same :)

Technorati Tags:

Friday, May 01, 2009

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed ...

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

If you happen to get this error message in your SQL Server 2005 then read on:

In "Results to Grid" format one cannot display more than 100 resultset.  So just change the result format to either "Result to Text" or "Result to File" option

Example to reproduce this error:

Step 1: Ctrl + D (setting to Grid format for demo purpose)
Step 2: Try to print the current datetime for 101 times for example

Select GetDate()
Go 101

You would see the error as we are trying to display more than 100 resultset in a "Result to Grid" format.

Just in case you thought 100 is the limitation of GO command in SQL Server try the below script

Create table tblQuestion4
(
Sno int identity,
Fname varchar(20)
)
Go

Insert into tblQuestion4 values ('Test')
Go 101

For easy reference:

1. Ctrl + T :: Result will be displayed in Text mode
2. Ctrl + D :: Result will be displayed in Grid mode
3. Ctrl + Shift + F :: Result will be saved to a File
 
Technorati Tags:

Check out this comment ... really funny

Got this code comment as a email fwd. Its really funny :)


//
// Dear maintainer:
//
// Once you are done trying to 'optimize' this routine,
// and have realized what a terrible mistake that was,
// please increment the following counter as a warning
// to the next guy:
//
// total_hours_wasted_here = 16
//

Saturday, April 18, 2009

Able to use sp_executesql without declaring variables!

From Denis blog I came to know recently that we are able to use sp_executesql without declaring the variables!

It really sounded strange to me. I thought it to be one another bug :)

So thought would dig deep into this and see what best I can conclude.

--Dummy table schema
Create table tblQuestion1
(
sno int identity,
fname varchar(50)
)
Go

--Lets add some dummy records into the table
Insert into tblQuestion1 values ('Alpha')
Insert into tblQuestion1 values ('Beta')
Insert into tblQuestion1 values ('Gamma')

--So-called strange script
Declare @strFetchData nvarchar(100)
Select @strFetchData = 'Select * from tblQuestion1'
Exec sp_executesql @alpha = @strFetchData

If you have noticed we haven't declared @alpha in the script but SQL hasn't complained about it!

I thought the easiest way to understand would be to go through sp_executesql procedure.

Sp_helptext sp_executesql
go

Result - "(server internal)" … so its not a stored proc.

I tried adding another dummy parameter!

Declare @strFetchData nvarchar(100)
Select @strFetchData = 'Select * from sys.sysobjects'
Exec sp_executesql @alpha = @strFetchData, @beta = N''

Just the @ alone is also taken without a variable name:

Declare @strFetchData nvarchar(100)
Select @strFetchData = 'Select * from sys.sysobjects'
Exec sp_executesql @ = @strFetchData, @beta = N''

The code still works.

I tried adding the third parameter! hurray it fails.

Declare @strFetchData nvarchar(100)
Select @strFetchData = 'Select * from sys.sysobjects'
Exec sp_executesql @alpha = @strFetchData, @beta = N'', @gamma = N''

So I guess during compilation SQL engine is ignoring undeclared variables for the first 2 parameters but not from the third parameter onwards.

That said, syntax of sp_executesql on a higher level is:

1. First parameter - SQL Stmt
2. Second parameter - Parameter Definition
3. Third parameter and going fwd -- Value for the parameters are assigned here. (Parameter mapping)

So once parameter mapping comes into play it fails. The first two parameters is being taken as a dummy placeholders!!!

Few more examples:

--This works
Declare @IntVariable int;
Declare @SQLString nvarchar(500);
Declare @ParmDefinition nvarchar(500);

Set @SQLString =
N'Select EmployeeID, NationalIDNumber, Title, ManagerID
From AdventureWorks.HumanResources.Employee
Where ManagerID = @ManagerID';
Set @ParmDefinition = N'@ManagerID tinyint';

Set @IntVariable = 197;
Exec sp_executesql @SQLString, @ParmDefinition,
@ManagerID = @IntVariable;

--This would fail
Declare @IntVariable int;
Declare @SQLString nvarchar(500);
Declare @ParmDefinition nvarchar(500);

Set @SQLString =
N'Select EmployeeID, NationalIDNumber, Title, ManagerID
From AdventureWorks.HumanResources.Employee
Where ManagerID = @ManagerID';
Set @ParmDefinition = N'@ManagerID tinyint';

Set @IntVariable = 197;
Exec sp_executesql @alpha = @SQLString, @ParmDefinition,
@ManagerID = @IntVariable;

Saturday, April 11, 2009

How to create Google finance like Charts?

From the day I saw Google Finance i was thinking what tool they would be using to display those cool, user friendly charts! After hours of searching on the web I learned that they were using some sort of custom in-house build tool/logic.

Fair enough, but that doesn’t solve my problem. I wanted to create some graphs similar to that for few of my projects. On further ‘googling’, just recently I stumbled upon amCharts which has the similar charts like Google finance.

You can download and use all amCharts products for FREE. The only limitation of the free version is that a small link to this web site will be displayed in the top left corner of your charts. Btw, if you don’t want the link go ahead and purchase the product.

del.icio.us Tags: ,
  
Technorati Tags: ,

Friday, April 10, 2009

List of some essential software one would need!

1. Browser – IE 8.0 and/or Google Chrome

2. Anti-Virus – One of the lightweight software is AVG. Check it out @ http://free.grisoft.com/

3. Offline thesaurus and dictionary - http://wordweb.info/free/

This is a cool app which sits on your task bar. For finding meaning of a word just block the word and press CTRL + Right click on it.

4. Photo Organizer – I love Picasa and am using it for quite sometime now without any issues. http://picasa.google.com/download/

5. Creating / Editing blogposts - http://writer.live.com/

6. Zip Compression utility – I was using Winzip then WinRar but offlate I have started using 7-zip an open source project. Download it here http://www.7-zip.org/download.html

7. Instant Messaging – I was using MSN and Yahoo. But offlate I am using only Gtalk or the Chat feature which comes embedded within Gmail. May be you might want to try out Skype if you love talking more than typing :)

8. Download Manager – I use Free download Manager for quite sometime now. http://www.freedownloadmanager.org/download.htm but one of my friend told me about http://www.orbitdownloader.com/ (which I haven’t tried yet)

9. WinPatrol – I use this to manage my startup programs, services etc., kind of it sits on the system tray alert you to hijackings, malware attacks and critical changes made to your computer without your permission. http://www.winpatrol.com/

10. I do have Microsoft Windows Defender as well.

11. CCleaner is a freeware system optimization, privacy and cleaning tool. It removes unused files from your system - allowing Windows to run faster and freeing up valuable hard disk space. It also cleans traces of your online activities such as your Internet history. Additionally it contains a fully featured registry cleaner. But the best part is that it's fast (normally taking less than a second to run) and contains NO Spyware or Adware! :)

12. Automated Password Manager and Form filler - RoboForm http://www.roboform.com/php/land.php?affid=dnf01&frm=frame10

Along with it I have Microsoft Private Folder also installed in my laptop. I guess Microsoft is not giving it out anymore. Since I have downloaded it sometime in 2006 i still have it working and it helps me have few of movie collections securely :)

Let me know what you guys use or if I miss anything important which one should have in their box.

Technorati Tags:
  
del.icio.us Tags: