I have seen this question asked previously but can not find a clear explanation of what is/isn't possible and what workarounds migth be available.
I have an existing C# application that uses SQL CE to store local information. This database is only accessed by a single application and is stored in the user's appdata folder.
Some environments have the appdata folder redirected to a network share which causes SQL CE to throw an exception: "Internal error: Cannot open the shared memory region."
I have read that SQL CE 3.5 SP2 is supposed to allow connections to network shares again, and that SP1 does not, however I have not been able to get it to work. Has anybody had any success getting any version of SQL CE to work over a network share? And if so, what version/code did you use to get it to work?
I have tried using mode=Exclusive in the connection string with no luck.
Or alternatively, has anybody found a different workaround? The data needs to be stored in the user's profile as it is specific per user and should not be accessible by any other user on the system.
I updated the application to use SQL Express with local user instances which worked, but it created too many deployment issues for our customers. I ended up having to write a layer to use Access MDBs while we look at different database options.
My suggestion for writing desktop applications is DO NOT use SQL express or SQL CE for local storage.