CRM 2011: Creating SQL Query through MSCRM RRS feed

  • Question

  • I have been asked to create some kind of query through SQL to find all open opportunities that have an activity record associated to that opportunity.

    Is this something that can be done through SQL? If so how do I join these two tables together?

    Thanks in advance

    Monday, March 9, 2015 6:11 PM


  • You can do this in SQL, though you can also easily access this directly in CRM using Advanced Find.

    When accessing data via SQL, the supported route is to use the filtered views, rather than querying the tables directly. Each entity has an associated filtered view, for example the opportunity entity has a filtered view called FilteredOpportunity.

    You can join opportunities to activities using the regaridngobjectid field on the ActivityPointer entity

    select * from FilteredOpportunity o join FilteredActivityPointer a on o.opportunityid = a.regardingobjectid

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Monday, March 9, 2015 8:37 PM