Two things that you might encounter while you are attempting to create your schema for your SQL Adapter using the "SQL Transport Schema Generation Wizard":
CREATE PROCEDURE SP_GetNewStudentInfoASDECLARE @Process_Date DateTimeSET @Process_Date=GetDate()Update StudentInfo Set ProcessedDate=@Process_Date Where ProcessedDate is NULLSELECT StudentID, Lastname, Firstname, Term, GPA, DateofAdmission FROM StudentInfo WHERE ProcessedDate=@Process_Date FOR XML AUTO, ELEMENTSGO
NOTE: If you add "ELEMENTS" to the Stored Proc as I did here, it will create the schema representing each column as a "Record" node... if you remove "ELEMENTS" it will create the schema representing each column as an "Attribute" node (which may be best if you plan on promoting any of those columns for routing purposes or to inspect their values within an Orchestration).
Hope this helps!
One other thing to check if you are getting the "Failed to execute SQL statement" message is the schema. If executing a stored procedure that does not belong to the login's default schema it produces this message.
By copying the stored proc to the default schema (typically dbo) and running the wizard, the adapter generation will succeed. You can then edit the SQLService.xsd file to insert the true schema name before the stored procedure name.