Is SQL Express 2005 by default a single connection db?

Is SQL Express 2005 by default a single connection db?
Background:
I wrote a simple .bat file which executes the following sqlcmd call:
"sqlcmd -S BLUEBOX\SQLEXPRESS -d C:\App_Data\ASPNETDB.MDF -U
MyUserCount -P MyUserPassword -Q "Exec SendMessage" -o C:\Vocalico
\SendMailQuePump\Undelievered.txt"
the above command works fine, as long as no other application is
accessing the db.
Also, using VS2005 I wrote a simple web based calendar scheduling
application which I am testing by launching it via Visual Studio 2005
(using the built in ASP.Net Development Server), the web application
seems to work correctly as well.
The Problem Found:
When I run the VS2005 web application the sqlcmd fails to login (note
that the web application and the sqlcmd access the same db (C:\App_Data
\ASPNETDB.MDF)). The following is the error I get form SQL 2005
Express when running the sqlcmd while the web application is running:
Msg 5120, Level 16, State 101, Server BLUEBOX\SQLEXPRESS, Line 1
Unable to open the physical file "C:\App_Data\aspnetdb.mdf". Operating
system error 32: "32(The process cannot access the file because it is
being used by another process.)".
Msg 5120, Level 16, State 101, Server BLUEBOX\SQLEXPRESS, Line 1
Unable to open the physical file "C:\App_Data\aspnetdb_log.ldf".
Operating system error 32: "32(The process cannot access the file
because it is being used by another process.)".
File activation failure. The physical file name "C:\App_Data
\aspnetdb_log.ldf" may be incorrect.
Msg 4060, Level 11, State 1, Server BLUEBOX\SQLEXPRESS, Line 1
Cannot open database "C:\App_Data\ASPNETDB.MDF" requested by the
login. The login failed.
Msg 18456, Level 14, State 1, Server BLUEBOX\SQLEXPRESS, Line 1
Login failed for user 'MyUserCount'.
Questions:
Shouldn't SQL 2005 Express allow more than one user to connect and
authenticate to the db by default? Are there any settings I need to
modify in Visual Studio 2005 or SQL Express 2005 in order to allow my
web app and the sqlcmd command to co-exist and execute without
interfering with each other?
My Setup:
VS 2005
XP Professional SP2
SQL 2005 Express
web.config connection string looks like this:
<add name="ASPNETDBConnectionString" connectionString="Data Source=.
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated
Security=SSPI;User Instance=True" providerName="System.Data.SqlClient"/
>
I have been going around in circles on this issue, any help would be
greatly appreciated!
Sincerely,
-ralphI think there might be some confusion to what the database concept is. For the "real" SQL server, we
create the database and to use it, se specify the database name in the USE command, or the database
name in the connection string or for the -d option for sqlcmd. But you use the physical name of the
mdf file in your sqlcmd call.
My guess is that sqlcmd does either some form of special attach (read about sp_attach_db for the
concept of attach) and it becomes in some form of single user mode. Or that sqlcmd even handles the
instance as a user-instance (a concept only available for Express, which I haven't played with but
which you should be able to find some info if you Google).
So, what I would do is to attach the database (sp_attach_db) and then specify the database name (not
file name) in sqlcmd and your connection string.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RGF" <Raf.Figueroa@.gmail.com> wrote in message
news:dcdeef9a-382d-47dd-9829-b491eb48df6a@.e4g2000hsg.googlegroups.com...
> Is SQL Express 2005 by default a single connection db?
> Background:
> I wrote a simple .bat file which executes the following sqlcmd call:
> "sqlcmd -S BLUEBOX\SQLEXPRESS -d C:\App_Data\ASPNETDB.MDF -U
> MyUserCount -P MyUserPassword -Q "Exec SendMessage" -o C:\Vocalico
> \SendMailQuePump\Undelievered.txt"
> the above command works fine, as long as no other application is
> accessing the db.
> Also, using VS2005 I wrote a simple web based calendar scheduling
> application which I am testing by launching it via Visual Studio 2005
> (using the built in ASP.Net Development Server), the web application
> seems to work correctly as well.
> The Problem Found:
> When I run the VS2005 web application the sqlcmd fails to login (note
> that the web application and the sqlcmd access the same db (C:\App_Data
> \ASPNETDB.MDF)). The following is the error I get form SQL 2005
> Express when running the sqlcmd while the web application is running:
> Msg 5120, Level 16, State 101, Server BLUEBOX\SQLEXPRESS, Line 1
> Unable to open the physical file "C:\App_Data\aspnetdb.mdf". Operating
> system error 32: "32(The process cannot access the file because it is
> being used by another process.)".
> Msg 5120, Level 16, State 101, Server BLUEBOX\SQLEXPRESS, Line 1
> Unable to open the physical file "C:\App_Data\aspnetdb_log.ldf".
> Operating system error 32: "32(The process cannot access the file
> because it is being used by another process.)".
> File activation failure. The physical file name "C:\App_Data
> \aspnetdb_log.ldf" may be incorrect.
> Msg 4060, Level 11, State 1, Server BLUEBOX\SQLEXPRESS, Line 1
> Cannot open database "C:\App_Data\ASPNETDB.MDF" requested by the
> login. The login failed.
> Msg 18456, Level 14, State 1, Server BLUEBOX\SQLEXPRESS, Line 1
> Login failed for user 'MyUserCount'.
> Questions:
> Shouldn't SQL 2005 Express allow more than one user to connect and
> authenticate to the db by default? Are there any settings I need to
> modify in Visual Studio 2005 or SQL Express 2005 in order to allow my
> web app and the sqlcmd command to co-exist and execute without
> interfering with each other?
> My Setup:
> VS 2005
> XP Professional SP2
> SQL 2005 Express
> web.config connection string looks like this:
> <add name="ASPNETDBConnectionString" connectionString="Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated
> Security=SSPI;User Instance=True" providerName="System.Data.SqlClient"/
> I have been going around in circles on this issue, any help would be
> greatly appreciated!
> Sincerely,
> -ralph
>
>
>