Find mail-addressess with CSP Query Builder 1

[goranm]

Hello !
We are using CE 10.

We have lot of reports that we schedule and email.

Now the customer are going to change email-addresses.

How do the query looks like that I can use in the CSP Query Builder to find all objects with mailadresses that ends with '@.yyy.com' ?

SELECT
* FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' and ?????

I can se that the information can be found under SI_DESTINATION and SI_ALERT_DESTINATION (SI_MAIL_ADDRESSES),
but I don´t know how to write the query.

Thanks
Goran

 

[KingfisherINC]

You cannot directly access these lower level properties from the Query Builder. You would need to access them via the SDK.

You can get ALL the destination info with a query like this

SELECT SI_SCHEDULEINFO.SI_DESTINATION 
FROM CI_INFOOBJECTS 
WHERE SI_PROGID = 'CrystalEnterprise.Report'
AND SI_SCHEDULEINFO.SI_DESTINATION != NULL

or

SELECT SI_SCHEDULEINFO.SI_ALERT_DESTINATION 
FROM CI_INFOOBJECTS 
WHERE SI_PROGID = 'CrystalEnterprise.Report'
AND SI_SCHEDULEINFO.SI_ALERT_DESTINATION != NULL

But they are very expensive queries.

Kingfisher

 

[goranm]

Thank you Kingfisher.

I have tried your queries.

I have for the moment 475 objects in the system, and the query wasn´t expensive at all.

But the first query get me all 475 objects.

And I really get to know which objects that have the wrong email-addresses.

In what way do you use and access the SDK ?

I havn´t used that at all.

Thanks again.

 

[Turkbear]

Hi,
You can Copy and Paste that query ( or any you can write, using CE's Query Language) directly into QueryBuilder - it works fine..It is easier, when just information is needed, than writing all the code needed to access the InfoObjects...


Be sure to be logged in as an administrator to be sure you see all instances..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[goranm]

Thanks for trying to help me, but I can´t find the right way to produce the query.
I have tried this one:

SELECT
* FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' and SI_OBJID = 'SI_DESTINATION' and SI_MAIL_ADDRESSES like '%@yyy.com%'

I have also tried
SELECT
* FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' and SI_MAIL_ADDRESSES like '%@yyy.com%'

But I get this message:
Number of InfoObject(s) returned: 0
Number of InfoObject(s) found: 0

So it seems that I don´t know the right way to write the query.

/Goran

 

[Turkbear]

Hi,
Try without the SI_MAIL_ADDRESSES Where clause to be sure the first part is working -- If OK, then use * as the wildcard for the SI_MAIL_ADDRESSES part..




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[goranm]

Hi again !

Yes, the first one works fine.

I used a * instead, but I still got the same result.

Number of InfoObject(s) returned: 0
Number of InfoObject(s) found: 0

/Goran

 

[Turkbear]

Hi,
After running without the SI_MAIL_ADDRESSES part, can you see some SI_MAIL_ADDRESSES in the query results?

Try a literal one as a test



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
After reviewing the Query Landuage Guide, IT appears that you cannot directly query the SI_MAIL_ADDRESSES property, since it is a part of a Property Bag or Collection.

You would need to create code ( in an ASP page for instance - using VBScript ) that,after creating the InfoObject object, uses it to query the ScheduleInfo object and its properties.

Look at the Query Reference in the CE_SDK help file, for the examples of setting the Destination..It will show you how to query for it as well by adapting the code they supply.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[goranm]

Ok, Thank you very much .

I will give it a try asap.

/Goran