How to get list of publication recipients using query builder?

[Former Member]

Does anyone know how to build a query in the query builder tool that will return a list of recipients (both enterprise and dynamic) that are assigned to a given publication?

Thanks,

George

[ted_ueda · Accepted solution]

To get a list of Principals associated with a Publication:

Select SI_CUID, SI_KIND, SI_NAME From CI_SYSTEMOBJECTS, CI_INFOOBJECTS Where CHILDREN("SI_NAME='Publication-Principal'", "SI_NAME='<Publication Name here>' And SI_INSTANCE=0")

assuming your Publication has a Unique Name that you enter in <Publication Name nere>. If not, you will have to refine the restrictive clause "SI_NAME='<Publication name here>' and SI_INSTANCE=0" to further specify your Publication.

To get a list of Excluded Principals associated with a Publication:

Select SI_CUID, SI_KIND, SI_NAME From CI_SYSTEMOBJECTS, CI_INFOOBJECTS Where CHILDREN("SI_NAME='Publication-ExcludedPrincipal'", "SI_NAME='<Publication Name here>' And SI_INSTANCE=0")

Note that retrieving the Dynamic Recipients are problematic, since they Names and Email addresses are retrieved from the source when the Publication is executed, so is not there in the Query Builder results.

What you can get from Query Builder is the list of 'Unique Identifiers' that's been specified when you associate the Dynamic Recipients to the Publication (usually primary keys displayed in a Crystal Report or Web Intelligence document).

You can get this info via:

Select SI_CUID, SI_NAME, SI_DYNAMIC_RECIPIENTS, SI_EXCLUDED_DYNAMIC_RECIPIENTS Where SI_KIND='Publication' And SI_INSTANCE=0 And <enter clause here to identify the publication here>

Sincerely,

Ted Ueda

[Former Member · Accepted solution]

Ted, that worked great! Thank you very much for sharing your help!!!

-George

[Former Member]

How about some modification of the following:

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND = 'Publication' and SI_INSTANCE = 0

I don't have any publications (with enterprise/dynamic recipients) to test this on.