Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How to tell if database is detached?

Posted on 2006-04-10
Medium Priority
Last Modified: 2008-01-16
I have a C# program that under Windows XP is unable to connect to a SQL Server Express database.  The logs indicate there is a problem with the logon id, but the users chose Windows Authentication method, so there shouldn't be an issue with user id or password, right?  I am wondering if the database is detached from the server... if so, is there a way I can tell?  how about telling programmatically?

Thanks for your help...
Question by:rmmarsh

Expert Comment

ID: 16420561
Not sure you can test if a database is detached specifically from C#, you can certainly handle SQLexceptions thrown from the calling command.

When you say use windows logon, you must have their windows logon added to the users in SQL.  If they are on the localhost then normally this is set during installation and will be fine...if they are from another machine it will use their windows logon.

I suggest to use a SQL login, and test that.

Can you connect to SQL with SQL management studio and query the database?

hopt this helps

Author Comment

ID: 16420644
The connection using SQL Management is next... have sent an email to user asking them to download it and give it a try... I will post the results here as soon as I get them...

THanks for your help...

Accepted Solution

osiris247 earned 750 total points
ID: 16420701
can you connect with sql management....if so its not detached.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 16420741
good point... I'll post the results as soon as I get them...

LVL 12

Assisted Solution

AGBrown earned 750 total points
ID: 16421525
In SQL 2000 you could tell the status of a database using the system tables, and the master database. In 2k you could do:

      SELECT name, status FROM master.dbo.sysdatabases

If the database is attached, it has a row in here, and the status is in the status column, which is made up of the following bit flags:
      1: autoclose; set with sp_dboption.
      4: select into/bulkcopy; set with sp_dboption.
      8: trunc. log on chkpt; set with sp_dboption.
      16: torn page detection, set with sp_dboption.
      32: loading.
      64: pre recovery.
      128: recovering.
      256: not recovered.
      512: offline; set with sp_dboption.
      1024: read only; set with sp_dboption.
      2048: dbo use only; set with sp_dboption.
      4096: single user; set with sp_dboption.
      32768: emergency mode.
      4194304: autoshrink.
      1073741824: cleanly shutdown.

In 2005 the system tables have been deprecated in favour of the new system views. There are compatibility views that are provided, so you could use the sys.sysdatabases view's status. Alternatively you could use the new sys.databases (http://msdn2.microsoft.com/en-us/library/ms178534(SQL.90).aspx) state column, which returns
      0 = ONLINE
      1 = RESTORING
      2 = RECOVERING
      4 = SUSPECT
      5 = EMERGENCY
      6 = OFFLINE
However, you still need to grant the appropriate permissions to select on that view to the users running the application, but it would give you a guaranteed way of checking the database state, and even if it was attached in the first place.


Expert Comment

ID: 16447807
on what andy said about permissions, try creating a view over that view for your user, so you don't have to grant access to your DB structure.

Anyway, those kind of situations SHOULD be handled by a try {} catch {} statement.

Author Comment

ID: 16581978
The problem was trying to use the connection before it was instantiated... dumb mistake...

Thanks again for the help...

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question