Detach SQL Server Exress 2005 Database on Form Startup

[Guest]

I am developing a windows application that uses a SQL Server Express 2005
database back-end. I am currently connecting using user instances and I am
also using a data access layer. I am trying to implement some automatic
program fixing techniques during application startup; i.e. if the previous
time the user ran the application it crashed and left the database connection
open, then it could safely close and re-open the connection on startup again.

To reproduce this problem, I'm intentionally throwing an exception in my
application so that it can't exit properly. I notice that when I use the
SSEUtil.exe program, it shows the database as still being attached after a
crash. I would like to be able to check to see if the database is already
attached, detach it, then attach it again with my current data layer
(connection objects).

I have 2 questions for this problem; On the startup of my application Is
there a way to check to see if a database is currently attached and...
- just use it in my objects in the SQL Server Exress 2005 environment?
- detach the current connection and re-attach my own instead?

I don't want to have to implement the SSEUtil during install if possible.
My application is going to be deployed as a Windows Service AND a Windows
Application. As a service, it is going to monitor files and write the logs
to the database. Then, when the user logs into the machine, it will open the
client application and access the same database as the service. Since it is
possible for both the service and the client app to run at the same time, is
it possible for both of them to access the same database at the same time on
the same machine? I'm open to suggestions and I appreciate your help. Thanks
in advance.

 

[Bryan Phillips]

If you want to know if the database is attached, run a query against the
sysdatabases table in the master database. You can detach a database by
running the proc sp_detach_db 'dbname' and attach a database by running
sp_attach_db 'dbname', 'mdf file path', 'ldf file path (optional)' .



Any number of processes can access the database if mounted/attached
normally. User instances cannot be shared to my knowledge.



Bryan Phillips

MCSD, MCDBA, MCSE

Blog: http://bphillips76.spaces.live.com

I am developing a windows application that uses a SQL Server Express 2005

database back-end. I am currently connecting using user instances and I am

also using a data access layer. I am trying to implement some automatic

program fixing techniques during application startup; i.e. if the previous

time the user ran the application it crashed and left the database connection

open, then it could safely close and re-open the connection on startup again.

To reproduce this problem, I'm intentionally throwing an exception in my

application so that it can't exit properly. I notice that when I use the

SSEUtil.exe program, it shows the database as still being attached after a

crash. I would like to be able to check to see if the database is already

attached, detach it, then attach it again with my current data layer

(connection objects).

I have 2 questions for this problem; On the startup of my application Is

there a way to check to see if a database is currently attached and...

- just use it in my objects in the SQL Server Exress 2005 environment?

- detach the current connection and re-attach my own instead?

I don't want to have to implement the SSEUtil during install if possible.

My application is going to be deployed as a Windows Service AND a Windows

Application. As a service, it is going to monitor files and write the logs

to the database. Then, when the user logs into the machine, it will open the

client application and access the same database as the service. Since it is

possible for both the service and the client app to run at the same time, is

it possible for both of them to access the same database at the same time on

the same machine? I'm open to suggestions and I appreciate your help. Thanks

 

[Linda Liu [MSFT]]

Hi Jon,

This is a quick note to let you know that I am performing research on this
issue and will get back to you ASAP.

I appreciate your patience.


Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Did you get a chance to do the research on this Linda? Let me know what you
find out.

Thanks for your reply Bryan. I'll be using the info you provided to try
some things within my project to see if I can come up with some solutions
myself based on your suggestions while I wait for Linda's response also.

 

[Linda Liu [MSFT]]

Hi Jon,

Sorry for my delayed reply.

I am trying to implement some automatic
program fixing techniques during application startup; i.e. if the previous
time the user ran the application it crashed and left the database connection
open, then it could safely close and re-open the connection on startup

again.

Do you mean that you'd like to check if the database that the application
attached in the previous time is still attached to the user instance when
the application starts up again, and if yes, detach the database and then
attach it again? Is your application always uses the same database data
file and attach it as a database with the same name? If so, I don't think
you need to do like that.

Suppose that for the first time you run the application, the database has
not been attached to the user instance. Then the application attachs the
database data file to the user instance. The application exits properly and
the database is still attached(no matter the application exits properly or
not, the database remains attached to the user instance). When you run the
application for the second time, the code of attaching the same database
data file as the same database name has no effect and the application will
use the database that has been attached.

