Separate your work and leasure

Separate your work and private laptop!!
Leave your work at the right place (OFFICE)

Categories: Life and all about it

Life Update 2011

December 26, 2011 Leave a comment

Since the last I post my last post on March 2010, I’ve moved to different kind of job, I’ve moved from Jakarta to Medan and now Surabaya.

Life is good..

Before i dedicated this blog to my interest in IT, currently I am not as update as before. So you’ll see that I am going to write random things.. Such as life, my thought, or anything cross my mind

Categories: Life and all about it Tags:

get user who altered stored procedure from server

March 25, 2010 1 comment

First I create a table for logging purpose.

CREATE table Logtable
(username varchar(2000), Modify_Date datetime, ProcName varchar(2000))

Then I create the Trigger

CREATE TRIGGER safety
ON DATABASE
FOR ALTER_PROCEDURE
AS
declare @name varchar(200)

--look for the object with stored procedure type that has the last modify time.
set @name = (select  top 1 Name from sys.all_objects
where type_desc like 'SQL_STORED_PROCEDURE'
order by modify_date desc)

--insert the information u need to log table
INSERT INTO Logtable (UserName, Modify_Date, ProcName)
select suser_name(), getdate(), @name

create  a dummy proc for testing and alter it for testing the trigger

create procedure JustAProc
as
select 'JustAProc'
go

ALTER procedure JustAProc
AS BEGIN
PRINT 'X'
END

see the content of the log

select * from Logtable
username Modify_Date ProcName
Melissa 3/24/10 8:17 PM JustAProc

and just drop it after you finish play with it

DROP TRIGGER safety
ON DATABASE;
go
DROP PROCEDURE JustAProc
GO
Drop Table Logtable

The Highest Score in a Class

Mrs. Jones is an admin in an elementry school called ‘SD Suka Tampil’. At the end of the school every six months she got to give report to the head master about students with the best score in every class. She has a table. Just a very simple table called StudentScore.

The structure is like

Name Class Score
Mina 1a 99
Arni 1b 98
Cukiyem 1a 55
Alfaredo 1c 78
Gilda ….

Aa.. just head to the query then to make this shorter.

CREATE TABLE #StudentScore (Name varchar(20), Class Char(2), Score int)

insert into #StudentScore (Name, Class, Score)
select 'Zee', '1a', 99 UNION ALL
select 'Arni', '1b', 98 UNION ALL
select 'Cukiyem', '1a', 55 UNION ALL
select 'Alfaredo', '1c', 78 UNION ALL
select 'Gilda', '1b', 88 UNION ALL
select 'Moore', '1a', 87 UNION ALL
select 'RiyanT', '1a', 90 UNION ALL
select 'Melissa', '1b', 98 UNION ALL
select 'Ninoood', '1c', 95

select * from #StudentScore
Name Class Score
Zee 1a 99
Arni 1b 98
Cukiyem 1a 55
Alfaredo 1c 78
Gilda 1b 88
Moore 1a 87
RiyanT 1a 90
Melissa 1b 98
Ninoood 1c 95

Ahaha that easy just use max

select Name, class, MAX(score) as 'The Highest'
from #StudentScore
group by Name, Class
Name class The Highest
Cukiyem 1a 55
Moore 1a 87
RiyanT 1a 90
Zee 1a 99
Arni 1b 98
Gilda 1b 88
Melissa 1b 98
Alfaredo 1c 78
Ninoood 1c 95

Why why why it don’t produce the right result? hehe cause you group it by the name and class. Hmm…
Let’s Find the highest first in every Class

select Class,  MAX(score) AS Score
from #StudentScore
group by Class
Class Score
1a 99
1b 98
1c 95

Ahaaaa….

SELECT A.name, A.Class, A.Score as 'The Highest'
from #StudentScore A
JOIN (select Class,  MAX(score) AS Score
 from #StudentScore
 group by Class
 ) B ON A.Class = B.Class AND A.Score = B.Score
name Class The Highest
Ninoood 1c 95
Arni 1b 98
Melissa 1b 98
Zee 1a 99

Or use CTE

