SQL Server Compact on a network share.
Greetings, I'm a little bit confused about SQL Server Compact behavior.
I have a small app that uses a small sdf file located on a network share.
The problem is that while the app itself is working perfectly well using the connection string that points the location of the sdf file on a network drive, when I try to open the same sdf using the Server Explorer I get this error:
SQL Server Compact does not support opening database files on a network share
Obviously, if the app IS working it does indeed support network shares, then why Server Explorer cannot open it?
I copied the database file to a local drive but it's not very convenient. What might be the problem?
Re: SQL Server Compact on a network share.
Quote:
Originally Posted by
cicatrix
Obviously, if the app IS working it does indeed support network shares
That is not the case.
There is a massive difference between "I haven't noticed any problems yet" and "supported" (aka "this is officially confirmed to be something that it is designed for").
SQL Server Compact is designed to be a single-user local database. Using it for multiple users is very likely to fail (in the same kind of way that other file-based databases [such as .mdb] fail), and to a lesser degree using it across a network is risky too.
The error is Server Explorer's way of telling you that you are doing something that is unsafe, and is likely to cause problems.
The important question right now is why do you have it on a network?
If it is to allow multiple users to work with it at the same time, you should change to a server-based database system (such as SQL Server Express, which is also free).
Re: SQL Server Compact on a network share.
No, the app is a single user one and the base is located on the network share due to safety considerations of our IT department (backups, security, etc). And I try to access it only while debugging. My problem is - while the app itself can access the base from a user machine and works, my Server Explorer in my VS IDE cannot. Using a server based database (MS SQL) is an overkill, I think and mdb... well mdb *IS* fail ;)
Re: SQL Server Compact on a network share.
Quote:
Originally Posted by
cicatrix
No, the app is a single user one and the base is located on the network share due to safety considerations of our IT department (backups, security, etc).
That's fair enough.
Quote:
And I try to access it only while debugging. My problem is - while the app itself can access the base from a user machine and works, my Server Explorer in my VS IDE cannot.
Have you tried using SQL Server Management Studio? (if you haven't got it, there is a free download for the Express one)
Quote:
Using a server based database (MS SQL) is an overkill, I think
Due to the Express edition, that is debatable... it wouldn't take much effort or resources, and would move you in to a "supported" configuration.
Quote:
and mdb... well mdb *IS* fail ;)
It does have its uses, but I basically agree!
