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 SQLAuthority.com and SQLServerCentral.com. 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;
GO
--Safety Check
IF OBJECT_ID('tempdb..#StudentSubject','U') IS NOT NULL
BEGIN
    DROP TABLE #StudentSubject;
END
GO

--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
                                      )
    );
GO

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

--Cleanup
IF OBJECT_ID('tempdb..#StudentSubject','U') IS NOT NULL
BEGIN
    DROP TABLE #StudentSubject;
END
GO

Until we meet next time,

Be courteous. Drive responsibly.

#0412 – SQL Server – SSIS – Error – The value type (__ComObject) can only be converted to variables of type Object. Variables may not change type during execution.


Recently, we were manipulating a string in an “Execute SQL” task inside a SSIS package, when we ran into the following sequence of errors.

[Execute SQL Task] Error: The value type (__ComObject) can only be converted to variables of type Object.
[Execute SQL Task] Error: An error occurred while assigning a value to variable "MyStringVariable": "The type of the value (DBNull) being assigned to variable "User::MyStringVariable" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.".
Error: The type of the value (DBNull) being assigned to variable "User::MyStringVariable" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.

The Execute SQL was similar to something that we had done hundreds of times before, and therefore we were stumped by the error. I found the root cause interesting and hence wanted to write about it right away.

The Test Setup

Before we go ahead, allow me to walk through the sample SSIS package which we used to reproduce the issue. As I mentioned, it is a simple SSIS package with a single “Execute SQL Task”.

0412_01_SSISExecuteSQLTask

The Execute SQL task in the sample SSIS package

The “Execute SQL” task simply executes a T-SQL statement that returns a single-row result set and sets a package variable of type “string“.

DECLARE @myVariable VARCHAR(MAX);

SET @myVariable = 'SQLTwins';

SELECT @myVariable AS myVariable;

0412_02_SSISVariable

User Variable of type “string” in the test package

0412_03_SSISExecuteSQLDetails

Execute SQL task details showing sample T-SQL script

0412_04_SSISResultSetVariableMapping

Variable Mapping in the Execute SQL Task

When we execute this SSIS package, it fails with the error referenced above.

0412_05_ExecuteSQLFailure

Failed Execute SQL Task

0412_06_ExecuteSQLFailureDetails

Execute SQL Task Failure Details

The Solution

The solution was right there in our faces, but we failed to notice it for a while. If we read the error message carefully, we can isolate the following points:

  • The data-type of the variable from the Result Set output of the Execute SQL task is different from the data-type of the target user variable
  • SSIS detects this as an attempt to change the data-type, which is not allowed because variables types are strict unless defined as an “object”

Based on this, we set about looking at differences between the single-row result set and the SSIS user variable of type “string”. We soon realized that the result set was returning a VARCHAR(MAX).

It appears that the (MAX) was causing problems in the SSIS engine. As soon as we changed it to a fixed-length variable the package worked as expected.

DECLARE @myVariable VARCHAR(8000);

SET @myVariable = 'SQLTwins';

SELECT @myVariable AS myVariable;

0412_07_ExecuteSQLSuccess

Successful execution of Execute SQL after changing to a fixed-length data-type

Hope this little tip helps in your development efforts someday.

Until we meet next time,

Be courteous. Drive responsibly.

 

SSDT 15.5.2 for Visual Studio 2017 Installation Error: 0x80072f76

#0411 – SQL Server – SSDT 15.5.2 for Visual Studio 2017 – Installation failed with error 0x80072f76: Failed to acquire payload


I was recently building up an all-in-one development environment for a project and ran into an unexpected error. I had already installed Microsoft Visual Studio 2017 and attempted to install SQL Server Data Tools (SSDT).

The SSDT 15.5.2 for Visual Studio 2017 failed to install with the following error.

SSDT 15.5.2 for Visual Studio 2017 Installation Error: 0x80072f76

SSDT 15.5.2 for Visual Studio 2017 Installation Error: 0x80072f76

Upon studying the error log file, I found the following sequence of unexpected entries:

