2

I have a simple SOAP service which takes one parameter(long) and returns a string. I have used SOAP UI and I am able to access the service fine and get a successful response.

What is that iam trying to achieve

SQL store procedure which will fetch the ID from the database (parameter for the service). Make a call to C# assembly (SQL CLR) C# assembly returns the result string back to SQL.

I have tried the following

Aproach 1

Create a c# class library with service reference to my service. The method is exposed correctly e.g., GetConfigPath(long id) However when I try to install the library into SQL I get a error. The assembly looks for System.ServiceModel and it is not found in SQL blah.

Approach 2

I used the following link and followed step by step (obviously changed the parameters to suit my requirement etc)

[https://code.msdn.microsoft.com/Calling-WCF-Service-from-a-8071ceaa#content][1]

When i was adding webreference as shown in the link above, I noticed that my method had an additional parameter e.g., GetConfigPath(long id, bool idpassedin)

I thought it might be default so I continued and installed the stored procedure into the database exactly as show in the link above. The installation script works fine. However when I run the stored procedure I get the following error.

The request failed with HTTP status 400: Bad Request.

I am using the following tools VS2015 SQL 2014

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
Abe
  • 1,879
  • 2
  • 24
  • 39
  • Consuming an HTTP service from inside SQL server is going to cause you problems in the long run. Unless this application is throw-away, I would seriously consider dropping this approach. – tom redfern Jan 31 '17 at 13:21
  • @Tom Redfern- Thanks for your response. I am not trying to consume the service from SQL sql is only a means to get the ID and CLR (c#) is trying to consume the service and return back a string to SQL. Please feel free to ask more questions. – Abe Jan 31 '17 at 13:56
  • I meant the CLR. This was not designed as a full-fledged runtime, and as such should not be used as an integration point. It's basically an alternative way to write complex stored procedure and function code. – tom redfern Jan 31 '17 at 15:12

1 Answers1

0

Generally speaking, yes, it is possible to communicate with a Web Service via SQLCLR. You just might need to accept not having access to some of the frameworks provided in .NET that make such communication somewhat "transparent".

Regarding "Approach 1", as of SQL Server 2012 you can no longer reference (or load into SQL Server) the System.ServiceModel library, due to it being changed into a mixed-mode assembly. Only pure-MSIL assemblies are allowed to be loaded into SQL Server.

Please see my answer (here on S.O.) for details:
SQL Server 2016: CREATE ASSEMBLY for assembly 'System.ServiceModel' failed

Regarding "Approach 2", if you want to see why you might be getting a "bad request", then you should download and use Fiddler. Read the directions as you may need to adjust your code to allow for going through a proxy (the proxy being Fiddler).

If you can't find a way to get that working, I always suggest handling it manually via a regular HttpWebRequest. You said that you are just sending in a single ID and getting back a sting, so it shouldn't be much work to put together the basic SOAP request (you can even capture a working request via Fiddler) and then just parse the XML response (via HttpWebResponse ) for the returned string.

Please see the various notes in the following S.O. answers of mine regarding Web Service calls via SQLCLR:

Community
  • 1
  • 1
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171