Saturday, April 25, 2009

"With append" - The thing that you have to note in 65 compatibility


If you have your database in 65 compatibility mode, you need to be extra careful, if you add an additional trigger to a table.
Let us take this example,

you have a database named test.
First move the database to 65 compatibility mode with the help of following statement.

sp_dbcmptlevel test,65

Then create a table and name it as  working_table.Now create a trigger for insert action on the table with name as working_table_insert_trigger1.

If you check now the number of triggers on the table with sp_helptrigger working_table, the sql server will show the newly created trigger working_table_insert_trigger1.

And here is the twist, if you wish to add an additional trigger on the table working_Table for the same insert activity, you need to be careful.

let us assume, you proceed normally and create a trigger in the following way,

Create trigger working_table_insert_trigger2
on working_table
for insert
as
begin
       select 1
end

If you check the sqlserver, you first trigger will be lost.

sp_helptrigger working_table will return only the latest trigger.The embarrassing thing is you wont get any warning message when you create the second trigger.

You have to use the append clause if you want to create multiple triggers on a table that is in a database with 65 compatibility mode.

Create trigger working_table_insert_trigger2
on working_table
for insert
with append
as
begin
        select 1
end

If you missed the "with append" option it means you are going to lose all your code in the previous triggers.

Another thing that Microsoft disappoints is, when We modify the triggers that we added later with "with append" option.
You should not use this "with append" option when you alter the trigger. This article may be on an outdate topic but still worth reading and useful.

Saturday, February 28, 2009

SP_WHAT - A New attempt to find the Blocks

Hi buddies,

The following procedure can be used to find whether there are any blocks in the sql server.
The procedure if run in Master database , can be used like any other
system stored procedure from any database.
The output of the proc will contain the following

SPID OF RUNNING APPLICATION
APPLICATION NAME
EXECUTING DATA
BLOCKED (Y/N)
BLOCKING SPID
BLOCKING APPLICATION
EXECUTING DATA (BLOCKING APPLICATION)


The Procedure's target version is SQL SERVER 2000.Stay tuned for more details and enhancements.




CREATE PROC SP_WHAT
AS
BEGIN
/*CODE WRITTEN BY
PAARTHASARATHY K
http://InayathilEnthanGeetham.blogspot.com/
http://PaarthasarathyK.blogspot.com/

*/
/*TO FIND THE SPID'S THAT ARE ACTIVE AND WHAT THEY ARE EXECUTING*/
CREATE TABLE #T1
(
A SYSNAME,
B SYSNAME,
[INPUT_BUFFER] NVARCHAR(1200),[SPID] INT NULL
);
DECLARE @A NVARCHAR(400),@B INT

DECLARE SP_IDS CURSOR
FOR SELECT SPID FROM SYSPROCESSES
WHERE STATUS ='RUNNABLE'
--STATUS <> 'SLEEPING'AND
--STATUS <> 'BACKGROUND'
AND SPID <> @@SPID
AND PROGRAM_NAME <> 'SQL PROFILER'
UNION ALL
SELECT SPID FROM SYSPROCESSES
WHERE BLOCKED <>0


OPEN SP_IDS
FETCH NEXT FROM SP_IDS INTO @B

WHILE @@FETCH_STATUS =0
BEGIN

SET @A = 'DBCC INPUTBUFFER('+RTRIM(@B)+')WITH NO_INFOMSGS;';


INSERT #T1(A,B,[INPUT_BUFFER]) EXEC SP_EXECUTESQL @A

UPDATE #T1 SET [SPID] = @B WHERE [SPID] IS NULL
FETCH NEXT FROM SP_IDS INTO @B
END


CLOSE SP_IDS
DEALLOCATE SP_IDS

CREATE TABLE #T2
(
SPID INT,
PROGRAM_NAME SYSNAME,
INPUT_BUFFER NVARCHAR(1200),
BLOCKED VARCHAR(2),
BLOCKING_PROCESS VARCHAR(10),
PROGRAM_NAME1 SYSNAME,
INPUT_BUFFER1 NVARCHAR(1200)
)


INSERT INTO #T2
SELECT A.SPID,B.PROGRAM_NAME, A.INPUT_BUFFER ,
CASE B.BLOCKED WHEN 0 THEN 'N' ELSE 'Y' END ,
CASE WHEN B.BLOCKED <>0 THEN CONVERT(VARCHAR(10),B.BLOCKED) ELSE 'NO BLOCK' END ,
'',''
FROM #T1 A,
MASTER..SYSPROCESSES B
WHERE A.SPID = B.SPID


UPDATE A
SET A.PROGRAM_NAME1 = B.PROGRAM_NAME
FROM #T2 A ,
SYSPROCESSES B
WHERE A.BLOCKING_PROCESS = B.SPID
AND A.BLOCKED = 'Y'



