Some time ago, we had a request from one of our clients to compile a list of all scheduled reports in their Cognos 10 environment. They had over 2000 schedules which were not maintained, so a lot of the recipients were no longer valid, some schedules needed to be stopped, etc. One way to check the recipients list in the scheduled report is to open the schedule for the specific report and check the To, CC and BCC fields. Given the sheer amount of the schedules, checking the schedules one by one was simply not an option.
What came to my mind was to compile this list from the Cognos Content Store. I started going through documentation, forums and discussions to figure out the needed database tables to construct a query. As it turned out, the Cognos Content Store is pretty much in the shadows when it comes to information about the data stored inside. I did find a lot of threads opened by people facing the same problem. It is not too hard to find a query which would generate a list of scheduled reports with their names rather than IDs, but the bottom line regarding the retrieving the recipients list on all of these places was pretty much the same. They all concluded that in order to retrieve the recipients list, you would need a third-party tool or check the schedules manually. As stated before, I could not check 2000+ schedules, and a third-party tool was a luxury I could not afford. So, I had to figure something out. After extensive digging, I came to the following finding:
Cognos Content Store has a neat table CMOBJPROPS26, and that table has a column DELIVOPTIONS. This column in massive in size, since it contains all kinds of data. When it comes to reports, it contains prompts, delivery formats AND the recipients, if any. Based on a query for the scheduled reports I found, I constructed the following one:
select ob2.cmid, c.name as className, n.name as objectName,
o.DELIVOPTIONS as DeliveryOptions
from CMOBJPROPS2 p
inner join CMOBJPROPS26 o on p.cmid=o.cmid
inner join CMOBJECTS ob on ob.cmid=o.cmid
inner join CMOBJECTS ob2 on ob.pcmid=ob2.cmid
inner join CMOBJNAMES n on n.cmid=ob2.cmid
inner join CMCLASSES c on ob2.classid=c.classid
where ACTIVE = 1 order by objectName
You can export the output of this query to Excel. The DeliveryOptions column will contain a lot of useless information (at least for me in this given situation). To ease the search, I used the nice Find and Replace option to eliminate them, for example, Find burst; and Replace With blank space. Make sure that you put the semicolon in the Find field or you may end up losing some valuable data (for example, if some of the reports have burst in the name, if you do not use the semicolon, it will be deleted as well). In the most cases, the emails are contained in the tag <item xsi:type=”bus:addressSMTP”>RECIPIENT_EMAIL</item>. However, try to find them after you remove all the other data which are not related to the email address. You may even try to adapt the original query to try and narrow down the search, however, I would strongly recommend to use it as is, and spend a bit more time clearing the Excel, rather than risking an entry loss.
If you have any additional questions regarding this, please contact me, and I’ll be happy to reply.
Jana Georgievska
DBA

One of the relatively common situations during disaster recovery is a suspect database. The suspect flag is a mode which is set to the database by SQL Server in several cases. When it comes to disaster recovery, mainly databases are marked as suspect in case of a hardware failure, improper shutdown of the DB server, DB files corruption, etc. Strangely enough, it can happen when you restore the entire DB server from a valid snapshot (it happened to me).

In such cases, you will need to bring the database back to online mode. At this point you need to be aware that during the process there may be some data loss. This can occur if there are incomplete transactions which will need to be rolled back. In general,

What you will need for the procedure is the SQL Server Management Studio. I will use the Prime database which I corrupted in the previous post 🙂

The first thing you need to do is to turn off the suspect flag (which does not mean that the DB is fixed). You will need to execute the following command:

EXEC sp_resetstatus ‘Prime’

You will get the following message:

The next step is to set the database into emergency mode, which will make it a read-only with the following command:

ALTER DATABASE Prime SET EMERGENCY

Then, perform a consistency check. The output will display any possible errors.

DBCC checkdb(‘Prime‘)

In the next step we do a rollback of any pending transactions. This step also brings the DB in a single user mode.

ALTER DATABASE Prime SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Finally, we will correct any reported errors. Keep track of the time, since this may be a lengthy process.

DBCC CheckDB (‘Prime‘, REPAIR_ALLOW_DATA_LOSS)

To wrap up the procedure, we will need to enable the multi user access and bring it online.

ALTER DATABASE Prime SET MULTI_USER

If we check the DB status now, it will be shown as online. We can browse through the tables and query data:

select DATABASEPROPERTYEX(‘Prime‘, ‘status’)

Jana Georgievska,

DBA