SQLDIAG and SQL Server 2012

SQLDIAG is a data collection utility that is used for collecting T-SQL script output, perfmon data and profiler traces in a consolidated manner. This allows the database administrators for collect a single output without having to configure multiple data collection utilities for capturing the required data.

SQLDIAG has been shipping with the SQL Server product since SQL Server 2005. Now the reason I am writing this post is to talk about a specific issue that you can encounter when you already have a previous version of SQLDIAG installed on your machine along with SQL Server 2012.

Using the command below I am trying to execute a SQLDIAG data collection using a specific SQL Server 2012 SQLDIAG configuration file. The command that I used was to specify the output folder and the default SQLDIAG configuration file available at C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLDiag.XML:

C:\>sqldiag /O "F:\Temp\SQLDIAG Output" /I "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLDiag.XML"

The output that I got was:

SQLDIAG Output path: F:\Temp\SQLDIAG Output\

SQLDIAG Invalid SQL Server version specified.  SQL Server version 11 is not supported by this version of the collector

SQLDIAG . Function result: 87. Message: The parameter is incorrect.

The reason for the above issue is that my environment PATH variable has the path for a previous version of SQLDIAG listed before the path of the SQL Server 2012 SQLDIAG. My path variable has the directory “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\” listed before “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\” which is the default location of the SQLDIAG utility. The PATH variable is updated with the SQL specific directories during a SQL Server installation. In my case, I have a SQL Server 2008 R2 instance installed on my box. So the configuration file which specifies a data collection for a SQL Server 2012 is failing because I am using a SQLDIAG from a SQL Server 2008 R2 installation.

If I executed the following command, then my SQLDIAG initialization will work correctly:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqldiag.exe" /O "F:\Temp\SQLDIAG Output" /I "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLDiag.XML"

To summarize, you need to full qualify your SQLDIAG executable path when collecting SQLDIAG data from a machine which has multiple versions of SQLDIAG installed on the machine.


Visual Studio 2010: Are you being cordial to Report Viewer

imageIf you are using Visual Studio 2010 and a Windows Forms Application which uses the Report Viewer control, then might have probably been scratching your head when you suddenly lost the capability of using any of the drill-through options in your reports.

The problem happens only after you install Visual Studio Service Pack 1! So, what is the solution… Install an update released for Visual Studio 2010 Service Pack 1 (KB2549864).

If you are using only the Microsoft Report Viewer 2010 SP1 Redistributable Package, then you need to install the file named “ReportViewer.exe”. If you use Microsoft Visual Studio 2010 Service Pack 1, install the file that is named “VS10SP1-KB2549864-x86.exe.” I saw yet another question on this being raised on this again this week… So I thought that a quick blog post on this would be definitely worth the effort!

Reference: Update fixes several Report Viewer issues after you install Visual Studio 2010 Service Pack 1 http://support.microsoft.com/kb/2549864

Brian Hartman’s Blog

Addition: 10/22/2014: A number of people have reported that the above link does not work anymore for downloading the hotfix files. You will need to download the hotfix files from the link mentioned below:

Update for Microsoft Visual Studio 2010 Service Pack 1 Report Viewer (KB2549864)

SQL Server Backup Simulator v2 available now

SQL Server Backup Simulator is used by CSS to troubleshoot SQLVDI related issues and to identify if the SQLVDI DLL is functioning correctly. Based on the feedback received from the use of the tool and the current troubleshooting needs, we decided to do v2 release for SQL Server Backup Simulator.

The new features for the v2 release are:

  1. Log backup – Now the tool can perform log backups. The tool performs COPY_ONLY backups so that your LSN chain is not broken.
  2. Compression support – Starting from v2, the tool will allow you to take backups with compression enabled for SQL Server 2008 and higher.

The compression option drop-down list has three-options:
a. With Compression: This option will allow you to perform a backup with compression enabled even if the server default is not to use compression for backups.
b. No Compression: This option will allow you to perform a backup with compression disabled even if the server default is to use compression for backups.
c. Server Default: This option uses the server default setting (configuration setting: backup compression default) to perform the backup.


Screenshot of the v2 UI

Log Restore option is not currently available in this release. The incorporation of this feature will be evaluated while planning for the next release.

A big thank you to Sakthi [Blog | Twitter] for his assistance on the v2 release.

The latest release can be downloaded here.

Previous posts for Backup Simulator:
SQL Server Backup Simulator v1.0
SQL Server Backup Simulator v1.2