Hi Everyone,
I have a software solution that consists of two separate applications that share a SQL Server Express database on a single computer. Everything is working great, except when both of the applications want to access the database at the same time. All I can find is information on configuring multi-user access in a distributed system. I'd appreciate any information on configuring multi-application access on a single computer.
Thanks,
Mike
SQL Server Express, Simultaneous ,Multiple Application Access?
Burrough
For what it's worth, in the connection string I had to include the ".mdf" extension for the AttachDbFilename attribute. E.g.,
Ceres629
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
Francisco G
A Lesson in User Instances
Here is what I have learned after researching my situation. The following summary came from a great article at:
http://msdn.microsoft.com/sql/express/default.aspx pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
I hope that this information helps others with similar issues…
Single User Access on Local Machine
REQUIRED ELEMENTS
1) Use User Instances;
2) Connection String must include the following:
a. Data Source=.\SQLEXPRESS;
b. AttachDbFilename=|DataDirectory|\XXXXX.mdf; where XXXXX is the root name of your database file.
c. Integrated Security=True;
d. User Instance=True"
e. Must be a SQLClient connection string
LIMITATIONS
1) ONLY ONE USER INSTANCE CAN USE THE DATABASE AT A TIME; FOR ANOTHER INSTANCE TO USE IT, THE CONNECTION MUST BE CLOSED! So, if you want to permit multiple applications on the same local machine to use the database file, you must close the connection in one application so the other one can open it, OR you must use a multi-user/application configuration, which does not use User Instances.
2) Only local connections are allowed;
3) Replication does not work with user instances;
4) Distributed queries do not work to remote databases;
5) User instances only work in the Express Edition of SQL Server 2005;
multi-user/multi-application Access on Local Machine (Does not use User Instances)
1) Attach the database files to a normal SQL Server instance by using the CREATE DATABASE ... FOR ATTACH command;
2) Remove the AttachDbFilename from the connection string;
3) Remove User Instance options from the connection string;
4) Each user must have a login on the local computer;
5) Each user must have a database account;
6) The database user must be granted, in the database, the permissions required to run the application;