Calling CRM Web Service from SQL Server

Past few days ago, someone asked me "Can we call Web Service from SQL Server?"

So, I simply google "call web service from sql server". wOW! It return me many kinds of samples/articles.

I referred one of the samples + doing some research....

ITS WORKS!!

Creating the CRM Web Service

Let’s start with creating our .NET assembly with the custom stored procedure.  Using Visual Studio 2005, create a new Database Project:

So first, we generate the proxy file for our webservice with the wsdl.exe tool in C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. We have to convince this tool however, to generate code using the ‘old’ asynchronous way (without the Async methods). Only proxy classes generated that way can be loaded with external access privileges. If we would use the ‘new’ proxy classes, we have to use unsafe as security level, which can better be avoided. We can do this by using a configuration file. First create a configuration file containing the following xml:

1:  <wsdlParameters xmlns='http://microsoft.com/webReference/'>

2:    <language>c#</language>

3:    <protocol>Soap</protocol>

4:    <nologo>true</nologo>

5:    <sharetypes>false</sharetypes>

6:    <webReferenceOptions>

7:      <codeGenerationOptions>properties oldAsync</codeGenerationOptions>

8:    </webReferenceOptions>

9:  </wsdlParameters>

Save this file as c:\oldwsdlconfig.xml (change the language element to VB if you are creating a VB.NET project: vb). Now, call wsdl.exe to generate the proxy class:

This will generate a CrmService.cs  file. We will now add this file to our SQL Server project in Visual Studio by right clicking our project, selecting Add -> Existing items, and then browse to the location where the CrmService.cs  file was created. We will see an error if we build our code this way: the generated proxy class uses the webservice dll, which is not yet referenced, so right click the project in the Solution Explorer, choose add reference, and select System.Web.Services from the list.

Next, we have to avoid that our code will do runtime assembly creation for the serialization and deserialization of the objects which travel across this webservice. In order to do that, we right click in our Solution Explorer on the project and select Properties.

Select the Build tab, and at the bottom select for Generate serialization assembly the value On

While we are in the project property window, we can set the permission level property as well: select the database tab, and set the value for permission level to external. In this way, we mark this assembly as an assembly which is ‘safe’ in the sence that it will not do dangerous thread manipulations, platform invokes, …, and hence it is not marked as unsafe. But it is less safe than an assembly marked ‘safe’, because it makes calls over the network, or reads/writes to the hard disk.

1:  CrmService services = new CrmService();

2:  services = ConnectCrmServiceWithoutImpersonation(services, "username", "password", "domain", "http://servername/mscrmservices/2007/CrmService.asmx", "organization");      

3:  account agent = new account();

4:  agent.name = "Test 123";

5:  agent.new_agentname = "Agent Name";

6:   

7:  services.Create(agent);

1:      public static CrmService ConnectCrmServiceWithoutImpersonation(CrmService oService, String NETWORKUSERNAME, String NETWORKPASSWORD, String NETWORKDOMAIN, String crmServiceUrl, String tokenOrganizationName)

2:      {

3:          try

4:          {

5:   

6:              oService.Credentials = new System.Net.NetworkCredential(NETWORKUSERNAME, NETWORKPASSWORD, NETWORKDOMAIN);

7:              oService.Url = crmServiceUrl;

8:              CrmAuthenticationToken token = new CrmAuthenticationToken();

9:              token.OrganizationName = tokenOrganizationName;

10:              oService.CrmAuthenticationTokenValue = token;

11:              oService.PreAuthenticate = true;

12:   

13:   

14:          }

15:          catch (Exception ex)

16:          {

17:              //TODO: Enterprise Library

18:              throw ex;

19:          }

20:          return oService;

21:      }

Next, we can build and deploy this project.

Building the assembly

Before we build our assembly there are a couple of project settings that need modification.
Here we ask Visual Studio to also generate an assembly containing the XML Serializers.  This is needed because our code is calling a web service and code running in SQL Server is not allowed to use the serializers that are normally generated dynamically.

Another setting that we need to change is the Permission Level.  This is also required because our code is calling a web service, hence external.

Server and Database Settings

Now that we’ve covered the actual development of the stored procedure we would like to install it on our database server.  This requires us to modify some settings on the server and database.  If you are not the DBA of the server and you are in the luxurious situation that such a person exists, please verify with him/her if you are allowed to modify these settings.

Enabling CLR Integration

By default SQL Server does not allow CLR Integration.  This setting can be easily modified with the following script:

exec sp_configure 'clr enabled', '1';

reconfigure;

Our database we trust

As our stored procedure needs external access permission, we need to create the assembly with external access (as shown in next chapter).  To get this to work we need to convince SQL Server that our database can be trusted: (I’ve used AdventureWorks in this article but this can be any existing database)

ALTER DATABASE AdventureWorks

SET TRUSTWORTHY ON;

Installing the Stored Procedure

We’re almost there, all that remains to be done is to tell SQL Server where our stored procedure can be found and that it actually exists.  Assemblies, just like stored procedures, live in a database (AdventureWorks in our case).  In Management Studio you can see your assemblies in the Object explorer under the Programmability node of your database:

\

Adding the assemblies

Following script adds the two assemblies that we’ve created earlier to your database.  An assembly is given a name, like MyStoredProcedures.  Here you can finally see the EXTERNAL_ACCESS permission that we need for our web service call.

CREATE ASSEMBLY MyStoredProcedures

FROM 'D:\MyStoredProcedures.dll'

WITH PERMISSION_SET = EXTERNAL_ACCESS;

GO

CREATE ASSEMBLY MyXmlSerializers

FROM 'D:\MyStoredProcedures.XmlSerializers.dll'

WITH PERMISSION_SET = EXTERNAL_ACCESS;

GO

Adding our stored procedure

Last step is to create our stored procedure.  We tell SQL Server that it can be found in the MyStoredProcedures assembly, in the class StoredProcedures and that the method is called WeatherSP.  We have one parameter of type string in our method definition, which translates to nvarchar in the stored procedure.  A length of 10 is more than enough for our ZIP code.

CREATE PROCEDURE WeatherSP

AS EXTERNAL NAME MyStoredProcedures.StoredProcedures.WeatherSP;

GO

Execute the stored procedure to run the web service

EXEC [dbo].WeatherSP

Drawbacks

Some drawbacks of calling CRM Web Service from SQL Server:

1. To get updated CrmService web service in code file .cs/.vb, we must recompile/regenerate the CrmService web service .asmx whenever changes done at CRM server, such as adding entities/fields.
2. Increase of resources usage(calling stored procedure -> crm service). It may slow down the system performance.