Call a Winservice hosted WCF service from a SQL stores proc
-
I have developed a WCF service which is hosted via a simple windows service (can't use IIS due to reasons to complex and boring to go into). The service works well when called from the test client. However I need to be able to call a the service from either a clr trigger or stored proc (using SQL Server 2005). The service uses the wsHttpBinding and has the following syntax
[OperationContract]
[FaultContract(typeof(IsaFault), ProtectionLevel = System.Net.Security.ProtectionLevel.None)]
void ExportData(string RouteId, string filePath);I don't have much experience with developing clr stored procs and am just looking to get a basic example of how to call the service. I've tried adding a SQL database project to my solution but it will not let me add a reference to my service proxies etc. Kind regards Ken
-
I have developed a WCF service which is hosted via a simple windows service (can't use IIS due to reasons to complex and boring to go into). The service works well when called from the test client. However I need to be able to call a the service from either a clr trigger or stored proc (using SQL Server 2005). The service uses the wsHttpBinding and has the following syntax
[OperationContract]
[FaultContract(typeof(IsaFault), ProtectionLevel = System.Net.Security.ProtectionLevel.None)]
void ExportData(string RouteId, string filePath);I don't have much experience with developing clr stored procs and am just looking to get a basic example of how to call the service. I've tried adding a SQL database project to my solution but it will not let me add a reference to my service proxies etc. Kind regards Ken
I don't know anything specific about calling a service from a "stored proc" but it seems to me you should be able to call your service operations from anywhere you have the .NET framework available. It looks like you have the server side working and now you need to do the client side: Building Clients (WCF)[^] Mark
Mark Salsbery Microsoft MVP - Visual C++ :java:
-
I have developed a WCF service which is hosted via a simple windows service (can't use IIS due to reasons to complex and boring to go into). The service works well when called from the test client. However I need to be able to call a the service from either a clr trigger or stored proc (using SQL Server 2005). The service uses the wsHttpBinding and has the following syntax
[OperationContract]
[FaultContract(typeof(IsaFault), ProtectionLevel = System.Net.Security.ProtectionLevel.None)]
void ExportData(string RouteId, string filePath);I don't have much experience with developing clr stored procs and am just looking to get a basic example of how to call the service. I've tried adding a SQL database project to my solution but it will not let me add a reference to my service proxies etc. Kind regards Ken
Is it possible to use SSIS instead of a stored procedure? There is a WebService Task option in SSIS. Also some limitations of what you can do in a CLR SP (can't remember offhand). You're probably better asking further questions in the database forum.
-
I have developed a WCF service which is hosted via a simple windows service (can't use IIS due to reasons to complex and boring to go into). The service works well when called from the test client. However I need to be able to call a the service from either a clr trigger or stored proc (using SQL Server 2005). The service uses the wsHttpBinding and has the following syntax
[OperationContract]
[FaultContract(typeof(IsaFault), ProtectionLevel = System.Net.Security.ProtectionLevel.None)]
void ExportData(string RouteId, string filePath);I don't have much experience with developing clr stored procs and am just looking to get a basic example of how to call the service. I've tried adding a SQL database project to my solution but it will not let me add a reference to my service proxies etc. Kind regards Ken
This has now been sorted in so much that I can get the clr stored proc to call the wcf service. The only issue I now have is that for some reason it does not want to see the config file as I'm getting the error message:
Msg 6522, Level 16, State 1, Procedure spTrackNTraceOutBound, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "spTrackNTraceOutBound":
System.InvalidOperationException: Could not find default endpoint element that references contract 'Delarue.Isa.ServiceContracts.ITrackNTrace' 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:
at System.ServiceModel.Description.ConfigLoader.LoadChannelBehaviors(ServiceEndpoint serviceEndpoint, String configurationName)
at System.ServiceModel.ChannelFactory.ApplyConfiguration(String configurationName)
at System.ServiceModel.ChannelFactory.InitializeEndpoint(String configurationName, EndpointAddress address)
at System.ServiceModel.ChannelFactory`1..ctor(String endpointConfigurationName, EndpointAddress remoteAddress)
at System.ServiceModel.EndpointTrait`1.CreateSimplexFactory()
at System.ServiceModel.EndpointTrait`1.CreateChannelFactory()
at System.ServiceModel.ClientBase`1.CreateChannelFactoryRef(EndpointTrait`1 endpointTrait)
at System.ServiceModel.ClientBase`1.InitializeChannelFactoryRef()
at System.ServiceModel.ClientBase`1..ctor()
at Delarue.Isa.ServiceContracts.TrackNTraceProxy..ctor()
at Delarue.Isa.Service.SqlClient.StoredProcedures.spTrackNTraceOutBound(String routeId, String filePath). Evan though the end point is in the config file and I have placed a copy of this file in the root of the SQL server instance binn folder. Any suggestions?