none
SQLCE 3.5 SP2 on Network Share

    Question

  • We have a desktop application that uses sql ce 3.5 SP1 - in the past we have always found that we cannot have the data file located on a network drive.

    We recently upgraded our development environment to sql ce 3.5 SP2 to facilitate development of synchronisation features. I was surprised to find that we can now open a data file on a network share. With sql ce 3.5 sp2 is it now supported to have the data file on a network share? I believe we only open one connection to the database at a time.

    I have also read with interest the announcement of the release of sql ce 4.0. Can sql ce 4.0 work with data files on a network share, or are there new features in this regard?

    We are using Sync Framework 2.1 to allow sync between sql ce databases and also (in due course) between sql ce and sql server databases.  I noticed the 4.0 readme indicates "Compact 4.0 does not support data replication with SQL Server using Sync Framework".  Does this mean that if we move to sql ce 4.0 it will not be possible to sync the sql ce 4.0 database with sql server? That would certainly stop us moving to sql ce 4.0.

    Thanks for any advice.

    Regards,
    Tim

    Wednesday, January 19, 2011 2:40 AM

Answers

  • SQL Server Compact is a single machine database, and will create an exclusive lock on databases open on network shares. So you should always store the database on a local drive. I think stroing on network shares is disabled in version 4.

    For now, no support for Sync with version 4. For now you can use 3.5 SP2 - what are you missing in this version compared to 4?


    Visit my SQL Compact blog - http://erikej.blogspot.com - Please mark as answer, if this was it.
    Wednesday, January 19, 2011 10:41 AM
    Moderator

All replies

  • SQL Server Compact is a single machine database, and will create an exclusive lock on databases open on network shares. So you should always store the database on a local drive. I think stroing on network shares is disabled in version 4.

    For now, no support for Sync with version 4. For now you can use 3.5 SP2 - what are you missing in this version compared to 4?


    Visit my SQL Compact blog - http://erikej.blogspot.com - Please mark as answer, if this was it.
    Wednesday, January 19, 2011 10:41 AM
    Moderator
  • Hi Erik,

    Can you confirm if version 4 removes support for access of database files off of network drives?

    If this is true it would be horrible news!   

    We only need the same behavior that 3.5 SP2 supports, which allows a single user exclusive access to databases open from a network share.   Our application currently uses 3.5 SP2 and if 4.0 removes network support it would kill a large portion of our user base.  Some companies and large corporations remap user’s desktop and document folders to distribute network drives.  We see this more often then you might suspect.  Some users are even unaware they are working on a network drive, so allowing access to network drives (even single use as with 3.5 SP2) is crucial to us. 

    Thanks much,
    James

    Monday, February 21, 2011 7:49 PM
  • Okay, what are you really trying to accomplish? Are you building a DBMS that contains data to be shared? If so, SQL Compact is designed to share its data with SQL Server via replication. In this architecture you put the SQL Server DBMS engine (even express) on the network share and use replication (only in 3.5) to push and pull changes from the server.

    hth


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Monday, February 21, 2011 8:18 PM
    Moderator
  • I will test it - and so could you...
    Visit my SQL Compact blog - http://erikej.blogspot.com - Please mark as answer, if this was it.
    Monday, February 21, 2011 8:22 PM
    Moderator
  • Hi William,

    Our application is already created so I'm not trying to accomplish anything other than to find out information about the new SQL CE 4. My question was if the new version of SQL CE 4 still allows access to database files stored on a network share as does SQL CE 3.5 SP2. Versions prior to SQL CE 3.5 SP2 did not allow this; they would return an error if you tried to open a database off of a network share. We have a WPF client-side application that uses SQL CE to store simulation data; it never opens more than one connection to the data at any given time. I want to verify if SQL CE 4 can be used in future versions of our product; otherwise, I may need to start investigating other database products, which would be a real shame as I really like SQL CE.

    Thanks,
    James.

    Tuesday, February 22, 2011 7:44 AM
  • Hi Erik,

    Yes, I could test it but I don't have a virtual development environment setup at the moment so I figured that asking the question in the SQL Server Compact forum would be an appropriate place to ask.  Getting an answer from someone who already knows would save me a bunch of work and time not having to set up a test machine.  We work under a very restricted environment so I cannot just install SQL CE 4.

    The only reason I asked in the first place was a comment from you saying you thought the feature was removed.  What made you think that?  I thought maybe you heard it second-hand from one of the Microsoft developers.

    Thanks much,
    James.

     

    Tuesday, February 22, 2011 7:59 AM
  • My comment was based on another users post in this forum. I will test with the RTM bits and let you know.
    Visit my SQL Compact blog - http://erikej.blogspot.com - Please mark as answer, if this was it.
    Tuesday, February 22, 2011 8:45 AM
    Moderator
  • OK, I have tested and get this error:

     Error Code: 80004005
     Message   : Internal error: Cannot open the shared memory region.
     Minor Err.: 25118
     Source    : SQL Server Compact ADO.NET Data Provider


    Visit my SQL Compact blog - http://erikej.blogspot.com - Please mark as answer, if this was it.
    Tuesday, February 22, 2011 4:33 PM
    Moderator
  • Hi Erik,

    Thanks for testing this for me. Hmmm, internal error... I wonder if this is a bug or if the feature has been removed. I've open a bug on Microsoft Connect in any case.

    Thanks again,
    James.

    Wednesday, February 23, 2011 7:32 AM
  • I think it means that this "feature" has been disabled.
    Visit my SQL Compact blog - http://erikej.blogspot.com - Please mark as answer, if this was it.
    Wednesday, February 23, 2011 8:16 AM
    Moderator