From Server\Instance how do I determine service dependency?
c#, sql-server
Solution
SQL Server services are named as either MSSQLSERVER (default instance) or MSSQL$INSTANCENAME (named instances). You can determine if it's a named instance either from the connection string (if is in the form `host\instance` the is a named instance, if is `host` then is default instance) but the truth is that this is not reliable, because:
- the connection string may use a SQL client alias
- the connection string may connect to a named instance listening on the default port
- the connection string may connect to an explicit port and not specify the instance name
So a more reliable way is to connect and ask for the instance name:
SELECT SERVERPROPERTY('InstanceName');
If the return is NULL, the service name will be MSSQLSERVER, otherwise is MSSQL$... You can even put this straight into the query:
SELECT COALESCE('MSSQL$'+cast(SERVERPROPERTY('InstanceName') as sysname), 'MSSQLSERVER');
Problem
I have an installer where the user enters the data source in the connection string (i.e. DataSource="machinename\instance"). Using C# given the machinename\instance string, I need know if the service name is MSSQLSERVER or MSSQL$SQLEXPRESS On one particular machine we have SQL Server 2005 (full) and SQL Express 2008 installed. The reason I need to know this is in our wix installer, the main application to be installed has a dependency on SQL server, so we need to have the correct dependency for it to be installed. The dependency could be on the service MSSQLSERVER or MSSQL$SQLEXPRESS and I can have both these services installed on a machine. JD