Detecting local Sql instances

I would like to find out what instances of sql are installed on any given machine. For instance, if a machine has SQL Server 2005, SQL Express 2005 and MSDE 2000 installed. I would like to be able to know the versions of each, and ultimately choose the one which is the best for the user to use.

For example, I would like to rank the instances, 1. SQL Server 2005 2. SQL server 2000, 3. Sql express, 4. MSDE

So I would basically like to default to the best choice available locally for the user.

Is there any way to do this using SMO?

Thanks...

Hi Johnny,

Yes, you could:

DataTable dt;

dt = SmoApplication.EnumAvailableSqlServers("JohnnysServer");

for (int i = 0; i < dt.Rows.Count; i++)
{
MessageBox.Show( dt.Rows["Version"].ToString() );
}

Cheers,
Rob

|||Thanks for your response Robert,

Just a few questions about this...

I have tried running it on a few machines, and on some of them the version was null...do you know why this would happen?

Also, does this specific version differ for the fully blown versions vs the light (sql express/msde) versions? Because I noticed that they can have identical versions....

Thanks again...|||

Hi Johnny,

Yes, the versions will be the same, however the "Edition" property (from memory) will display the actual, well...edition. Have a browse of the datatable colums as there's a heap of properties/columns returned.

Not sure why version would be returned as null, unless of course the service wasn't running or otherwise unavailable.

Cheers,
Rob