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 out 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 profiles, 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. :doh: Any ideas?so you answer don't be scared of failure The only failure is never to try Things You've Never Done - Passenger -2008