Symptoms

SmartObjects - Services not picking up resultset into SQL Server Service's Properties
 

Diagnoses

The information referenced regarding stored procedures in the attached KB and associating PDF below:
http://help.k2.com/kb001451

Of which some options are available:

a. Use SET FMTONLY ON if applicable

b. When using 'SET FMTONLY ON' and temp tables some issues can surface (please see the PDF provided by the KB) of which there are some tips and tricks to achieve the required behavior
 

Resolution

Instead of using a temp table, table variables were used instead to achieve the necessary requirement. As per the blog below:

http://odetocode.com/articles/365.aspx

"In fact, a table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement. The variable will no longer exist after the procedure exits - there will be no table to clean up with a DROP statement."