Acquiring package: Microsoft.DataTools.AnalysisServices, payload: pay98911873C1CF2F7FF48824555D2B0337, download from: https://go.microsoft.com/fwlink/?linkid=866936
Error 0x80072f08: Failed to send request to URL: https://go.microsoft.com/fwlink/?linkid=866936, trying to process HTTP status code anyway.
Error 0x80072f76: Failed attempt to download URL: 'https://go.microsoft.com/fwlink/?linkid=866936' to: 'C:\Users\sqltwins\AppData\Local\Temp\2\{5C5CD709-A276-454C-88E3-0E939CB80B0E}\pay98911873C1CF2F7FF48824555D2B0337'
Error 0x80072f76: Failed to acquire payload from: 'https://go.microsoft.com/fwlink/?linkid=866936' to working path: 'C:\Users\sqltwins\AppData\Local\Temp\2\{5C5CD709-A276-454C-88E3-0E939CB80B0E}\pay98911873C1CF2F7FF48824555D2B0337'
Failed to acquire payload: pay98911873C1CF2F7FF48824555D2B0337 to working path: C:\Users\sqltwins\AppData\Local\Temp\2\{5C5CD709-A276-454C-88E3-0E939CB80B0E}\pay98911873C1CF2F7FF48824555D2B0337, error: 0x80072f76.
MainViewModel.OnPackageAction: Install CompletedDownload for package SQL Server Analysis Services (id: Microsoft.DataTools.AnalysisServices)
Error 0x80072f76: Failed while caching, aborting execution.

From the error log entries, it is clear that the installer program was unable to access a particular URL in order to download the respective installer components.

So, I took the URL “https://go.microsoft.com/fwlink/?linkid=866936”, pasted it in the address bar of a browser and immediately ran into a problem:

Your current security settings do not allow this file to be downloaded.

Enhanced Security Configuration (ESC) preventing file downloads

Enhanced Security Configuration (ESC) preventing file downloads

This clearly indicates that the Internet Explorer Enhanced Security Configuration (IE-ESC) was preventing the download and in-turn resulting into the error.

Solution

I immediately added microsoft.com to the “trusted sites” zone and restarted the installer. This time, the installer completed successfully! (One may suggest to disable Enhaned Security Configuration altogether, but that is not recommended due to the obvious security reasons.)

SSDT 15.5.2 for Visual Studio 2017 Installation continues after necessary package URLs are allowed in Enhanced Security Configuration

SSDT 15.5.2 for Visual Studio 2017 Installation

Hope this helps you someday when you are setting up your environments.

References

  • Download SQL Server Data Tools (SSDT): https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt
  • Internet Explorer Enhanced Security Configuration (ESC): https://support.microsoft.com/en-in/help/815141/internet-explorer-enhanced-security-configuration-changes-the-browsing

Until we meet next time,

Be courteous. Drive responsibly.

#0410 – SQL Server – Dividing a TimeSpan by an Integer to find average time per execution


I recently encountered an interesting question on the forums the other day. The question was how to determine the average time taken by a single execution of the report provided we know how many times the report ran and the total time taken for all those executions.

The challenge is that the total time taken for all the report executions is a timespan value (datatype TIME in SQL Server). A TIME value cannot be divided by an INTEGER. If we try to do that, we run into an error – an operand clash.

USE [tempdb];
GO
DECLARE @timeSpan TIME = '03:18:20';
DECLARE @numberOfExecutions INT = 99;

SELECT @timeSpan/@numberOfExecutions;
GO
Msg 206, Level 16, State 2, Line 6
Operand type clash: time is incompatible with int

The solution is to realize that a timespan/TIME value is ultimately the number of seconds passed from a given instant. Once the timespan is converted to the appropriate unit (number of seconds), dividing by the number of executions should be quite simple.

Here’s the working example:

USE [tempdb];
GO
DECLARE @timeSpan TIME = '03:18:20';
DECLARE @numberOfExecutions INT = 99;

SELECT @timeSpan AS TotalActiveTime,
       DATEDIFF(SECOND,'1900-01-01 00:00:00.000',CAST(@timeSpan AS DATETIME)) AS TotalExecutionTimeInSeconds,
       DATEDIFF(SECOND,'1900-01-01 00:00:00.000',CAST(@timeSpan AS DATETIME))/(@numberOfExecutions * 1.0) AS TimePerExecution;
GO

/* RESULTS
TotalActiveTime  TotalExecutionTimeInSeconds TimePerExecution   
---------------- --------------------------- -------------------
03:18:20.0000000 11900                       120.20202020202020
*/

I trust this simple thought will help in resolving a business problem someday.

Until we meet next time,

Be courteous. Drive responsibly.