How to know is a sql server is not available?

> 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/


"Don Juan" wrote in message
news:F34A8A8E-D192-4B41-81D4-C30AFFBB982Datmicrosoftdotcom...
> 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

Don Juan wrote:
> 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

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

Steve

On Oct 2, 11:05*am, "Don Juan" wrote:
> 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

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

Thanks!

"Dooza" wrote in message
news:e5v3ix0QKHA.4244atTK2MSFTNGP06dotphx.gbl...
> Don Juan wrote:
>> 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
>
> There is this useful SP that can check remote or linked servers:
> http://www.sqldbatips.com/showcode.asp?ID=38
>
> Steve