How to tell if database is detached?

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...
rmmarshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

osiris247Commented:
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
o
0
rmmarshAuthor Commented:
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...
0
osiris247Commented:
can you connect with sql management....if so its not detached.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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

0
AGBrownCommented:
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
      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.

Andy
0
ositearCommented:
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.
0
rmmarshAuthor Commented:
The problem was trying to use the connection before it was instantiated... dumb mistake...

Thanks again for the help...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.