"About Me" Pages

I was going through web to have new way to implement personal profile page. Here I found 60 beautiful and effective About Me pages.

~ Kiran

Pronounce Name Correctly! :)

This one is really a useful site. In our daily project work, we interact with Americans who have unfamiliar names. This site helps us to pronounce their name correctly. "How to say that Name" : http://www.howtosaythatname.com/

I am glad to see my name in the database. http://howtosaythatname.com/component/option,com_mtree/task,search/Itemid,4/searchword,Kiran/

Check Your Hearing! :)

They say that it is common for people who are over 25 years of age to not be able to hear above 15kHz. I tested my hearing. I can hear up to 17Khz. Check yours. :)  http://www.noiseaddicts.com/2009/03/can-you-hear-this-hearing-test/

Audit log Design : Using AFTER INSERT, UPDATE, DELETE Trigger

In my last project, we had to implement Audit log functionality for SQL Server Data Changes. Due to very strict timeline(Project was of 2 months and Out of that Audit functionality was having only 10 days to implement), we were goggling for ready made design for Audit functionality. But our efforts gone into vain. Didn't find any good.

SQL Server 2008 is having in built functionality to capture data changes in the form of Change Data Capture Tool(CDC). We tried to convince the client to use the SQL Server 2008. But they didn't agree.:(. We had to go with SQL Server 2005.

We followed trigger based approach. We created AFTER INSERT, UPDATE, DELETE triggers on the all client tables, and recorded all the changes happened. The design was very simple. Hope this helps you guys.

Features included are:

1. Audit Log SHOULD have turn off & turn on switch.
2. Audit Log MUST capture information like who made the changes, what changes, when the changes occurred.
3. Audit Log SHOULD have rollback facility.
4. Retrieval of Audit details in Paging.
5. Audit retrieval WOULD have following APIs
   • Get all changes
   • Get all today’s changes.
   • Get all changes for period.
   • Get all changes for particular transaction.
   • Get single Audit Log entry for given Id

Block diagram to show entire architecture used for Audit:

Audit Log Table:

