SqlCeException database is opened with a read-only connection

Question

We store a template for building SQL Server CE connection strings in our web.config and use string.format to set the data source and temp path values.

In the web.config, the template for the DB connection string is:

"Data Source={0}; Temp Path={1}; Mode=Read Only" 

In the web.release.config, we use to build deployment packages, the template for the DB connection string is:

Data Source={0}; Temp Path={1} 

Now we only use the SQL Server CE database for read only operations. So, being a smartypants, I thought I would remove the connection string transform in the web.release.config and just always use the read only connection string from the web.config.

This worked fine for the database I was testing. But the same code running with a different SQL Server CE database fails with the following exception:

Exception 'System.Data.SqlServerCe.SqlCeException' with message 'The database is opened with a read-only connection. Can't perform post-initialization operations like re-building indexes and upgrading public tracking. Please re-open with a read-write connection. [ Database name = D:\inetpub\wwwroot\MyApp\App_Data\Storage.sdf ]'

We are using the System.Data.SqlServerCe.4.0 provider

Why in my release build that is deployed to a server does read only access fail for some SQL Server CE databases but not others? It is the same code, just pointing at a different .SDF file. The .SDF files have the same schema, just different data.

Is the driver trying to rebuild indexes because the .SDF file is in some older format? Should we never use a readonly connection in production? Is that why the original code had the transform? why do they all work with a readonly connection on my Windows 7 workstation?

Answer 1

You should always specify a temp path, as moving databases between OS platforms/versions can require index rebuilds.

See my blog post post here: http://erikej.blogspot.dk/2009/08/running-sql-compact-from-cd-rom-read.html for more detailed info.