It is possible to create a managed .net stored procedure for SQL Server that acts like a WCF client. SQL Server 2005 at least is required. But I won't talk about how to. I'd rather discuss an odd error you can encounter while performing this stunt.
On development machines one can encounter this odd error when WCF client is being initialized, after managed stored procedure is being invoked through T-SQL.
Msg 6522, Level 16, State 1, Procedure CalcAdd, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "CalcAdd":
System.Configuration.ConfigurationErrorsException: The type 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior, Microsoft.VisualStudio.Diagnostics.ServiceModelSink, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' registered for extension 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior' could not be loaded. (C:\Windows\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 192)
System.Configuration.ConfigurationErrorsException:
at System.Configuration.BaseConfigurationRecord.EvaluateOne(String[] keys, SectionInput input, Boolean isTrusted, FactoryRecord factoryRecord, SectionRecord sectionRecord, Object parentResult)
at System.Configuration.BaseConfigurationRecord.Evaluate(FactoryRecord factoryRecord, SectionRecord sectionRecord, Object parentResult, Boolean getLkg, Boolean getRuntimeObject, Object& result, Object& resultRuntimeObject)
at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
at System.Configuration.BaseConfigurationRecord.GetSection(String configKey, Boolean getLkg, Boolean checkPermission)
at System.C...
Note that everything is configured properly and WCF client inside stored procedure should initialize just fine. The problem is somehow connected to machine.config file or better, to some initialization of types defined there.
Workaround: (Temporary) disable WCF debugging by invoking this command line utility:
Now the WCF client inside SQL Server runs just fine. After you've done you can re-enable WCF debugging services by calling same exe with different parameter:
Note that administrator privileges are required to disable/enable it.