Author Archives: nakulvachhrajani

About nakulvachhrajani

Nakul Vachhrajani is a SQL Server MCTS, TOGAF certified Technical Architect with Capgemini having a total IT experience of more than 12 years. Nakul is an active blogger, and has been a guest columnist for and Nakul presented a webcast on the “Underappreciated Features of Microsoft SQL Server” at the Microsoft Virtual Tech Days Exclusive Webcast series (May 02-06, 2011) on May 06, 2011. He is also the author of a research paper on Database upgrade methodologies, which was published in a CSI journal, published nationwide. In addition to his passion about SQL Server, Nakul also contributes to the academia out of personal interest. He visits various colleges and universities as an external faculty to judge project activities being carried out by the students. Disclaimer: The opinions expressed herein are his own personal opinions and do not represent his employer’s view in anyway.

Import Event Viewer Logs into Excel

#0414 – Analyzing Event Viewer Logs in Excel

When troubleshooting issues, the Event Viewer is one of the most handy of all tools. Assuming that appropriate coding practices were used during application development, the Event Viewer contains a log of most problems – in the system, in the configuration or in the application code.

The only problem is analyzing the Event Viewer logs when you have a thousand events. It becomes extremely difficult to try and answer questions like the following while going through events serially:

  1. Events logged by type for each source
  2. Events by severity
  3. Events by category
  4. And many more such analytical questions…

These analytical requirements are best achieved with tools like Microsoft Excel. And so, I went about analyzing Event Viewer logs in Microsoft Excel in just 2 steps.

Step #1: Export the Event Viewer Logs to XML

  1. Once the Event Viewer is launched, navigate to the Event Log to be evaluated
  2. Right-click on the Event Log and choose “Save All Events As” option
  3. In the Save As dialog, choose to save the Events as an XML file
    • If asked to save display information, you can choose not to store any or choose a language of your choice

And that’s it – it completes the 1st step!

Screenshot showing how to Save the Event Viewer Logs
Save the Event Viewer Logs
Screenshot showing how to save the Event Viewer Logs as an XML file
Choose to save the Event Viewer Logs as an XML file

Step #2: Import the XML file into Excel

  1. Launch Microsoft Excel
  2. In the File -> Open dialog, choose to search files of “XML” type
  3. Select the exported Event Viewer Log file
  4. In the Import Options, you can choose to import as an “XML Table”
    • Excel will prompt to create/determine the XML schema automatically. It’s okay to allow Excel to do so

And that’s it – the Event Viewer Logs are now in Excel and you can use all native Excel capabilities (sort, filter, pivot and so on).

Choose to import the Event Viewer Logs into Excel as an XML table
Import the Event Viewer Logs as an XML table
Image showing the successfully imported Event Viewer data into Microsoft Excel
Event Viewer Logs successfully imported into Excel

I do hope you found this tip helpful. If you have more such thoughts and ideas, drop in a line in the Comments section below.

Until we meet next time,

Be courteous. Drive responsibly.

Output of the sp_help command showing negative signs for a few columns.

#0413 – SQL Server – Interview Question – Why are some columns displayed with a negative sign in sp_help?

One of the first things I do when I start work on a new database is to use “sp_help” to go through each table and study their structure. I recently noticed something that would make an interesting interview question.

Here’s what I saw during my study.

Output of the sp_help command showing negative signs for a few columns.

Output of the sp_help command

The interview question that came to my mind was:

Why is there a negative “(-)” sign in the sp_help output?

The answer

The answer is quite simple – the negative sign simply indicates the columns are in a different sort order. By default, when a sort order is not specified for a column on an index, Microsoft SQL Server arranges it in ascending order. When we explicitly specify a descending sort order of the column on the index, it will be reported with the negative “(-)” sign.

Here is the script I used to capture the screenshot seen above:

USE tempdb;
--Safety Check
IF OBJECT_ID('tempdb..#StudentSubject','U') IS NOT NULL
    DROP TABLE #StudentSubject;

--Create a temporary table to demonstrate the point under discussion
CREATE TABLE #StudentSubject 
    (StudentId          INT          NOT NULL,
     SubjectId          INT          NOT NULL,
     DayNumber          TINYINT      NOT NULL,
     SequenceNumber     TINYINT      NOT NULL,
     IsCancelled        BIT          NOT NULL 
                        CONSTRAINT df_StudentSubjectIsCancelled DEFAULT (0),
     Remarks            VARCHAR(255)     NULL,
     CONSTRAINT pk_StudentSubject 
                PRIMARY KEY CLUSTERED (StudentId      ASC,
                                       SubjectId      ASC,
                                       DayNumber      DESC,
                                       SequenceNumber DESC

--Notice the DESC keyword against the DayNumber & SequenceNumber columns
--These columns will be reported in index with negative values
sp_help '#StudentSubject';

IF OBJECT_ID('tempdb..#StudentSubject','U') IS NOT NULL
    DROP TABLE #StudentSubject;

Until we meet next time,

Be courteous. Drive responsibly.