Have you ever needed to quickly get your hands on Dynamics 365 CRM online data? Whether it be for reporting, SQL query writing with advanced joins, troubleshooting or just curiosity about tables and fields behind the scenes, sometimes you just need to see the data. If you’re like me, there is nothing like getting your hands on the straight SQL. Luckily, Scribe Insight offers a quick and easy method to copy Dynamics 365 online data to SQL Server.
Dynamics 365 Online Replication Methods
There are several ways to keep a replicated copy of your CRM online database in either an on-premise or cloud database. Scribe software has been doing this for some time with their replication service (https://www.scribesoft.com/resources/scribe-online-replication-services-rs ). Microsoft has more recently released the ability to replicate your database to an Azure instance (https://technet.microsoft.com/en-us/library/mt744592.aspx ). A full time replicated database has many advantages for support, reporting, and Business Intelligence. If you don’t already have such a database set up, here is a quick way to grab a table using Scribe Insight.
Using Scribe Insight to Copy Dynamics 365 Online Data to SQL Server
First, connect to your CRM online instance for both the source and target connection. For our example, we are going to grab a copy of the activityparty entity as it is an often-misunderstood entity.
For the target connection, create a seek step to the same entity as your source (activityparty in this instance).
The next step will seem a little strange but, should make sense soon. Create a lookup link from a field on the source that does not and never will match the linked field on the target. The idea is that every seek for every row will fail and we will capture the activityparty entity using the rejected source rows feature of Scribe. In our case, we will lookup from activitypartyid to activityid
Next well will configure the Rejected Source Rows. From the Run menu click Edit Settings and go to the Rejected Source Rows tab.
If you’ve never use rejected source rows, what this feature does is allow you to output any failed rows from the execution of a job to a table or file. For this exercise, we will output the failed rows to a new table in the Scribe Internal database. Notice that we do not have to go through the exercise of creating the table in SQL either manually or by trying to hunt for the TSQL for the table schema. Scribe creates the table for us.
The final thing we need to do is set the Seek step to fail on a success of 0.
Just click the “Log an Error” on that step and specify an error message if you like.
Now run the job!
You will notice that every row will fail because we will not find an instance of the activitypartyid matching the activityid in this table.
That’s it! You now know how to easily copy Dynamics 365 Online data to SQL Server. Just open SQL Server Management Studio to explore the new table.