Friday 27 January 2012

Using NHibernate to format your SQL

Just did this in the immediate window from break point set in Abstract Batcher, GetSQL method in the NHibernate source code

image

sql.ToString()

"exec [dbo].[GetProjectComments] ?"

SqlClientDriver driver= new SqlClientDriver();

SqlStringFormatter formatter = new SqlStringFormatter(driver);

sql.Visit(formatter)

Expression has been evaluated and has no value

formatter.GetFormattedText()

"exec [dbo].[GetProjectComments] @p0"

Thinking of using this to write a custom little app on the side to make all my SQL look nice again. I know SQL Prompt already does this nicely, but don't have a license for that right now, so this is a nice free alternative I guess.

Thursday 26 January 2012

Create Bootable Windows 7 USB Flash drive

http://www.intowindows.com/bootable-usb/

1. Insert your USB (4GB+ preferable) stick to the system and backup all the data from the USB as we are going to format the USB to make it as bootable.

2. Open elevated Command Prompt. To do this, type in CMD in Start menu search field and hit Ctrl + Shift + Enter. Alternatively, navigate to Start > All programs >Accessories > right click on Command Prompt and select run as administrator.

3. When the Command Prompt opens, enter the following command:

DISKPART and hit enter.

LIST DISK and hit enter.

Once you enter the LIST DISK command, it will show the disk number of your USB drive. In the below image my USB drive disk no is Disk 1.

4. In this step you need to enter all the below commands one by one and hit enter. As these commands are self explanatory, you can easily guess what these commands do.

SELECT DISK 1 (Replace DISK 1 with your disk number)

CLEAN

CREATE PARTITION PRIMARY

SELECT PARTITION 1

ACTIVE

FORMAT FS=NTFS

(Format process may take few seconds)

ASSIGN

EXIT

Bootable USB

Don’t close the command prompt as we need to execute one more command at the next step. Just minimize it.

5. Insert your Windows DVD in the optical drive and note down the drive letter of the optical drive and USB media. Here I use “D” as my optical (DVD) drive letter and “H” as my USB drive letter.

6. Go back to command prompt and execute the following commands:

D:CD BOOT and hit enter. Where “D” is your DVD drive letter.

CD BOOT and hit enter to see the below message.

BOOTSECT.EXE/NT60 H:

(Where “H” is your USB drive letter)

USB Bootable

7. Copy Windows DVD contents to USB.

You are done with your bootable USB. You can now use this bootable USB as bootable DVD on any computer that comes with USB boot feature (most of the current motherboards support this feature).

Note that this bootable USB guide will not work if you are trying to make a bootable USB on XP computer.

Tuesday 24 January 2012

Who dropped my SQL Database?

Today we found that one of our databases had mistakenly been dropped from our SQL server. To find out who you can use one of these two queries which look at the SQL log file trace...

DECLARE @path varchar(256)

SELECT @path = path
FROM sys.traces
where id = 1

SELECT *
FROM fn_trace_gettable(@path, 1)
where databasename = 'myDbName'
order by starttime desc

To prove it without a shadow of a doubt, you can use this more detailed query...

declare @databaseID int = 5 -- TODO: Figure out your database id here

;With cteObjectTypes AS
      (
      SELECT
            TSV.trace_event_id,
            TSV.subclass_name,
            TSV.subclass_value
      FROM
            sys.trace_subclass_values AS TSV JOIN
            sys.trace_columns AS TC ON
                  TSV.trace_column_id = TC.trace_column_id
      WHERE
            TC.[name] = 'ObjectType'
      ),
      cteEventSubClasses AS
      (
      SELECT
            TSV.trace_event_id,
            TSV.subclass_name,
            TSV.subclass_value
      FROM
            sys.trace_subclass_values AS TSV JOIN
            sys.trace_columns AS TC ON
                  TSV.trace_column_id = TC.trace_column_id
      WHERE
            TC.[name] = 'EventSubClass'
      )
SELECT
    TE.[name],
    I.ApplicationName,
      I.BigintData1,
      I.ClientProcessID,
      I.ColumnPermissions,
      I.DatabaseID,
      I.DatabaseName,
      I.DBUserName,
      I.Duration,
      I.EndTime,
      I.Error,
      I.EventSequence,
      Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,
      I.FileName,
      I.HostName,
      I.IndexID,
      I.IntegerData,
      I.IsSystem,
      I.LineNumber,
      I.LoginName,
      I.LoginSid,
      I.NestLevel,
      I.NTDomainName,
      I.NTUserName,
      I.ObjectID,
      I.ObjectID2,
      I.ObjectName,
      Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,
      I.OwnerName,
      I.ParentName,
      I.Permissions,
      I.RequestID,
      I.RoleName,
      I.ServerName,
      I.SessionLoginName,
      I.Severity,
      I.SPID,
      I.StartTime,
      I.State,
      I.Success,
      I.TargetLoginName,
      I.TargetLoginSid,
      I.TargetUserName,
      I.TextData,
      I.TransactionID,
      I.Type,
      I.XactSequence
FROM
    sys.traces T CROSS Apply
    sys.fn_trace_gettable(T.[path], T.max_files) I JOIN
    sys.trace_events AS TE ON
            I.EventClass = TE.trace_event_id LEFT JOIN
      cteEventSubClasses AS ESC ON
            TE.trace_event_id = ESC.trace_event_id And
            I.EventSubClass = ESC.subclass_value LEFT JOIN
      cteObjectTypes AS OT ON
            TE.trace_event_id = OT.trace_event_id AND
            I.ObjectType = OT.subclass_value
WHERE
    T.is_default = 1
    --TE.NAME = 'Object:Deleted'
    --and databaseid = @databaseID
    order by starttime desc

Safe to say with the latter query the evidence was pretty damning for one individual on our team Sad smile

Wednesday 11 January 2012

Adding resource to a project does not necessarily mean increased velocity

Recently got pointed in this direction to help explain this to the powers that be at the client where I'm working currently.

http://en.wikipedia.org/wiki/The_Mythical_Man-Month

I was originally referring to a graph of project velocity that Ken Schwaber (http://courses.scrum.org/about/ken-schwaber) drew years ago whilst I was attending his certified scrum master course, which looked somewhat like this... (Note: the flat line is the loss in velocity period where the existing expert resource has to down tools to help bring up to speed the new resources added to the project).

clip_image001

How to find the last interactive logons in Windows using PowerShell

Use the following powershell script to find the last users to login to a box since a given date, in this case the 21st April 2022 at 12pm un...