Service Broker activation procedure in C# with external dependencies I am not entirely sure if I have come to the right place, because this question involves both SQL Server and .Net. Apologies if this is not an appropriate place for my question!

I am currently working on an in-house tool to transfer data from our
ERP system to our SharePoint. Specifically, my boss wants the closing
of a project in the ERP system to trigger a workflow in SharePoint.

My current line of thinking is to use the SQL Server Service Broker -
create a T-SQL trigger on the projects table in the ERP database to
send a message whenever a project is closed. The message would like
carry an XML payload with some information on the project.
I have done something very similar before, so that should not be too
much of a problem.

In the next step I would have to receive these messages, extract the
information from the XML payload and create entries in a list in our
SharePoint server.
Adding items to a SharePoint list from a C# program is also something
I have done before, so that should not be a problem either.

My question is how I should approach the transfer from the Service
Broker Message Queue to the SharePoint list. 
My first idea was to write a stored procedure in C# to receive
messages from the queue, extract the relevant information from the XML
payload, and then create a list item SharePoint. (Stored procedures in C# is not something I have done before!)

As far as I could find out, this should not be a problem per
se. However, it would require access to the SharePoint client
assemblies from the stored procedure.
Also, I would like to do some logging, and being a lazy slob, I would
like to use Log4Net for that. So I would need access to two
assemblies. (More actually, I think the SharePoint client library is
split up into several assemblies.)

Can I just load these assemblies into the SQL Server along with the
one for my stored procedure, and then reference them from within the
SQL Server? If so, are there any pitfalls, things I need to keep in
mind?
Or can I just install the required assemblies on the database server
and reference them from within my stored procedure?
Another option just comes to my mind - I could use Fody Costura, I
think, to massage the additional assemblies into the one carrying my
stored procedure... Are there any significant downsides to this?

The alternative, of course, would be a program running externally from
the SQL Server. This seems in some ways to be a better alternative -
the problem with the SharePoint and Log4Net assemblies
disappears. Also, I could deploy the resulting program on an arbitrary
machine.

In that case, though, I am not sure how to interact with the Service
Broker and the Message queue. Can I just issue a RECEIVE statement via
an SqlCommand instance and extract the fields like from a regular
SELECT statement? 
Would I have to finish the conversation? I have found some hints to a
Service Broker API, but where do I get the assemblies?

So, my questions, in short, are:
- Can I reference other assemblies from a CLR stored procedure?
- I so, how? What are the pros and cons?
- If now, how do I speak to the Service Broker from an external
  program? Do I need other assemblies besides the SQL Server Client?

Thank you very much for any insight you might be able to share with
me,
Benjamin
I suggest external activation rather than SQLCLR. That will provide capabilities like log4net that aren't easily doable in SQLCLR.

The trigger would begin a conversation and sends the project closure message.  The C# app would RECEIVE the closure message from the queue, do the SharePoint stuff, and end the conversation.  You'll also need to close the initiator side of the conversation to avoid endpoint leakage. Below is a single-queue example with the same SB service acting in both initiator and target roles.  Note that your C# will receive EndDialog messages for both sides of the conversation with this method.

CREATE QUEUE dbo.ProjectClosureQueue;
CREATE MESSAGE TYPE ProjectClosureRequest
    VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT ProjectClosureContract (
      ProjectClosureRequest SENT BY INITIATOR
    );
CREATE SERVICE ProjectClosureService
    ON QUEUE dbo.ProjectClosureQueue ([ProjectClosureContract]);
GO


Trigger code snippet:

DECLARE @ProjectClosureConversationHandle  uniqueidentifier;
BEGIN DIALOG CONVERSATION @ProjectClosureConversationHandle
    FROM SERVICE ProjectClosureService
    TO SERVICE 'ProjectClosureService', 'CURRENT DATABASE'
    ON CONTRACT ProjectClosureContract
    WITH ENCRYPTION = OFF;
SELECT @ProjectClosureConversationHandle;

SEND ON CONVERSATION @ProjectClosureConversationHandle
    MESSAGE TYPE ProjectClosureRequest (@ProjectClosureXmlMessage);


Get next message in C# app:

WAITFOR (
    RECEIVE TOP (1)
           conversation_handle
           CAST(message_body AS xml)
        , message_type_name
    FROM dbo.ProjectClosureQueue
    ), TIMEOUT 10000;


If no message received, repeat receive.

If message_type is ProjectCloseRequest, do SharePoint stuff

If message_type is not ProjectCloseRequest or '"http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog', log error

Finally, END CONVERSATION