Skip to main content

Posts

Showing posts from May, 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: SQL Server

‘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

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

Microsoft Surface Computing …

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

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: HOST file

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 t

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,

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 //