But really SQLDIAG I do, and I am not lying!! I have helped multiple users of SQLDIAG (not SQL Nexus) trace down the root cause of this issue. SQLDIAG is a tool that is used to collect data which can be imported into a SQL Server database with the help of SQL Nexus.

The error that you get in the ##SQLDIAG.LOG file when you try collecting data using the configuration files downloaded from the SQL Nexus website is:

2011/04/05 22:54:32.27 SQLDIAG Failed to open Software\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER key . Function result: 2. Message: The system cannot find the file specified.
2011/04/05 22:54:32.27 SQLDIAG There are no instances of version 10 on this computer
2011/04/05 22:54:32.27 SQLDIAG . Function result: 87. Message: The parameter is incorrect.

The above error is most commonly due to the use of the SQLDIAG configuration file which was configured to capture data from SQL Server 2008 instances. So if you are using the same configuration file to capture data from SQL Server 2008 R2 instances, then you need to make a small change to the .xml configuration file. The XML file will have an XML tag with the name “Instance”.

<Instance name="<instance name>" windowsauth="true" ssver="10.5" user="">
OR
<Instance name="<instance name>" windowsauth="true" ssver="*" user="">

If you want to collect data only from a SQL Server 2008 R2 instance, then you can change the ssver value from 10 to 10.5 (first example above). If you intend to collect data from more than one instance from two different releases of SQL Server (eg. SQL Server 2008 and SQL Server 2008 R2), then you need to change the ssver value to *.

The <instance name> place holder is for the SQL Server instance. The instance name provided depends on the data collection scenario:

Name = * - If you want to collect data from all instances

Name = Instance Name – If you want to collect data only from a specific stand-alone instance SQL Server named instance

Name = MSSQLSERVER – If you want to collect data from a default stand-alone SQL Server instance.

There are other reasons due to which this issue occurs:

  1. If you are collecting data from a SQL Server clustered instance, do the following:
    a.       Hard code the machine name (<Machine name=".">). Do NOT use ".".    Instead, hard code the virtual server name
    b.      Hard code the instance name (<Instance name="*"…).  Do NOT use "*".   Instead, hard code the actual instance name.  If it is a default instance, enter MSSQLSERVER.
  2. The error could also happen if you are using a 32-bit SQLDIAG to collect data from a 64-bit SQL Server instance.
  3. Check if the following registry key exists on the machine: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names
    a.       If the above registry key exists, then check the version number reported under the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance name>\mssqlserver\CurrentVersion. It should either be 9.0, 10.0 or 10.5.
  4. If you run into this issue on a clustered installation, then it could be that after SQLDIAG detected that the instance was clustered it was unable to successfully execute an OpenCluster API call. The most common reason for this API call failing is lack of permissions for the account running SQLDIAG to execute the API call successfully.
  5. If you are running into this issue on a Windows Server 2003, then apply this fix if the above suggested action items don’t help.

Happy data collection! J


Amit Banerjee
SQL Server Escalation Services
Twitter: @banerjeeamit