Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MarshMallowEgg
Starting Member
2 Posts |
 Posted - 2008-12-30 : 12:29:11
|
| Hi,I just started experimenting with using SQL, CLR alongside a WCF service. However I'm running into an issue where it seems like the db and the client service connection is timing out. I got my code off of an example on CodeProject. I got it working, I deploy the CLR to the DB (works fine). I start up the service on the server (still good). I do an update/insert and my WCF service reads the transaction and logs it accordingly in it's logs (the intended effect). BTW I should mention that for this little experiment the DB and service host are my desktop computer so it's localhost for both.Now the issue is that if I leave it and I don't perform any transaction for about an hour or so, it won't read the transaction later. It doesn't pop up an SQL error or anything. It's as if the DB thinks the connection is functioning but the service doesn't receive anything. At this point, if I restart the service it still doesn't function. But if I redeploy the CLR it starts working again. Is there a setting I need to set with in the DB? BTW I'm running VS2005, XP, SQL2005, .Net 3.5 for this project. The WCF service is hosted as a windows service.Any suggestions are appreciated!ThanksBelow is the code for the CLR trigger:using System;using System.Data;using System.Data.SqlClient;using Microsoft.SqlServer.Server;using System.ServiceModel.Description;using System.ServiceModel;using System.Collections;using System.Diagnostics;using System.Threading;public partial class Triggers{ //Create an endpoint addresss for our serivce public static EndpointAddress endpoint = new EndpointAddress(new Uri("http://localhost:8000/services/ServiceContract")); //Create a binding method for our service public static WSHttpBinding httpBinding = new WSHttpBinding(); //Create an instance of the service proxy public static CLRTest.localhost.ServiceContractClient myClient = new CLRTest.localhost.ServiceContractClient(httpBinding, endpoint); //A delegate that is used to asynchrounously talk //to the service when using the FAST METHOD public delegate void MyDelegate(String crudType); public Triggers() { httpBinding.CloseTimeout = TimeSpan.Parse("02:00:00"); httpBinding.OpenTimeout = TimeSpan.Parse("02:00:00"); httpBinding.ReceiveTimeout = TimeSpan.Parse("02:00:00"); httpBinding.SendTimeout = TimeSpan.Parse("02:00:00"); } [SqlProcedure()] public static void SendData(String crudType) { /*A very simple procedure that accepts a string parameter based on the CRUD action performed by the trigger. It switches based on this parameter and calls the appropriate method on the service proxy*/ switch (crudType) { case "Update": myClient.UpdateOccurred(); break; case "Insert": myClient.InsertOccurred(); break; } } [Microsoft.SqlServer.Server.SqlTrigger(Name = "WCFTrigger", Target = "Names", Event = "FOR UPDATE, INSERT")] public static void Trigger1() { /*This is a very basic trigger that performs two very simple actions: * 1) Gets the current trigger Context * and then switches based on the triggeraction * 2) Makes a call to a stored procedure * Two methods of calling the stored procedure are presented here. * View the article on Code Project for a discussion on these methods */ SqlTriggerContext myContext = SqlContext.TriggerContext; //Used for the FAST METHOD MyDelegate d; switch (myContext.TriggerAction) { case TriggerAction.Update: //Fast method - STRONGLY RECOMMENDED FOR PRODUCTION! d = new MyDelegate(SendData); d.BeginInvoke("Update", null, null); break; case TriggerAction.Insert: //Fast method - STRONGLY RECOMMENDED FOR PRODUCTION! d = new MyDelegate(SendData); d.BeginInvoke("Insert", null, null); break; } }} |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
MarshMallowEgg
Starting Member
2 Posts |
Posted - 2008-12-30 : 14:01:15
|
| It is possible that there is a setting in the C# code that I haven't set. I am using all the objects with their default settings. I'll look around some more. Thanks for you suggestion though. |
 |
|
|
|
|
|
|
|
CLR SQL connection timeout?