Skip to main content

Posts

Showing posts from May, 2007

Termination option in ALTER DATABASE

This is tried on Yukon . Assume that User1 is connected to the database MyDb. User2 executes the following statement: ALTER DATABASE MyDb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE Since Termination option is ROLLBACK IMMEDIATE, it disconnects User1 from database and changes the database to restricted_user mode. Now, let us consider similar scenario. User1 is connected to MyDb. User2 executes the following statement: ALTER DATABASE MyDb SET MULTI_USER WITH ROLLBACK IMMEDIATE This statement fails and returns error as shown below: Msg 1222, Level 16, State 28, Line 1 Lock request time out period exceeded. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed. That means termination option has no effect when database is switching to multi_user mode. As long as users are connected to the database, above statement fails to execute. I’ve no idea about the logic behind this design. Kindly update me if you know.

Pivoting in SQL Server 2000

In one of the discussion forums, one guy asked the following question: Here is the Table: Id SalesDate Sales 1 2 Jan 2006 100 1 13 Jan 2006 200 1 14 Feb 2006 300 2 3 Feb 2006 150 2 4 Feb 2006 200 3 5 Jan 2006 300 3 13 Feb 2006 400 I need the following output: Id Jan Feb Mar Apr 1 300 300 0 0 2 0 350 0 0 3 300 400 0 0 Solution: Let’s create a table CREATE TABLE PivotEx ( Id INT , SalesDate DATETIME , Sales MONEY ) Next, Insert values INSERT INTO PivotEx VALUES ( 1 , '2 Jan 2006' , 100 ) INSERT INTO PivotEx VALUES ( 1 , '13 Jan 2006' , 200 ) INSERT INTO PivotEx VALUES ( 1 , '14 Feb 2006' , 300 ) INSERT INTO PivotEx VALUES ( 2 , '3 Feb 2006' , 150 ) INSERT INTO PivotEx VALUES ( 2 , '4 Feb 2006' , 200 ) INSERT INTO PivotEx VALUES ( 3 , '5 Jan 2006' , 300 ) INSERT INTO PivotEx VALUES ( 3 , '13 Feb 2006' , 400 ) Here is the query: SELECT Id , SUM ( CASE WHEN MONTH ( SalesDate ) = 1 THEN Sales ELSE 0 END