locked
Sql Query to Pull All Active Accounts, Contacts and Inside and Outside Salesreps RRS feed

  • Question

  • I have a need to pull all active accounts with their associated contacts and inside and outside salesreps.  Does anyone know the correct sql that would do this? I have tried multiple queries, but nothing has really worked.
    Thursday, July 6, 2017 12:46 PM

All replies

  • Hello,

    I'm not 100% sure what does "inside and outside salesreps" requirement mean. With associated contacts do you mean child contacts of account or primary contact of account?


    Dynamics CRM/Business Solutions MVP
    Read My blog


    Thursday, July 6, 2017 2:49 PM
    Moderator
  • I think that inside and outside salesreps are unique to us. And yes, I need the child contacts of account of primary contact of account.
    Thursday, July 6, 2017 3:56 PM
  • Try to use following query:

    Select
    a.acountid
    ,a.name
    ,c.contactid
    ,c.fullname
    From FilteredAccount a
    Left Join FilteredContact c on a.accountid = c.parentcustomerid
    Where a.statecode = 0


    Dynamics CRM/Business Solutions MVP
    Read My blog

    Thursday, July 6, 2017 4:00 PM
    Moderator
  • It executed fine, but returned no rows.
    Thursday, July 6, 2017 4:12 PM
  • What account have you used to connect to SQL? Windows account or SQL Server account? You should use Windows Account.

    Dynamics CRM/Business Solutions MVP
    Read My blog

    Thursday, July 6, 2017 4:23 PM
    Moderator
  • I am using MS SQL Server Management Studio on the server itself.
    Thursday, July 6, 2017 4:42 PM
  • The question was - what account have you used to connect to SQL Server and DB - Domain account or SQL Server account. If my question is not clear please check this article - https://docs.microsoft.com/en-us/sql/relational-databases/security/choose-an-authentication-mode

    And again - use Windows Authentication to connect, don't use SQL Server Authentication.


    Dynamics CRM/Business Solutions MVP
    Read My blog

    Thursday, July 6, 2017 4:49 PM
    Moderator
  • Based on that note, I am using a sql server account.
    Thursday, July 6, 2017 5:06 PM
  • Based on that note, I am using a sql server account.
    That answers question why query doesn't return any records. As I wrote earlier - use windows authentication instead.

    Dynamics CRM/Business Solutions MVP
    Read My blog

    Thursday, July 6, 2017 5:39 PM
    Moderator
  • try this

    Select a.acountid ,a.name ,c.contactid ,c.fullname From Account a Left Join Contact c on a.accountid = c.parentcustomerid Where a.statecode = 0


    Hope this helps. ----------------------------------------------------------------------- Santosh Bhagat If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    Friday, July 7, 2017 11:37 AM
  • try this

    Select a.acountid ,a.name ,c.contactid ,c.fullname From Account a Left Join Contact c on a.accountid = c.parentcustomerid Where a.statecode = 0


    Hope this helps. ----------------------------------------------------------------------- Santosh Bhagat If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    And this will give access to all the accounts and contacts without respect to security settings and sharing.

    Dynamics CRM/Business Solutions MVP
    Read My blog

    Friday, July 7, 2017 1:32 PM
    Moderator