“SQLExpress database file auto-creation error” but

Question

I am using Visual Studio 2012 to create an MVC4 Internet application. I've moved away from Entity Framework and I am now using a local install of SQL Server 2008 R2.

I changed my connection string to point at my local SQL Server instance:

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=.;Initial Catalog=NotesBoard;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />
</connectionStrings>

There are no other connection strings in my project. This worked fine, but now I am getting

---

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance...

---

The weird things is that, intermittently, this works, and then goes back to it's broken state.

Update: To Answer Pawel's question: I have the following class which is referenced from all my controllers:

public class DataAccess
{

    public void ExecuteNonQuery(String StoredProc, List<SqlParameter> Parameters)
    {
        String ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
        SqlConnection cn = new SqlConnection(ConnectionString);

        SqlCommand cm = new SqlCommand(StoredProc, cn);
        cm.CommandType = CommandType.StoredProcedure;

        FillCommandParameters(Parameters, cm);

        cn.Open();
        cm.ExecuteNonQuery();
        cn.Close();
    }

    public DataSet Execute(String StoredProc, List<SqlParameter> Parameters = null)
    {
        String ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
        SqlConnection cn = new SqlConnection(ConnectionString);

        SqlCommand cm = new SqlCommand(StoredProc, cn);
        cm.CommandType = CommandType.StoredProcedure;

        FillCommandParameters(Parameters, cm);

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cm;
        DataSet ds = new DataSet();

        cn.Open();
        da.Fill(ds);
        cn.Close();

        return ds;
    }

    private static void FillCommandParameters(List<SqlParameter> Parameters, SqlCommand cm)
    {
        if (Parameters != null)
        {
            if (Parameters.Count > 0)
            {
                foreach (var Parameter in Parameters)
                {
                    cm.Parameters.Add(Parameter);
                }
            }
        }
    }
}

Answer 1

I have been getting the same error on my MVC4 site when redeploying. I can't say what the root cause of the issue is but it appears to be that something cached in the browser (perhaps the session Guid) is preserved when the site is redeployed and for some reason this causes the server side code to throw this curious error.

Closing and reopening the browser seems to clear the cache and then the site works as expected. This might explain the intermittent nature of the issue.

Answer 2

If this is SQL Server Express (the tag suggests so) then you very likely have a named instance called SQLEXPRESS since this is the default instance name for SQL Server Express installations. If this is the case you need to change your connection string to have Data Source=.\SQLEXPRESS.