Query an SQL server

(0) Share

Report

Posted on by Community Member

We have a number of different systems that require access to a postcode lookup table, our two separate CRM systems also,currently, have the postcodes added as entity within them as well.

What I would like to do is write a webresource to access a centralised SQL server table can I do this, if so any pointers as to how?

The CRM systems are Dynamics CRM 2011 and are both on premise.

regards,

Matt

*This post is locked for comments

I have the same question (0)

All responses (26)

Answers (0)

Suggested answer

Drew Poggemann 4 on at

Like (1)

Report

Hi Matt,

Please check out the following: community.dynamics.com/.../206637

Hope this provides an approach for you.

Was this reply helpful? Yes No
Suggested answer

RaviKashyap 55,410 Moderator on at

Like (0)

Report

Hi Matt,

Postcode won't change that often so why ca't you just import all the postcodes from your sql table into CRM postcode entity?

If you still want some kind of sync between the data in CRM and you sql table, you could write a console app which updates the records in crm from sql. You could also schedule it using windows yask.

Hope this helps.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

The idea is to hold the postcodes in one centralised area, it's only used as a lookup, theres 28 million records in total.

Once you hold this amount of data three or four times the storage requirements and also updating them becomes troublesome to say the least.

Matt

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Drew,

Thanks again for your help.

It looks a bit beyond my skillset at the moment - as I haven't got a clue what it is talking about (being honest).

Provides me with an additional research line though, if I find a more detailed step by step example I'll post a link up as it may help people in the future.

Matt

Was this reply helpful? Yes No
Suggested answer

RaviKashyap 55,410 Moderator on at

Like (1)

Report

Hi Matt,

I didn't know you have 28 million records for postcode. I agree replicating this much data for each system is not a good idea. In version 9 we do have something called as virtual entities but as you are in CRM 2011, the best way to do this is through html web resource. Its the same thing mentioned in the the Link Drew has shared.

You could refer the following links which may give you an idea on how to start on this-

crmbook.powerobjects.com/.../html-web-resources

arunpotti.wordpress.com/.../html-webresource-example-in-crm

docs.microsoft.com/.../gg327945(v=crm.5)

Hope this helps

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for the help, its the WCF I am struggling with as the examples googled seem to fail - or I am doing something wrong.

regards,

Matt

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I also forgot to add the annoying thing is googling wcf and sqlserver people appear up in arms over whether it should be done, never be done, etc.

For a newbie to all of this you'd have thought there would be a bit more clarity as to the accepted way to do this.

Matt

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ok following a youtube video: www.youtube.com/watch I've managed to create a WCF service and run it on my local machine, I can test it by running it in visual studio, it runs and returns the data as expected.

So how can I test this from within CRM and a webresource, do I have to have the service hosted in IIS on the CRM server - any pointers as to how I do this?

Second question once the service is hosted how do I call the service - I want the user to input a postcode within CRM then click a button within the html webresource which invokes the service, upon retrieval of the results it then displays them on the HTML webresource (in a grid) alowing the user to select one to populate the forms data fields.

Any examples would help.

regards,

Matt

Was this reply helpful? Yes No
RaviKashyap 55,410 Moderator on at

Like (0)

Report

Hi Matt,

It would not be easy to implement what you are looking for as it requires skills on various technologies (WCF, IIS Hosting, Consuming WCF, Rest endpoint, javascript, HTML, and of cource web resources in CRM).

So on a high level-

1. You need to create a Rest Based WCF service which connects to your SQL and returns the data. You need rest based so that you can easily use it using javascript.

www.codeproject.com/.../Create-RESTful-WCF-Service-API-Step-By-Step-Guide

www.codeproject.com/.../Create-RESTful-WCF-Service-API-Step-By-Step-Guide

2. You need to create a HTML page. Within that page you need to call your WCF service using java script and then load the result in a dropdown.

3. You also need to add some logic to pass the selected value back to CRM. This will require CRM XRM javascript.

I know this i very high level and not exactly provides you what you are looking for :(

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ravi,

Thanks for the reply.

I've got the WCF service working on my PC, it connects, queries and retrieves the data I require (tested this by running it through visual studio).

I've got the html Webresource which currently is sat querying the CRM system it has been added to - it queries and displays the results in a grid, populates the fields as required.

I'm just at the stage of trying to work out how\where to host the service and also have the webresource send the request to the service for the data.

regards,

Matt

Was this reply helpful? Yes No