Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / CRM ownerid - Assign m...
Microsoft Dynamics CRM (Archived)

CRM ownerid - Assign millions of records with SQL ?

(0) ShareShare
ReportReport
Posted on by Pekka Häkkinen 200

Hi, we have an XRM solution where we process and keep track of end-customers financial holdings and transactions. This means that we can have tens of thousands of records to be assigned when customer owner is changed. We have created workflows and cascading assigns to handle assigns made "manually". But now we should do a total "re-assignment of all customers" resulting in cascading assigns to multiple financial transnational and holding entities.  (stocks, funds ect). This would result in millions of rows re-assigned... and I really doubt how well system will perform there.

So my question is: can we just update ownerid in these entities with direct SQL ?

Cheers,

PEKKA

*This post is locked for comments

  • Suggested answer
    Goutham A Profile Picture
    Goutham A on at
    RE: CRM ownerid - Assign millions of records with SQL ?

    It is not recomended to do so as Assign operation in CRM does/checks many other actions before updating ownerid field in databas. Some depndencies are

    -- All child records needs to be updated accordingly with cascade settings in relationship---owneridtype field needs to be updated appropriately

    --privilege checks needs to be done.

    --if enabled for offline, offline db needs some updates.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: CRM ownerid - Assign millions of records with SQL ?

    I recommend doing these tests in a cloned environment.

    If you found the answer helpful, please mark as Verified 

    Thank You & Best Regards

    Francesco Picchi

    Microsoft Dynamics CRM Consultant, Bologna, ITALY

    Independent Contractor

    http://www.francescopicchi.com

  • Verified answer
    ashlega Profile Picture
    ashlega 34,475 on at
    RE: CRM ownerid - Assign millions of records with SQL ?

    I'd second Ravi in this.. you can, but it can go wrong. Technically, assign operation is, probably, not that different from simply updating ownerid and owneridtype.. you will also have to update owningbusinessunit field. Since I don't have access to the on-prem CRM right now, I would not try giving you the exact queriy, but here is how I'd do it:

    - Turn off all "cascading" assigns

    - Enable SQL profiler

    - Assign one record and see what SQL queries are involved 

    Then run the same queries for all the records (not only those involved in this particular assign, since there will be other entities which you would normally re-assign through the cascading behavior)

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: CRM ownerid - Assign millions of records with SQL ?

    Assign operation over a lot of records is probably one of the worst action to do by SQL Server because on assign all security settings are recalculated.

    You definitely can't do it by sql: please evaluate to use an async workflow (or console application) to run it over millions of records.

    Hope it helps.

    If you found the answer helpful, please mark as Verified 

    Thank You & Best Regards

    Francesco Picchi

    Microsoft Dynamics CRM Consultant, Bologna, ITALY

    Independent Contractor

    http://www.francescopicchi.com

  • Verified answer
    RaviKashyap Profile Picture
    RaviKashyap 55,410 on at
    RE: CRM ownerid - Assign millions of records with SQL ?

    Hi Pekka,

    First of all its unsupported. This means if something goes wrong then MS would not be able to help you. This does not mean you can't do it (you can but at your own risk).

    Answering your question, yes you can update the ownerid directly but you need to find the fields which needs to be updated. CRM stores the data in some related fields as well like owneridtype (which could be either team or user) etc.  

    I would suggest You try this on one record followed by full testing like update, assignment etc for that record.

    Do remember to take a backup before changing anything in SQL.

    Hope this helps.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans