5

Referring to question How do I share a SQL Server CE database file (.sdf) for multiple processes?

According MSDN, SQL CE allows 256 concurrent connections to single database.

But even on the SAME machine, I facing sharing violation error when I open the same database BUT with 2 different process (a.exe & b.exe, code is the same). What seems to be the problem here?

I was using connection string 

SqlCeConnection("Data Source=d:\test.sdf;Encrypt Database=True;Password=test;File Mode=read write;")
Community
  • 1
  • 1
Dennis
  • 3,528
  • 4
  • 28
  • 40
  • You need to connect to the Service not the file for concurrent access. Not sure the CE has a Service. They refer to 256 connections in a web scenario. – paparazzo Dec 09 '13 at 15:07
  • @Blam can you show me some sample code? I am trying to have 2 process access the same database (read & write) – Dennis Dec 09 '13 at 15:07
  • You probably need to expose the data...as a Service (WCF of WebApi)....and have the 2 different apps/processes talk to that one service.....and have the service talk to the database.... Connections are not the same as Processes. Also, Microsoft Access 16bit said it supported 255 connections, but that was poppy-cock. I think they later revised it to 10-15 as reasonable. And think..in a webapp (or similar) you quickly open and close connections..they don't live and linger. But I'd think about the WCF(or WebApi) route, instead of 2 different processes accessing this file........ – granadaCoder Dec 09 '13 at 15:10
  • No I cannot show you sample code as I don't think CE has a service. See the link you reference. – paparazzo Dec 09 '13 at 15:10
  • Right. There is no "service". (Like a full fledged sql-server has). Thus you have to roll your own. WCF or WebApi would be the usual suspects. – granadaCoder Dec 09 '13 at 15:28
  • Is D: a local drive? It MUST be. And are the connection strings from both processes EXACTLY the same? – ErikEJ Dec 09 '13 at 17:15
  • It also will happen, [when the SDF file is located on a network drive](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/275ed470-52ea-4134-951b-084ab424ebf1/sqlce-file-sharing-violation?forum=sqlce) instead of a local drive and then is accessed from multiple processes simultaneously. – Uwe Keim Apr 16 '19 at 09:15

2 Answers2

5

This is not the answer you want to hear, but you should never attempt to open an SQL CE file from multiple processes. Yeah, I know, SQL CE 4.0 allows multiple writers, and you think you'll never have multiple writers writing to the same record, but this is like thinking it's safe to juggle flaming chainsaws so long as you wear a blindfold.

SQL CE modifies changed bytes inside the live data file. You cannot reliably do this from multiple processes. You think you can, but you can't. Read the accepted answer to the question you quoted; use a service-oriented database like SQL Express.

Community
  • 1
  • 1
Dour High Arch
  • 21,513
  • 29
  • 75
  • 90
  • 1
    but according to http://technet.microsoft.com/en-us/library/ms171817.aspx "Read Write" mode "Permits multiple processes to open and modify the database", isn't it possible? – Dennis Dec 10 '13 at 02:07
  • 1
    I don’t think you understand how difficult it is to do what you are asking; you will effectively have to write SQL Server Express yourself. It is very easy to make subtle mistakes that corrupt your data and leave no trace. Do not go down this route, use SQL Express, this is what it is designed for and SQL CE is not. – Dour High Arch Dec 10 '13 at 02:35
0

In my case, the file was somehow locked. I was using TFS online and the sdf file was in locked/edit mode, although i had checked out the file and removed read-only flag from file attribute.

To fix it, i had to open tfs source control explorer and remove the lock.

Please make sure that the file is not locked.

Note: This is an old post but I am only adding this answer so that it could be helpful to someone facing this issue.

Chirag
  • 1,683
  • 2
  • 17
  • 26