;WITH CTE AS (
SELECT Name, Class, Score,
ROW_NUMBER() OVER (PARTITION BY Class Order by Score DESC) AS RN
FROM #StudentScore
)SELECT Name, Class, Score FROM CTE
 WHERE RN = 1;
Name Class Score
Zee 1a 99
Arni 1b 98
Ninoood 1c 95

wait why we can see melissa in class 1b?  please see below

SELECT Name, Class, Score,
ROW_NUMBER() OVER (PARTITION BY Class Order by Score DESC) AS RN
FROM #StudentScore
Name Class Score RN
Zee 1a 99 1
RiyanT 1a 90 2
Moore 1a 87 3
Cukiyem 1a 55 4
Arni 1b 98 1
Melissa 1b 98 2
Gilda 1b 88 3
Ninoood 1c 95 1
Alfaredo 1c 78 2

why melissa is grade as number 2 in class 1b? because row number sort the score based on score
and when they found two same score it’ll sort again by name. And A comes first.
so how to have this with CTE. Use Dense Rank!

SELECT Name, Class, Score,
Dense_rank() OVER (PARTITION BY Class Order by Score DESC) AS RN
FROM #StudentScore
Name Class Score RN
Zee 1a 99 1
RiyanT 1a 90 2
Moore 1a 87 3
Cukiyem 1a 55 4
Arni 1b 98 1
Melissa 1b 98 1
Gilda 1b 88 2
Ninoood 1c 95 1
Alfaredo 1c 78 2

Now we have Melissa and Arni in the same position
tadaaaa.

;WITH CTE AS (
SELECT Name, Class, Score,
Dense_rank() OVER (PARTITION BY Class Order by Score DESC) AS RN
FROM #StudentScore
)SELECT Name, Class, Score FROM CTE
 WHERE RN = 1;

I present to You…

Report For Miss Jones..

Name Class Score
Zee 1a 99
Arni 1b 98
Melissa 1b 98
Ninoood 1c 95

Insert to another table after inserting to a table

This is quite an often question asked by people.
There are 2 options. Create a stored procedure with 2 steps
something like

This is quite an often question asked by people.
There are 2 options. Create a stored procedure with 2 steps something like. It’ll insert the last value inserted to table A to table B

CREATE PROCEDURE InsertInsert

AS BEGIN

declare @id int
Insert to table A
select 'A', 'B'

--take the last id inserted
SET @id = @@identity

GO

Insert to table B
Select Col1, Col2 from Table A
where ID = @id

END

or you can use trigger.

CREATE TRIGGER InsertToOtherTable
ON TableSource
AFTER INSERT
AS
INSERT INTO TableB
SELECT Col1, Col2
FROM INSERTED

BOL

Query to know the Object Level Permissions for all users of a Database

March 24, 2010 1 comment

sometimes we need to know what user is allowed to do what then here’s the query to know the level permission of all user.

select pr.name as LoginName, state_desc,  permission_name as PermissionTo , s.name as schemaName, o.Name
from sys.database_permissions p
inner join  sys.objects o on  p.major_id =o.object_id
inner join sys.schemas s on o.schema_id=s.schema_id
inner join sys.database_principals pr on grantee_principal_id=pr.principal_id

LoginName  state_desc       PermissionTo  schemaName           Name
User1              GRANT            EXECUTE            dbo                       USP_A
User2              GRANT            EXECUTE             dbo                      USP_B

Insert into Identity Column

January 22, 2010 1 comment

You create a table with identity column, You accidentally delete one of its row instead update it.. If you insert new column it’ll only increment to the last id inserted+1. You need it to be a specific id. So you try to insert it with the id specified but you cannot do that you get error.. So how to insert column to identity column?

CREATE TABLE #Customer(
ID INT IDENTITY NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL
)
GO
-- Inserting values into #Customer table.
INSERT INTO #Customer(Name) VALUES ('Jake')
INSERT INTO #Customer(Name) VALUES ('Suzan')
INSERT INTO #Customer(Name) VALUES ('Marry')
INSERT INTO #Customer(Name) VALUES ('Sally')
GO

