I learned a few important things about calling a stored procedure from an orchestration today.
1. Use “add generated items” instead of trying to create the schema and ports yourself
2. Make sure your SQL in the procedure ends with “for xml auto, xmldata” BEFORE you add generated items. Then remove the “, xmldata” part when you have your orchestration running.
3. Make sure in the adapter properties of your send port that the “document target namespace“ and “response root element name“ match your SQLservice schema.
I'll probably write a lot more “what I learned today” posts, just as a way for me to not forget all of the important things I need to remember when working with Biztalk. Maybe this will be useful to someone else as well.