CREATE TABLE [Administration].[AuditLog]( [AuditLogID] [bigint] IDENTITY(1,1) NOT NULL, [TransactionID] [bigint] NOT NULL DEFAULT ([dbo].[ufn_GetTransactionID]()), [ApplicationName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT ([dbo].[ufn_GetApplicationName]()), [ObjectID] [int] NOT NULL, [OperationID] [tinyint] NOT NULL, [PerformedBy] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT ([dbo].[ufn_GetContextLogin]()), [PerformedAt] [datetime] NOT NULL DEFAULT (getdate()), [TSQL] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [PrimaryKeyColumn] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [PrimaryKeyValue] [int] NOT NULL, [PreviousValue] [xml] NULL, [NextValue] [xml] NULL

Table Schema:

	Name	Data Type	Description
	ID	bigint	Primary key - AuditLogID
	TransactionID	bigint	Transaction ID used while performing DML query. This column is used to find out all the changes made in single transaction.
	ApplicationName	nvarchar(max)	Application though which SQL query is made.
	ObjectID	int	Table Id – To know on which table the operation happened.
	OperationID	tinyint	1 = Delete 2 = Insert 3 = Update
	PerformedBy	nvarchar(100)	To store who has performed the operation.
	PerformedAt	datetime	Time when operation occurred.
	TSQL	nvarchar(max)	To store exact query which made this AuditLog entry.
	PrimaryKeyColumn	nvarchar(max)	To store primarykey column name of the Table on which operation occurred.
	PrimaryKeyValue	int	Primary key value.
	PreviousValue	XML	PreviousValue for changed row. Note: For insert operation this value is null.
	NextValue	XML	CurrentValue for changed row. Note: For delete operation this value is null.

Stored Procedures:

1. [Administration].[usp_EnableAuditLog]

Description: Enables Audit for database activities.

CREATE PROCEDURE [Administration].[usp_EnableAuditLog] AS BEGIN -- All 3 steps are done in transaction-block. -- 1. Create IUD trigger on all ARIC tables. -- 2. Create cleanup job. -- 3. Set Audit flag to true in database. END

2. [Administration].[usp_DisableAuditLog]

Description: Disables Audit.

CREATE PROCEDURE [Administration].[usp_DisableAuditLog] AS BEGIN -- Both below steps are done in transaction-block. -- 1. Delete all IUD triggers which were used for auditing purpose from all the ARIC tables. -- 2. Delete cleanup job. -- 3. Set Audit flag to false in database. END

3. [Administration].[usp_GetAuditLog]

Description: Retrieve log depending on specified input parameters.

CREATE PROCEDURE [Administration].[usp_GetAuditLog] @TransactionID BIGINT = NULL, @TableName NVARCHAR(100)= NULL, @UserName NVARCHAR(100)= NULL, @StartDate DATETIME = NULL, @EndDate DATETIME = NULL, @PageSize INT = NULL, @PageNumber INT = NULL, @TotalPages INT OUT AS BEGIN -- This Stored procedure is used by below 4 APIs -- a. Get all changes -- b. Get all today’s changes. -- c. Get all changes for period. -- d. Get all changes for particular transaction. -- Retrieves changes entries as per input criteria specified. -- Entries are returned in pages. END

4. [Administration].[usp_GetAuditLogEntry]

Description: Retrieves single log information according to AudiLogID.

CREATE PROCEDURE [Administration].[usp_GetAuditLogEntry] @AuditLogID BIGINT AS BEGIN -- Retrieves single log information according to AudiLogID END

5. [Administration].[usp_Rollback]

Description: Revert the update change occurred.

CREATE PROCEDURE [Administration].[usp_Rollback] @AuditLogID BIGINT AS BEGIN -- Reverts the update change occurred. END

 

Other Audit Log Fields can be found out by using In built SQL Server functions  or DMVs. For example,

TransactionID:

SELECT TRANSACTION_ID FROM SYS.DM_TRAN_CURRENT_TRANSACTION;

ApplicationName:

SELECT program_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID

ObjectID:

Here ObjectID is the tableID in which trigger got fired. We calculated it as follows:

SELECT @ObjectID = parent_id FROM sys.triggers WHERE object_id = @@PROCID;

TSQL:

Here TSQL represents, the query responsible for firing the trigger.

DECLARE @ExecStr NVARCHAR(100);
DECLARE @inputbuffer TABLE  
(
          EventType NVARCHAR(MAX), 
          Parameters INT, 
          EventInfo NVARCHAR(MAX)
)

SET @ExecStr = 'DBCC INPUTBUFFER(' + LTRIM(RTRIM(STR(@@SPID))) + ')'

INSERT INTO @inputbuffer
EXEC (@ExecStr)
SET @TSQL = (SELECT EventInfo FROM @inputbuffer);

OperationID:

SELECT @DeletedCount = COUNT(1) FROM deleted
SELECT @InsertedCount = COUNT(1) FROM inserted
IF @InsertedCount= 0 AND @DeletedCount = 0 
       RETURN;
SET @OperationID = CASE WHEN @DeletedCount > @InsertedCount THEN 1 – DELETE
           WHEN @DeletedCount < @InsertedCount THEN 2 -- INSERT 
           WHEN @DeletedCount = @InsertedCount THEN 3 -- UPDATE
 END

etc…..

~ Kiran

Meeting with Dr. Kakarmath

Last to last Sunday when I met to Dr. Kakarmath, that was one of the scheduled meeting of Giants International Group.

By the way, I am the member of Giants International Group and Dr. Kakarmath is the president of this group for Kalyan Metro. For last several years, the organization is busy in doing social work, such as helping poor but needy and talented students getting free school books and school dresses, tree plantation on open spaces etc. ( I have mentioned only those activities in which i was involved. Actually there are so many to mention that space here will come short.).

Dr. Kakarmath has been my family doctor for last 10 years. So we have developed good relationship between us. I met him after Giants meeting. In a flow of talk, I just asked him about his dispensary and asked him to come out of the daily paperwork for keeping patients information.(I was knowing the fact that he keeps one file per family and whenever any family member visits his dispensary; he keeps paper, scribbled after checking the patient and prescription given to him, inside it. That is the way, he keeps patients information.). I queried the reason behind not using the software to keep this information. In reply to that, Doctor told me that he was aware of the softwares available in the market but all softwares were meant for the hospitals or for the dispensaries having beds for the patients.

When I see the family doctors in my area, all of them still use diaries or the folders similar to Dr. Kakarmath to keep patients information and their last prescription given. I think small doctors are still away from this Software Revolution. I have promised Dr. Kakarmath to help in this case. and have started working on it. I hope the software which I am implementing will come up as handy tool for him and some of his doctor friends.

As a part of the software, I am done with the database design. And really forcing myself to create a very good UI. I am learning WPF but i think, getting mastery in WPF will take some time. So I will start with Windows Forms only. Some of my friends are also having experience in implementing their own softwares.( obviously in different domain.). Their experiences will guide me to frame user friendly and well presented GUI.

I will keep on posting my experiences on my blog.

~ Kiran

Delete All Databases Except System Databases

Many times I require to create databases with different configurations for testing purpose.(This includes databases having special characters in their names, databases with different sizes, databases having data files on different locations. etc). As soon as I finish unit testing of the functionality , I have to delete those many databases manually from SQL Server Management Studio.

Today,Two times I had to delete 15 such databases manually :(. But now no more manual work. I have written SQL script for it :)

USE [master]
GO

DECLARE @database_name NVARCHAR (4000);

-- Collect all user created databases into cursor
DECLARE databases_cursor CURSOR FOR
SELECT name FROM sys.databases as d where d.database_id > 4
OPEN databases_cursor

FETCH NEXT FROM databases_cursor
INTO @database_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Dropping Database ' + @database_name
    DECLARE @cmd NVARCHAR(4000);
    SET @database_name = REPLACE(@database_name,']',']]');
    SET @cmd = 'ALTER DATABASE ['+@database_name+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE ['+@database_name+'];'
    EXECUTE sp_executesql @cmd
    FETCH NEXT FROM databases_cursor
    INTO @database_name
END
CLOSE databases_cursor
DEALLOCATE databases_cursor

KeePass : Password Manager

I have so many passwords with me for every website I visit. Other than that there are Windows Network login, Net Banking, Gmail, Yahoo, MSN, My Office Email passwords. Hussss...the list is endless. It was really difficult task for me to keep remembering all those passwords. Many of the times, I was dependable on 'Forget Password' link to reset my password and set new one. And you will agree me, it is really boredom task.


Thanks to KeePass. This software has really saved me from this trouble. KeePass is a free open source password manager, which helps me to manage my passwords in a secure way. You can put all your passwords in one database, which is locked with one master key. So you only have to remember one single master password to unlock the whole database.

I am currently using it and very much satisfied with it. :)

These features (http://keepass.info/features.html) and screenshots (http://keepass.info/screenshots.html) will help the first time users.

~ Kiran

Search Engine for Videos!

Truveo : I was in search for such site so long. Earlier i used to run around video sharing sites (There are so many. YouTube, MySpace, Google Videos, Metacafe etc) for searching any particular video.

This site does this whole thing for me. This search engine lets me find and play videos from all over the web.[:)]

~ Kiran

Animoto!

There is a lot of fun on the Internet! Animoto is one of them.

This web application turns your pictures into very interesting movie trailers. and you require only 3 steps to get it. Just a) get your images b) get music background and c) provide some details.

The only single problem is you can produce, remix, and share videos freely of 30 second length only. But it is worth to try this out. :)
Here is my first composition.[:D]

10 Minute Mail!

Today I came across really a fascinating website. 10 Minute Mail.

Many a times you need to provide your email address while downloading any software or file and then respond to a confirmation link to prove it. More often you don’t trust the website or you want to avoid bunch of spam getting after that; you end up not downloading the file\software.

This is the one area the website targets. The website provides you a temporary e-mail address. Any e-mails sent to that address will show up automatically on the web page. You can read them, click on links, and even reply to them. The e-mail address will expire after 10 minutes.

~ Kiran

Online photo editor!

PiZap is an online photo editor. You can play with some demo photos or upload your own. You can then add text, stickers, change faces and a whole lot more. Here's an example.

~ Kiran

Cut on your arm!!

Cut anything (lover's name??) you want on your arm!!! Go here