locked
Retrieve and export all the accounts and theirs fields from the CRM 4 RRS feed

  • Question

  • I have a question for a specific need. I have an application which use sql server database 2005 and I would like to populate the "accounts" table from this database with the "accounts" records from my CRM 4 database. I'm part

    I'm looking for the best way. I was thinking to begin by develop an application which retrieve all the accounts record and theirs fields in the CRM and write them in a file.

    Then I will be able to use this file to import data in the "account" table of my SQL database of my application every day by example to populate by database.

    Do you have any idea to do that because I'm a little lost with that.

    Thanks for your help!

    Friday, September 3, 2010 2:15 PM

Answers

All replies

  • In addition to your approach you have options to use

    1. write window service to transfer data from ms crm account's to custom database (http://msdn.microsoft.com/en-us/library/ms913861.aspx) it is using old crm version but you can implement the same with ms crm 4.0

    2. using SSIS http://blogs.msdn.com/b/crm/archive/2009/12/31/data-integration-migration-using-sql-integration-services-ssis-2008.aspx


    Mahain : http://mahenderpal.wordpress.com
    • Proposed as answer by Donna EdwardsMVP Friday, September 3, 2010 3:11 PM
    • Marked as answer by Jim Glass Jr Friday, September 3, 2010 4:06 PM
    Friday, September 3, 2010 2:39 PM
    Moderator
  • Hi,

    Remember you can also use SQL filtered views to make a direct query to the CRM database.

    This way you dont have to export to any file. just query SQL DB directly.

    Making use of that you may be able to develop some logic that makes a SELECT on your CRM database and INSERTs / UPDATE on your current SQL 2005 DB.

    If you feel familiar with SQL Server Integration Services it may be a very smart option for bulk inserting and/or merging data.

    Hope it helps.

    PP [twitter.com/pabloperalta]

    Friday, September 3, 2010 2:41 PM
  • In addition to the above, rather than build an application, you can use a 3rd Party tool like Scribe.

    Regards, Donna

    Friday, September 3, 2010 3:11 PM
  • Thanks to both of you.

    To simplify, what I'm trying to do is to INSERTs / UPDATE some statements on a MS SQL database on a server with the "accounts" record of the CRM database. So I have two different database on two different server.

    Malhender:

    • your first approach seems interesting  but effectively, the it is using a really old version and as I'm not a good developper, It seems to be really complicated to realise.
    • Your second approach seems easier but your link explain how to do from a external database to the customer.

    pabloperalta:

    • I don't really understand how to use SQL filtered view in this case.
    • You are also speaking about SQL Server Integration Services, like Malhender. Could explain what are the steps to reach my purpose? What can I do with a SSIS package?

    I'm not really a C# developer and I don't know SQL Server Integration but  I need to find a way to solve that, so what is the most simple solution for a noob like me? :-)

    What I don't understand is what I need to do to insert / update  periodically data from the CRM to an external SQL server database. Is there a example or somebody who has already done that?

    Thanks.

     

     

    Friday, September 3, 2010 3:35 PM
  • Hi,

    In your case I would use SSIS. It has wizards, so, making it easier to start. Here you have a start point (http://msdn.microsoft.com/en-us/library/ms140052.aspx) and here too.

    SQL filtered views in the case of accounts refers to the view named 'FilteredAccount' on your CRM account. Querying this view will be filtered by your user's privilege on CRM, so, bear in mind that. You are only going to retrieve the records you have access through CRM.

    Hope it helps.

    PP [twitter.com/pabloperalta]

     

     

    • Marked as answer by Jim Glass Jr Friday, September 3, 2010 4:06 PM
    Friday, September 3, 2010 3:56 PM