Re: Connection issues between .Net framwok and SQl Express 2005

On Nov 19, 2:04 pm, "Cowboy \(Gregory A. Beamer\)"
<NoSpamMgbwo...@xxxxxxxxxxxxxxxxxx> wrote:
It does allow more than one user.

The problem you are experiencing is the installation of the membership
database is a destructive "update" that requires an exclusive lock. If you
want to prove this, try setting up a desktop app and a web app with the same
connection string and play with both at the same time.

Solution: Recycle the web application.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************"RGF" <Raf.Figue...@xxxxxxxxx> wrote in message

news:c3b107dc-5dee-464b-88d6-dedd6a1ac528@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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
.Net Framework 2.0
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

Thanks Gregory,

Is there a way to make the the membership database a non destructive
"update"? so that it does not require an exclusive lock.

Also with respect to your suggested solution <Recycle the web
application.>, could you suggest the best implementation to recycle
the web app without breaking the user authentication?

-ralph

.

Relevant Pages