locked
Querying SQL Server from Dynamics CRM RRS feed

  • Question

  • Is it possible to query a SQL Server database from within Dynamics CRM 4.0 and then to display the results on CRM form?  For example, suppose I have defined an object/entity called "Server" within our CRM installation. Then suppose there is an external database with information about Servers (actually with more details then what is required by the CRM Server object). Next, suppose I compose a select statement requesting only the relevant fields. Can the result of that query be displayed on CRM form?

    My research suggests that the answer is no. That I must use a SSIS package to transform the external data and store it within Dynamics CRM as CRM data? Thereby creating a intermediate data store (either of the entire database or on the fly as records are requested from the external store). Or am I just misreading things and the SSIS is equivalent to want I am asking for in the first paragraph.

    Thanks.

    William
    Friday, February 6, 2009 10:08 PM

Answers

  • Hello,

    Yes you can!  You can if you want do this with JavaScript and .NET.  You, on the load of the form, can call out to a .ASPX page using javascript and a little bit of AJAX.  You'd build a url dynamically if you have parameters you want to pass to this page.  Then call the page using the url and this ASPX web page, you can execute the sql query, and build the results as an xml string and return it back to the javascript file.

    In javascript you can parse this xml out and set the appropriate fields.  I've done this several times for clients and it's a quick way to query databases. Good luck!
    Danny Varghese Crowe Horwath LLP http://www.crowehorwath.com/cs/blogs/crm
    Monday, February 9, 2009 5:33 AM

  • wlhutchison,

    Yes it is possible. Please check the post:

    http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/0d9aa5e3-6320-4f2f-9c99-30961cb9818f


    Sohaib Ahmad [Business Management Solution Specialist] http://www.solutiontalk.blogspot.com
    Monday, February 9, 2009 11:30 AM

All replies

  • I would think you can though I'm no expert in this. An approach I'd investigate would be to use an Iframe on the CRM form that points to a web page. Design the web page to fetch and display the data from your SQL Database.  You may even be able to set up some interaction so that the data fetched from the database can vary depending on the value of a field on the CRM form.

    I would strongly urge you not to duplicate the data into CRM as I'm sure that is not necessary.

    Saturday, February 7, 2009 8:24 AM
    Moderator
  • Hi,

    If you want to display the data by using the "out-of-the-box" mechanisms and CRM forms, then the answer is: yes you will have to copy the data. However, you can display data from external sources on CRM forms in iframes. Create a very simple page, query the data you want and put that page into an iframe on the CRM form. The users will not realize that the source of the data is an external database, and you will not have to copy the data.
    If you want to query CRM database, remember about using the filtered views, not the tables!


    I hope that helps.
    Regards,
    Kuba Skałbania
    Saturday, February 7, 2009 9:25 PM
  • Hello,

    Yes you can!  You can if you want do this with JavaScript and .NET.  You, on the load of the form, can call out to a .ASPX page using javascript and a little bit of AJAX.  You'd build a url dynamically if you have parameters you want to pass to this page.  Then call the page using the url and this ASPX web page, you can execute the sql query, and build the results as an xml string and return it back to the javascript file.

    In javascript you can parse this xml out and set the appropriate fields.  I've done this several times for clients and it's a quick way to query databases. Good luck!
    Danny Varghese Crowe Horwath LLP http://www.crowehorwath.com/cs/blogs/crm
    Monday, February 9, 2009 5:33 AM

  • wlhutchison,

    Yes it is possible. Please check the post:

    http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/0d9aa5e3-6320-4f2f-9c99-30961cb9818f


    Sohaib Ahmad [Business Management Solution Specialist] http://www.solutiontalk.blogspot.com
    Monday, February 9, 2009 11:30 AM