Oh sweet mercy save me! .MDF on network

[pachjo]

I have written my application with the data file being a sql server .mdf file currently stored on my hard drive.

However the intention is to have the file on a file server so that the client will be on users computers and the database on the server.

I just tested my app with the .mdf file on a file server and got this error:

The file "\\maze\pcc$\Developer\TestDB\TestSQL.mdf" is on a network path that is not supported for database files.
An attempt to attach an auto-named database for file "\\maze\pcc$\Developer\TestDB\TestSQL.mdf" failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Oh please PLEASE tell me I can access an mdf file on the network!!

I am in BIG s**t if it it is not possible.

If not can anyone please suggest a way to save my bacon?

For the record a SQL Server instance on this or any other server is out of the question!

I thought that as VS.NET provided all the divers etc it would work.

I am in meltdown panic mode!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[Arg81]

If you move the file to a proper shared folder, I think you will then have to update all your connection info in your app...it won't do that automatically.

Not sure as I've never done it this way, but I assume

- MAZE is the name of YOUR computer

- any share with $ in means it is an admin share which standard users cannot access, it's best to create a properly shared folder on the network that has NTFS AND Share permissions for the correct users

...other than that, my suggestion would of been SQL express, but as I see you can't do that :/

 

[pachjo]

oooh now theres a thought, I will try it as a user would. the $ share is for my admin guff!

I will try what you suggest and let you know.

Re the conection string, that is what I was testing. As part of the admin section of the app if an admin class user of the app is logged on it allows them to redirect the application to a new database source, you know when server get moved, upgraded renamed etc.

The function allow this, rebuilds the connection string and then restarts the application to read it all in and away it goes.

This worked fine when I pointed to a file elsewher on my own PC

 

[InertiaM]

Random thought - check access rights for the user, as I expect a user needs write access to the file.

 

[pachjo]

The file path stated is one I have full access to so am suprised access would be an issue.

When released to the everyday users the mdf would be placed in a normal shared folder.

I'm just walking thru to see what is what....

 

[pachjo]

Nope! I think I am stuffed!!!!

If I try to create a new connection to a network drive it fails with the same error.

I have read elsewhere to try sql server 2005 compact?

lost in limbo now totally deflated!

I cannot believe I got this far into development with everything working 100% as required only to get shafted by the mdf not allowed on networks!

 

[pachjo]

Can a guru throw me a bone and thus allow me a peaceful nights sleep tonight?

Thought/Idea...

I have a standard Windows XP Pro PC in my office on 24/7

Could I install SQL Server Express 2005 on this PC and have the database there

I beleive it only allows 5 concurrent users, is this true?

Could this be what saves my bacon?

 

[JohnH]

I read somewhere that attaching SQLX to a network file is not supported, the file must be local.
There is no connections limit, but max 4GB db size. You'll need How to: Enable Network Access in SQL Server Configuration Manager (SQL Server Express) since this is disabled by default.
Also found a statement about SQL Compact allows file share connection, but locks the file for single attachment.

 

[pachjo]

Thanks JohnH, this is very helpful

I shall have a busy day checking this out, will post my success ...... or failure!

 

[cjard]

The file "\\maze\pcc$\Developer\TestDB\TestSQL.mdf" is on a network path that is not supported for database files.

Erm. Dude. If you want people on a network to access a database file, you DONT set up 1 copy of sql server on every client and then have them all use a network shared MDF file.. You set up ONE SQL Server (that uses the MDF locally) and then have every client connect to it.. That's the whole idea of SQL Server being a network aware database

SQL Server is not MS Access...

 

[pachjo]

Erm. Dude. If you want people on a network to access a database file, you DONT set up 1 copy of sql server on every client and then have them all use a network shared MDF file.. You set up ONE SQL Server (that uses the MDF locally) and then have every client connect to it.. That's the whole idea of SQL Server being a network aware database

SQL Server is not MS Access...

Yes I understand this now! Sometimes I wonder how my brain generates enough electricity to allow me to breath!!

Each new days brings new knowledge ...