Creating socket inside a SQL-CLR trigger or stored

Question

I have a SQL-CLR trigger in SQL Server 2008 that sends out message to socket server. It works fine but I need the TCP client to stay connected to the server it communicates with.

In order to do this I have to make some thread that stays alive. So I created a thread added the message to queue, I see client connecting but SQL Server kicks out before the client is able to process the queued message and send it to tcp server.

The purpose of this application is to send what fields and some values associated to them that were updated via xml. I wanted to put them in queue that gets handled in thread and send to TCP Socket Server.

What I am planning as alternative now is have CLR Trigger add messages to MSMQ and have another service to process these messages.

Any good suggestions how to accomplish this?

Answer 1

CREATE ASSEMBLY Messaging AUTHORIZATION dbo FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll' WITH PERMISSION_SET = UNSAFE GO