SQL2005 + Service Broker + CLR Stored Procedure + XML + Temp Tables

This is killing me...and not slowly.

I have a stored procedure (a) which calls another stored procedure (b).

Stored procedure (b) is a C# stored procedure which simply writes out to a file data in XML format. Internally, it calls...select fld1, fld2, fld3, fld4, fld5from #tmptable for xml auto, elements

If I call stored procedure (a) from Query Analyser / SQL Management Studio everything works fine. Perfect.

But....we need this all to run asynchronously. So we used the Service Broker, configured the queues and messages and off we went. All worked as planned except our XML files were empty.

Further investigation showed that if we call select fld1, fld2, fld3, fld4, fld5from #tmptable

- without the 'xml' bits, we got a resultset back. But if we call it with the for xml auto, elements, the reader was empty. No errors are visible in the profiler, but the XmlReader refuses to read.

The binary / extended stored procedure is the same pysical binary that is called from Query analyser that works, but via the Service Broker refuses to do anything XML based. Outputting the data as normal text is cool, but not what we want.



----------------- UPDATE --------------
I changed the code so the CLR Stored proc was fired as a trigger on an update to a table. If I update the table in Query analyser, the trigger fires, the CLR Stored proc is called, the XML is generated.

If I update the table as part of my message handling in the Service Broker queue, the trigger is fired, the CLR Stored proc is called, the XML is generated EMPTY!!! The other TSQL statements work fine, but selecting for xml simply will not work for a procedure called, either implicitly or explicitly from the service broker.