What's the best way to detect the presence of SMO?

Question

I have some code that uses SMO to populate a list of available SQL Servers and databases. While we no longer support SQL Server 2000, it's possible that the code could get run on a machine that SQL Server 2000 and not have the SMO library installed. I would perfer to check for SMO first and degrade the functionality gracefully instead of blowing up in the user's face. What is best way to detect whether or not SMO is available on a machine?

Every example that I have seen through a quick Google scan was a variation of "look for C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll". The problem with that approach is that it only works with SQL Server 2005. If SQL Server 2008 is the only SQL Server installed then the path will be different.

Regenerative

A related question: Have you faced the issue of making the same code work with 2005 and 2008. If yes, how did you solve it? – Marras

Answer 1

I had a look at the SharedManagementObjects.msi from the SQL2008 R2 feature pack and my Windows Registry (SQL2008 R2 Dev is installed on this machine) and I believe these are the reg keys one should use to detect SMO (All under HKLM):

SOFTWARE\Microsoft\Microsoft SQL Server\SharedManagementObjects\CurrentVersion - this is apparently the main key, indicating that some version of SMO is installed.

SOFTWARE\Microsoft\Microsoft SQL Server 2008 Redist\SharedManagementObjects\1033\CurrentVersion - this one probably means 2008 English is installed. Probably just checking for the presence of SOFTWARE\Microsoft\Microsoft SQL Server 2008 Redist\SharedManagementObjects would suffice.

Same applies to SQL2012: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server 2012 Redist\SharedManagementObjects\1033\CurrentVersion

But NOT SQL2005! even though I do have 2005 installed on this machine as well.

One more thing, You'd normally want Microsoft SQL Server System CLR Types as well, since SMO depends on them. The SQLSysClrTypes.msi has only one registry key: SOFTWARE\Microsoft\Microsoft SQL Server\RefCount\SQLSysClrTypes

Spiller

With the redistributable CLR Types, the registry key seems to be HKLM\SOFTWARE\Microsoft\Microsoft SQL Server 2008 Redist\SQL Server System CLR Types. The information you might want to check (like version) is in subkeys. – Andra

Answer 2

This is kind of clunky, but a quick check of the registry seems to work. Under HKEY_CLASSES_ROOT, a large number of classes from the SMO assemblies will be registered. All I needed to do was to pick one of the SMO classes and check for the existence of the key with the same name. The following function will return true if SMO has been installed, false if otherwise.

private bool CheckForSmo()
{
    string RegKeyName = @"Microsoft.SqlServer.Management.Smo.Database";
    bool result = false;
    Microsoft.Win32.RegistryKey hkcr = Microsoft.Win32.Registry.ClassesRoot.OpenSubKey(RegKeyName);
    result = hkcr != null;

    if (hkcr != null)
    {
        hkcr.Close();
    }

    return result;
}

Regenerative

Answer 3

What I do is just try to create an instance of some SMO object. If it fails, its not there.

Marras

Answer 4

Solution for SQL Server 2012:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\SharedManagementObjects\CurrentVersion\Version

You can check if this key exists (and check if the value is greater than 11).

Sconce

Answer 5

Just a quick note: HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\SharedManagementObjects\CurrentVersion\Version doesn't represent the current version that is installed, because there could be several versions installed.

The registry key above is being updated when you install a version, so if you've installed SMO 2014 then you should see 12.x, but if afterwards you install SMO 2012, then this version would change to 11.x If you then decides to repair the 2014 installtion, then the version would be again 12.x

You should better look at: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server 2012 Redist\SharedManagementObjects\1033\CurrentVersion

or HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server 2014 Redist\SharedManagementObjects\1033\CurrentVersion

Does someone knows if the 1033 is guaranteed? (Meaning only english version)

Uncalledfor

Hi, I've tested it, and it is not guranteed that it is 1033 (it depends on the SMO that is being installed). – Uncalledfor