0

We develop SQL CLR stored procedure which calls methods of a web-service. We use VS2010 to develop and debug this project. The problem is the following exception when we try to start test script which calls our stored procedure:

A .NET Framework error occurred during execution of user-defined routine or aggregate
"WebServiceExample": 
System.InvalidOperationException: Could not find default endpoint element that
references  contract 'ServiceReference1.ExRatesSoap' in the ServiceModel client
configuration section.  This might be because no configuration file was found for your
application, or because no  endpoint element matching this contract could be found in
the client element.
System.InvalidOperationException:
...

There are some recomendations regarding this problem in the Net which use wsdl.exe and sgen.exe utilities along with another manipulations. But we are interested in solution which utilizes only VS2010 tools, settings, options, etc. It's seemed unbeleivable that VS2010 can't cook this pie from beginning to end, we think we are doing something wrong.

Serg
  • 2,346
  • 3
  • 29
  • 38
  • HTTP calls from SQLCLR is a very very very bad idea. Use and external process. – Remus Rusanu Oct 16 '12 at 11:58
  • @RemusRusanu: And can you explain why it's bad idea? I think it's good idea namely because I need not any external process. – Serg Oct 16 '12 at 12:49
  • 1
    Because you'll exhaust precious SQL server resources (eg. [workers](http://msdn.microsoft.com/en-us/library/ms187024(v=sql.105).aspx)) blocked in waiting for HTTP responses. Because it does not allow to push blocking to periphery (scale-out the HTTP calls to non critical machines, not the DB server). Because I've seen it done many times, and every times it failed miserably, even under mild load, once deployed against the real world WWW/HTTP, with its irregular and whimsical response times. – Remus Rusanu Oct 16 '12 at 12:58
  • @RemusRusanu: Thank you very much for your explanation and extremly valuable information! I think that maybe it results in rejecting the idea of using SQLCLR for this purpose. Although it would be useful for me to see confirmation of your advise. Maybe, in my case, it work as quick-and-dirty solution at the beginning. External process also requires development, deployment and some resources to work. Also, using SQLCLR with Service Broker seems to be good alternative to external process. – Serg Oct 16 '12 at 13:16
  • Our decision is to follow Remus's advise and shift to solution using external process, namely SQL Service Broker with external activation. Consuming web-service from SQLCLR seems really not good idea for production at least because it needs very many .NET assemblys which warned about not to be tested with SQL itself. This is too risky. – Serg Oct 17 '12 at 07:14

1 Answers1

0

You may have to manually create the reference to the web service in a config file, e.g. sqlserver.exe.config where it is invoked from SQL Server. Those aren't created automatically for SQL CLR since it is executed by SQL Server.

Turnkey
  • 9,266
  • 3
  • 27
  • 36
  • Thank you for your answer. Definitely we can go further after it but stopped with this error: A .NET Framework error occurred during execution of user-defined routine or aggregate "WebServiceExample": System.Configuration.ConfigurationErrorsException: An error occurred creating the configuration section handler for system.serviceModel/bindings: Request failed. (C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe.Config line 4) ---> System.Security.SecurityException: Request failed. – Serg Oct 16 '12 at 14:55
  • 1
    I think it is missing something else in the config file. It seems there are a lot of holes in trying to run those sort of things from SQL Server CLR. It may be better to move that into an app service as Remus suggested as these things could also be problematic and hard to troubleshoot on the production server. – Turnkey Oct 17 '12 at 03:42
  • Yes, thank you, I think you and Remus are right and we made final decision to move to solution you both talking about. Also, I think you are right about something missed in config. The problem remains, so formally I can't mark your answer but I really appreciate you assistance and advise. Thank a lot. – Serg Oct 17 '12 at 07:08