Can we have generalized connection string for SQL Server and SQL Server Express?

Question

I have a WCF service that is deployed on a machine. This WCF service can be configured to either SQL Server or SQL Server Express.

NOTE : The SQL database location can be other machine other then where WCF service is deployed.

I put following information in XML file:

1. user id
2. password
3. ServerName
4. MachineName

In case of SQL Server Express

The ServerName property is "SQLEXPRESS". Internally I append the ServerName with MachineName so the serverName is:

MachineName\SQLEXPRESS

which is passed to connection string.

In case of SQL Server

When I pass ServerName to SQL Server it throws exception.

Please guide me for the best approach!!

Answer 1

You can use the same connection string format to connect to both SQL Server Express and full editions.

Just make sure that you are going through with the proper string, something like this would be common, for DEFAULT installations.

Data Source=MachineName\SQLExpress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

and for full SQL Server

Data Source=MachineName;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Answer 2

The most likely reason is because your instance of MSSQLSERVER is running under the default instance rather than a named instance (which is what SQL Express does). A work around is to configure a local properties file that holds the connection string specific to the environment. Then either reference the file with in the config file itself, or have part of your build process incorporate the information into the configuration file.

Answer 3

What is the exception? Also I am not sure what the difference is between ServerName and MachineName. Maybe you meant ServerName and InstanceName? You should construct your string this way (pseudo code, I have no idea what language you're using):

DataSource = ServerName
If (InstanceName != "")
    DataSource += "\" + InstanceName