Could you please tell me why you want to detach the database if it has been
attached and then re-attach it again?

If you do want to check to see if the database is currently attached, I
think there's a simple way to do it. You could remove the
"AttachDBFileName" keyword from the connection string of the SqlConnection
object and call the Open method of the SqlConnection to connect to the user
instance. If an exception occurs, it means that the database has not been
attached yet. If the database has already been attached, you just could use
it and needn't detach it and then re-attach it again.

Since it is possible for both the service and the client app to run at the same time, is
it possible for both of them to access the same database at the same time

on the same machine?

When the client application and service run at the same time, both of them
connect to the same user instance and use the same database and it is
possible. What's impossible is that different user instances use the same
database data file at the same time.

Hope this helps.
If you have anything unclear, please feel free to let me know.



Sincerely,
Linda Liu
Microsoft Online Community Support

 

[Guest]

Thanks for the reply Linda. Your info helps a little. From what I
understand, User Instances are meant for each user to have their own database
independent of each other, so if UserA edits db info then UserB won't see
those changes, because they have their own copy of the DB, which Windows
stores in each user's hidden application data folder within their Windows
profile. I think after reviewing that info, I don't want to use User
Instances. I want to attach one database on the file system within my
Windows Form App folder, then I want both my Windows Service and my Windows
Form Application to connect to them at the same time. Your post below helped
me understand some of the logic with attaching a database.

If I understand you correctly, then I should be able to create my DB
connections using a connection string that assumes the DB is already attached
to my local SQL Express Instance and not use the AttachDBFilename property.
If it fails, then I can try calling the same connection string with the
AttachDBFilename property to see if I can restore the connection if it has
timed-out or detached in the background. This would help me achieve my
original goal of reconnecting if there was a failure.

You ask why this was a problem for me orignally? I have an installation
package that installs my Windows Service and my Windows Form Application. I
ran the installation package on my development machine and tried running the
application. It threw and exception, because the DBName I created within my
connection string was already in use within my development environmen, even
though the databases were in completely different folders on my system. It
is the duplicate DBName that is conflicting within the SQLEXPRESS Instance.
To get around that, I could simply have a different development and
production name for the attached db, or I could leave it nameless and have it
default to the actual full path and filename of the file. I just thought I
would get fancy and try to do some checking in the background to control the
attach/detach of my databases.

The one question I do have is this... I have seen where the log file gets
corrupted somehow during development and I can't attach the DB. I have to
manually delete the dbname_log.ldf file in the same folder and try again. Is
there a property I can set in the connection string to overwrite or re-create
the log file if it won't mount?

I'll try your idea of only sending the AttachDBFilename property in my
connection string only when the first call without it fails. I'll post my
results on that when I have them. Thanks.

 

[Linda Liu [MSFT]]

Hi Jon,

Thank you for your reply and detailed explanation.

Your understanding of user instance is correct.

It seems that your WinForms appilcation and Windows Service use two
different database data file but attch them with the same database name.

Suppose we have two applications. It's possible for the two applications to
attach the same database data file (e.g. c:\AttachDB\MyDBFile.mdf) with
different database names. But it is not allowed to attach different
database data file(e.g c:\AttachDB\MyDBFile.mdf and
d:\AttachDB\MyDBFile.mdf) with the same database name.

You could specify two different database names in your WinForms application
and Windows Service by setting the 'Intial Catalog' property in the
connection string. Alternatively, you could also leave the database
namesless as you said.

I have seen where the log file gets
corrupted somehow during development and I can't attach the DB. I have to
manually delete the dbname_log.ldf file in the same folder and try again. Is
there a property I can set in the connection string to overwrite or re-create
the log file if it won't mount?

I have spent some time researching on the problem but unfortunately I
haven't found the answer yet. But I think you could delete the
dbname_log.ldf file in your program by calling the File.Delete method. I
will go on research on this problem and get it back to you ASAP.

Hope this helps.
If you have any concerns, please feel free to let me know.


Sincerely,
Linda Liu
Microsoft Online Community Support