Ho do I allow multiple users to share a SQL 2005 Express database?

Hello,

Ho do I allow multiple users to share a database?

Background

I have developed a Windows App in VS.NET 2005 which connects to a SQL 2005 Express database.

Now I want to install the app and database on the network and I am getting an error "File 'file_name' is on a network device not supported for database files"

What is the best way to get this working

Thanks in advance,

Phil

Sorry, please find my connection string below. (Note that Z:\ is my network share)

Data Source=.\SQLEXPRESS;AttachDbFilename="Z:\Data\SurveyDB.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

Thanks,

Phil

|||do you really need it as user instance, i suggest that you attach the database to the main instance of sqlexpress and modify your connection string. user instance bahaves differently since everytime an applicaqtion is calling this method, the calling application spawns a service to the installed sql server express and makes the user of the calling application its administrator|||

Hi, and thanks for your prompt reply,

It seems that I am only just learning about the different types of connections!

How do I attach the database to the main instance of SQL Express? And then what would an example of my connection string be?

Thanks

Phil

|||

OK, I somehow have made a "main instance" of the database, and have changed my connection string to Server=LAPTOP\SQLEXPRESS;Database=SurveySystemsDB;Trusted_Connection=True

BUT I have no idea on how I "converted" my database from its original "User Instance" to the "main instance"

Can anybody tell me how to do this?

Thanks,

Phil

|||

Hi Phil,

User Instances are not about the database, it's about the server itself. A User Instance is a special instance of SQL Express that is started up at run-time; you can read about the details in the User Instance white paper. You can not actually share a User Instance between multiple users, they are designed to only allow local access.

The way you "converted" your database is simply by attaching it to your main instances of SQL Express rather than trying to attach it at run time using a connection string. Since I wasn't there when you attached your file, I couldn't tell you how you did it. The normal ways of attaching a database are to run T-SQL script, such as using sp_attach_db or by using a GUI tool such as Management Studio Express to attach the database.

Once a database is attached to a server, you appliction simply makes a connection as you've demonstrated above.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

sql