How to know is a sql server is not available?

[Don Juan]

Hi I need to create a SP with the following fake code:
(Sql Server 2005)
...
If DB1 is available and DB2 is available
Insert into DB3
Select field1, field2 from DB1

Insert into DB3
Select field1, field2 from DB2
Where status = 1
Else
Insert into DB3
Select 'One of databases is not available','0'
End

What I need is to know if a database is Restoring.. or Offline...

Thanks in advanced

[Dan Guzman]

Post by Don Juan
What I need is to know if a database is Restoring.. or Offline...

Check sys.databases for the desired database database states (online,
read_write and multi-user). You'll also need to execute the conditional
code using dynamic SQL to avoid errors if the databases are not available:

IF (SELECT COUNT(*)
FROM sys.databases
WHERE state = 0 --online
AND is_read_only = 0
AND user_access = 0 --multi-user
AND name IN(N'DB1',N'DB2')) = 2
BEGIN
EXEC sp_executesql N'
Insert into DB3...
Select field1, field2 from DB1

Insert into DB3...
Select field1, field2 from DB2
Where status = 1
'
END
ELSE
BEGIN
EXEC sp_executesql N'
Insert into DB3...
Select ''One of databases is not available'',''0''
';
END
--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Post by Don Juan
(Sql Server 2005)
...
If DB1 is available and DB2 is available
Insert into DB3
Select field1, field2 from DB1
Insert into DB3
Select field1, field2 from DB2
Where status = 1
Else
Insert into DB3
Select 'One of databases is not available','0'
End
What I need is to know if a database is Restoring.. or Offline...
Thanks in advanced

[Dooza]

Post by Don Juan
(Sql Server 2005)
...
If DB1 is available and DB2 is available
Insert into DB3
Select field1, field2 from DB1
Insert into DB3
Select field1, field2 from DB2
Where status = 1
Else
Insert into DB3
Select 'One of databases is not available','0'
End
What I need is to know if a database is Restoring.. or Offline...
Thanks in advanced

There is this useful SP that can check remote or linked servers:
http://www.sqldbatips.com/showcode.asp?ID=38

Steve

[Don Juan]

Thanks!

Post by Dooza

Post by Don Juan
(Sql Server 2005)
...
If DB1 is available and DB2 is available
Insert into DB3
Select field1, field2 from DB1
Insert into DB3
Select field1, field2 from DB2
Where status = 1
Else
Insert into DB3
Select 'One of databases is not available','0'
End
What I need is to know if a database is Restoring.. or Offline...
Thanks in advanced

http://www.sqldbatips.com/showcode.asp?ID=38
Steve

[Damien]

Post by Don Juan
(Sql Server 2005)
...
If DB1 is available and DB2 is available
    Insert into DB3
    Select field1, field2 from DB1
    Insert into DB3
    Select field1, field2 from DB2
    Where status = 1
Else
    Insert into DB3
    Select 'One of databases is not available','0'
End
What I need is to know if a database is Restoring.. or Offline...
Thanks in advanced

You might do better to wrap the two inserts in a TRY/CATCH and a
transaction - what if the databases disappear between the time the
check runs and the time the insert runs?

Damien