How to tell if database is detached?

Posted on 2006-04-10
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
    LVL 7

    Expert Comment

    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

    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...
    LVL 7

    Accepted Solution

    can you connect with sql management....if so its not detached.

    Author Comment

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

    LVL 12

    Assisted Solution

    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 ( state column, which returns
          0 = ONLINE
          1 = RESTORING
          2 = RECOVERING
          3 = RECOVERY_PENDING
          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.

    LVL 1

    Expert Comment

    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

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

    Thanks again for the help...

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Suggested Solutions

    Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
    We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now