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

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" <Juanete (AT) gmail (DOT) com> wrote

Don Juan wrote:
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" <Juan... (AT) gmail (DOT) com> wrote:
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" <steveNO (AT) SPAM (DOT) dooza.tv> wrote