CREATE TABLE #T3
(
A SYSNAME,
B SYSNAME,
[INPUT_BUFFER] NVARCHAR(1200),[SPID] INT NULL
);

DECLARE CURSOR_2 CURSOR
FOR SELECT BLOCKING_PROCESS FROM #T2
WHERE BLOCKED ='Y'


OPEN CURSOR_2
FETCH NEXT FROM CURSOR_2 INTO @B

WHILE @@FETCH_STATUS =0
BEGIN

SET @A = 'DBCC INPUTBUFFER('+RTRIM(@B)+')WITH NO_INFOMSGS;';


INSERT #T3(A,B,[INPUT_BUFFER]) EXEC SP_EXECUTESQL @A

UPDATE #T3 SET [SPID] = @B WHERE [SPID] IS NULL
FETCH NEXT FROM CURSOR_2 INTO @B
END

CLOSE CURSOR_2
DEALLOCATE CURSOR_2
/*
--DEBUGG

SELECT * FROM #T2 A
SELECT * FROM #T3 B
--DEBUGG
*/
UPDATE A
SET A.INPUT_BUFFER1 = B.INPUT_BUFFER
FROM #T2 A, #T3 B
WHERE A.BLOCKING_PROCESS <> 'NO BLOCK' AND
A.BLOCKING_PROCESS = CONVERT (VARCHAR(10),B.SPID)

SELECT DISTINCT LTRIM(RTRIM(SPID)) 'SPID'
,LTRIM(RTRIM(PROGRAM_NAME)) 'APPLICATION NAME',
LTRIM(RTRIM(INPUT_BUFFER)) 'EXECUTING DATA',
LTRIM(RTRIM(BLOCKED)) 'BLOCKED (Y/N)',
LTRIM(RTRIM(BLOCKING_PROCESS)) 'BLOCKING SPID' ,
LTRIM(RTRIM(PROGRAM_NAME1)) 'BLOCKING APPLICATION',
LTRIM(RTRIM(INPUT_BUFFER1)) 'EXECUTING DATA (BLOCKING APPLICATION)' FROM #T2


DROP TABLE #T1
DROP TABLE #T2
DROP TABLE #T3

END

Sunday, February 1, 2009

Life is Beautiful – A Good Movie worth watching

Life is Beautiful – A Good Movie worth watching

Cast:

                 Roberto Benigni, Nicoletta Braschi, Giorgio Cantarini 

Awards:

                Grand Prix, Cannes

                Academy Award for Best Foreign Language Film

                European Film Award for Best European Film

 

Story

The movie was the story of a Jewish Italian, who spends his days with his son in a Nazi concentration camp. Roberto makes us laugh out loud in the first half and leave us with sad tears in eyes in the end. The first half of the movie is a whimsical, romantic comedy. He steals her Lover (Dora, she is not a Jew), at her engagement function from her rude and loud fiancé. Then they marry and have a son called Joshua. The story takes a turning point at Joshua's 5th birthday. Roberto and his son were taken to the Nazi Concentration camp. Dora too joins them at the camp, but stays separately. To keep up Joshua's spirits, Roberto convinces him that the camp is just a game in which the first person to get thousand points will win a tank. Roberto tells Joshua if he cries, or complains that he is hungry or want his mother, he lose points, while quiet boys who hide from the camp guards earn points. Roberto ables to manage the story to his son until the chaos caused by the American troops. At this juncture he asks his son to hide in a box but Roberto was caught in to the trap while trying to find Dora. Roberto was shot dead, but succeed in making his son laugh by imitating the Nazi guard. At last the American troop enters the camp and liberates everyone. Joshua was reunited with his mother and manages a ride in a Tank.

Satyam Saga

January 7 2009 was a shocking day not only in India, but also across the world, when Ramalinga Raju admitted cooking account books and a fraud of 8000 crore Indian rupees. It was the country's biggest corporate fraud involving about Rs 8,000 crore, Satyam was hurtling towards disaster, following the shocking disclosure of accounts fudging by its founder Ramalinga Raju, who then quit as chairman - leaving an uncertain future for the company and its 53,000 employees.

Certain questions rise on reading his letter to SEBI and the board of directors. He had said "That neither myself, nor the Managing Director (including our spouses) sold any shares in the last eight years -- excepting for a small proportion declared and sold for philanthropic purpose" and "hat neither me, nor the Managing Director took even one rupee/dollar from the company and have not benefitted in financial terms on account of the inflated results". If this is what actually happened, then why to fudge the books and show profits in the market. But reports show that Raju created 300 companies to divert funds.  According to the government, Satyam Computer Services’ founder Ramalinga Raju created a network of about 300 companies and diverted funds from one company to another in a complex but carefully planned process. There has been an issue of siphoning of funds. The investors money where siphoned and they where cheated. Investigation showed that the company really employed only 44000 employees and the employee’s salary account were used to siphon the funds.