Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
synchronizing crm account entity list with external sql server database. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    In our company we have a sql server database handling all of the company's client accounts. The database is served by an asp.net application at the front end, where we create new accounts for our customers. So all new customer accounts are added, modified or deleted in the sql server database through the front-end asp.net website. Now, for our sales managers we need to put together ms crm system where the account entity should get automatically populated with the data from this external sql server database(which has asp.net website as front-end). How can we automatically populate data in the accounts entity of ms crm 4.0 with the data in the sql server database.

     

    We need to do this on a scheduled basis and we need to ensure that the data in the sql server database and crm system remains the same as we would be using both systems simultaneously. Is there a way to use a synchronized automated solution for just the accounts entity between ms crm and external sql server database??

     

    Please help me..i need to work on this project for my company and i am just a beginner in crm stuff

     

     

     

    Wednesday, June 2, 2010 2:09 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    That's too bad then. In that case I think you'll have to query the source database for changes on regular intervals (e.g. every hour/minute/day , depending on how critical it is to sync the databases). You'll need a datetime field that stores the time when a record was last modified (like the "modifiedon" field in CRM). 

    Then you can query for all records that have been modified since the last sync, you'll probably want to add a field that stores the sync time too.

    Your query will become something like

    SELECT *
FROM Account
WHERE DateDiff(d, modifiedon, lastsync) > 0

    • Marked as answer by android123 Thursday, June 10, 2010 6:29 PM
    Monday, June 7, 2010 6:23 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    As you said you want to do it on a scheduled basis, you can write a window service to create/update account data in ms crm using ms crm webservices.

    For reference check http://msdn.microsoft.com/en-us/library/ms913861.aspx

    Hope it will help you !!!

    Mahain
    Wednesday, June 2, 2010 2:43 PM
    Moderator

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    As you said you want to do it on a scheduled basis, you can write a window service to create/update account data in ms crm using ms crm webservices.

    For reference check http://msdn.microsoft.com/en-us/library/ms913861.aspx

    Hope it will help you !!!

    Mahain
    Wednesday, June 2, 2010 2:43 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    thanks mahendar!!

     

    however, the reference link you have provided is used to update data in sql server when data changes in ms crm system. My situation is different.

     

    i need to update all account and contact records within the crm system whenever, the account records and contact records change in a sql server database which has two tables, one for accounts and one for contacts.

     

    How can i write a windows serivce which captures or detects changes in the sql server database.

     

    also note that the sql server database is completely independent of ms crm system.

     

    Can  u advise me on this?

     

    Saturday, June 5, 2010 8:29 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You can think of MS CRM as just a large SQL database, to simplify your problem. So you have one database with account/contact information, and the CRM database. As I understand correctly, you want to sync the account/contacts database with the CRM database whenever data changes in the first database?

    Since changes are made through a front-end ASP.Net application, I'd say that you have to build some extra logic into that application that also updates the data in the CRM database.

     

    Sunday, June 6, 2010 9:27 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    thanks for your reply. u understood my problem correctly. The technique u have prescribed, i think is very good but the front end asp application is developed and maintained elsewhere and only the database and crm system are under my control.

     

     

    any suggestions on how to detect changes to the database within the sql server??

    Sunday, June 6, 2010 1:57 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    That's too bad then. In that case I think you'll have to query the source database for changes on regular intervals (e.g. every hour/minute/day , depending on how critical it is to sync the databases). You'll need a datetime field that stores the time when a record was last modified (like the "modifiedon" field in CRM). 

    Then you can query for all records that have been modified since the last sync, you'll probably want to add a field that stores the sync time too.

    Your query will become something like

    SELECT *
FROM Account
WHERE DateDiff(d, modifiedon, lastsync) > 0

    • Marked as answer by android123 Thursday, June 10, 2010 6:29 PM
    Monday, June 7, 2010 6:23 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    thats an excellent suggestion!!!

    thanks for your valuable time!!

     

     

     

    Thursday, June 10, 2010 6:29 PM