I am using 2 DBs union in a scheduled report. In dev environment I noticed 1 DB is down and report shows an "log on error becuz of one DB down" What i want is how can i notify the users if the scheduled report did not run becuz of 1 Database down ,out of 2 Database
The actual technical solution depends on which RDBMS you are using. For SQL server it would be a matter of running a report on a system view:
select name,database_id,state_desc from [master].sys.databases
state_desc will be 'offline' for DB's that are not accesible.
It is then a matter of scheduling a monitoring report prior to the actual report
Quote from: blom0344 on 19 Jan 2011 06:28:17 AM
The actual technical solution depends on which RDBMS you are using. For SQL server it would be a matter of running a report on a system view:
select name,database_id,state_desc from [master].sys.databases
state_desc will be 'offline' for DB's that are not accesible.
It is then a matter of scheduling a monitoring report prior to the actual report
How would cognos be able to query the view if the database if offline? The error the OP is getting suggests he can't even log on,nevermind query an internal database table.
A solution might be to use the metadata service through the sdk to "check" the connection each database periodically just like the "Test connection" feature in the portal. Assuming a database is down, an email could be sent/report bursted.
Well, generally a database is part of an instance (database server) A database can be taken offline while the other databases and instance are functioning normally. In case of SQL server one can then still query the views in the master database (provided one has the authority)
Or in more general terms , database related information is handled at the instance level. The OP does not mention which RDBMS he/she is using, so mine was just a proposition..