Calling web service from sql server using sql clr

Problem :

In my previous project I was been asked call web service from sql server stored procedure.

So I get it done using SQL CLR. So using CLR we can run managed code inside sql server.

Code that runs within the CLR is referred to as managed code.

We can create stored procedures, triggers, user defined types, and user-defined aggregates in managed code.

We can achieve significant performance increases because managed code compiles to native code prior to execution.

We can use SQL CLR in in SQL Server 2005 and later.

Why Sql CLR in sql server:

In some cases some tasks are not possible by T-SQL as my requirement.

So in that point we can go with SQL CLR.

What tools I have used in this post

Visual Studio 2015

Sql Server 2014

In Action:

  1. Create SQL Server Database project in VS 2015


  1. Add SQL CLR C# Stored Procedure


Name stored procedure As CallWebService

3. Add C# codes to Call Web service. I am using below web service to do test

HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://www.webserviceX.NET//globalweather.asmx//GetCitiesByCountry?CountryName=Sri Lanka");

request.Method = "GET";

request.ContentLength = 0;

request.Credentials = CredentialCache.DefaultCredentials;

HttpWebResponse response = (HttpWebResponse)request.GetResponse();

Stream receiveStream = response.GetResponseStream();

// Pipes the stream to a higher level stream reader with the required encoding format.

StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8);

System.IO.File.WriteAllText("d://response.txt", readStream.ReadToEnd());



I am simply writing response to a text file.  You can do whatever in here.

4.Enable clr and set trust worthy on database. I am using AdventureWorks database

sp_configure 'show advanced options', 1;




sp_configure 'clr enabled', 1;




alter database [AdventureDatabase] set trustworthy on;

5. Build Visual Studio Project

Then it will generate dll in bin folder

6. Register assembly in Database.

Go to AdventureWorks > Programmability > Assemblies

Right click on Assemblies and Click new Assembly


Set Permission to External access and browse for Our dll.  It is in bin folder of your project

Once you added we can see assembly registered in side Assemblies as below.


7. Create stored procedures to call assembly’s stored procedure


Once you created a stored procedure you can see locked stored procedure.


8. So Now we have finished just execute the stored procedure. You can see text file is generated in drive


Source code

2 thoughts on “Calling web service from sql server using sql clr

  1. The code in 3. gave a bunch of errors as soon as I copied and saved in VS 2015. The prevailing error was about namespace, so I entered a namespace ‘SQLCLRTEST’. It did not help – the next error was “Error CS0000 A namespace cannot directly contain members such as fields or method”. This log is not helpful at all. I am not a .NET developer to crack errors like that, I just need to be able to interface to a web service frp, SQL, which this log did not help to accomplish.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s