-- Accidentaly delete Marry (oh nooo I just want to add Marry's Maiden name!?!?!)
DELETE #Customer
WHERE Name = 'Marry'
GO

SELECT *
FROM #Customer
GO

-- Try to insert Marry again to ID 3;
-- got an error.
INSERT INTO #Customer (ID, Name) VALUES (3, 'Marry Marc')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT #Customer ON
GO

-- Try to insert an explicit ID value of 3.
INSERT INTO #Customer (ID, Name) VALUES (3, 'Marry Marc')
GO

SELECT *
FROM #Customer
GO

--Don't forget to set the identity insert back off. :D
SET IDENTITY_INSERT #Customer OFF
GO

-- Drop Customer table.
DROP TABLE #Customer
GO

Huraaah!!

How to convert time format to minutes/duration

January 22, 2010 Leave a comment

Have you ever face trouble when you need to convert values like ’00:45:00.00′ to 45 minutes or ’01:23:00.000′ to 83 minutes?

How do you do it?

here’s the input :

declare @time datetime
set @time = '01:45:00.000'

This is the first method, the idea is using datepart function to get the hour part and minute part of the data and multiply the hour by 60 then add it with the minute part.

select datepart(hour,@time) AS hour, datepart(MINUTE,@time) AS min,
datepart(hour,@time)*60 +datepart(MINUTE,@time) As Duration

the 2nd method, I like this one better cause it’s simpler. The idea is looking the difference in minutes from 00:00:00.000 represented by 0 to the data..

Select DATEDIFF(minute, 0, @time) As Duration

error :Msg 8133, Level 16, State 1, Line 7 ; None of the result expressions in a CASE specification can be NULL.

January 15, 2010 Leave a comment

above error is happen because the SQl is confuse what is the datatype for the result expression in the case statement..

here’s simple script to produce the same error and the solutiooooon (at least it worked for me :D )

declare @i int
set @i = 3

declare @x int
set @x = 6

Select case when @i >8  OR @i <@x
then NULL
END AS 'Derived Column'
----error :Msg 8133, Level 16, State 1, Line 7
----None of the result expressions in a CASE specification can be NULL.

ALTERNATIVE :

Select case when @i >8  OR @i <@x
then 'Something' --different value
END AS 'Derived Column'

Why It Worked  : because you assign one of the value to varchar so sql thought ok the null is varchar, follow the other value)
second alternative :

Select case when @i >8  OR @i <@x
then NULL
Else 'X' -- put dummy thingy
END AS 'Derived Column'

Why it worked : It Worked because you don’t even try to assign it to NULL value

But but I need to assign it to NULL and no other value and I don’t want and else..

AHAAA…

Select case when @i >8  OR @i <@x
then cast(NULL as int)
END AS 'Derived Column'

why why ? here you cast the result to a datatype (here i cast it to int, sql is no longer confuse)..

No nooo.. I want assign it to a varchar then so be it.. :D

Select case when @i >8  OR @i <@x
 then convert(varchar(10),NULL)
 END AS 'Derived Column'
Categories: Uncategorized Tags: ,

DateTime to Varchar to Varbinary to Varchar to DateTime

January 14, 2010 Leave a comment

My first post on 2010.. Yay!! The First is convert between datatypes. The Idea is convert from datetime to varchar to binary to varchar again to datetime .. :D

DECLARE @DateWithDateTime DATETIME
SET @DateWithDateTime = '2010-09-08 00:00:00:00'

DECLARE @Date varchar(200)
SET @Date = (SELECT CONVERT(VARCHAR(20), @DateWithDateTime, 120))

SELECT CONVERT(VARBINARY(100), @Date)AS theVarbinary
--theVarbinary
--0x323031302D30392D30382030303A30303A30303A3030

SELECT convert(VARCHAR(200),CONVERT(VARBINARY(100), @Date)) AS BackTodate
--BackTodate
--2010-09-08 00:00:00:00

hey where is back to datetime.. hahaha it’s just to complete the cycle(feels good)..

Follow

Get every new post delivered to your Inbox.