Here are some ideas to focus on:
1) Does your SQL Stored Proc work in Query Analyzer? Remember that if you follow my prior instructions, every time you run it, it will update the database, so there will no output when you run it again – unless some other job has inserted new data back into the database.
2) For a quick test, you can just create a flat file send port and subscribe it to the SQL receive port. Then you can test without an orchestration. The receive port should run every x minutes, and the send port should be able to grab the output (in addition to the orchestration).
3) Check the event log (and HAT) for errors but with SQL, often the messages in the System Event Log are more specific and helpful.
Neal Walters
http://Biztalk-Training.com – Learn Biztalk Faster
http://Sharepoint-Training.com – End User Videos