Anyway, I got an error message "System.InvalidOperationException: The context connection is already in use." when calling the stored procedure from a trigger. In the trigger, I defined a new connection, did some stuff and called the stored procedure:
using (SqlConnection cn = new SqlConnection("context connection=true"))
int stuffID = GetStuffIdFromDatabase(cn);
and in the stored procedure, I ran an update statement:
SqlContext.Pipe.ExecuteAndSend(new SqlCommand("UPDATE Stuff SET [Description] = 'Updated from trigger' WHERE ID = " + stuffID));
I googled around for a while without much luck, but eventually realised that the
SqlContext.Pipe.ExecuteAndSendmust be creating its own connection. I moved the stored procedure call out of the using block, and it all worked fine.
n.b. I posted this for the benefit of other people who will, in future, google for the error message. If you are one of these people, I hope it helps you, because my regular readers have undoubtedly moved on by now...