CRM ownerid - Assign millions of records with SQL ?

(0) Share

Report

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

I have the same question (0)

All responses (5)

Answers (3)

Suggested answer

Goutham A 2 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

ashlega 34,477 on at

Like (1)

Report

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)

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (1)

Report

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

Was this reply helpful? Yes No
Verified answer

RaviKashyap 55,410 Moderator on at

Like (1)

Report

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.

Was this reply helpful? Yes No