Symptoms

When creating a smartobject for a stored procedure, tables are displayed, but stored procedures do not appear in the service tester.
 

Diagnoses

As the particular stored procedure in question creates a temp table, when the service instance attempts to discover the schema of the table in order to allow for smartobject creation, it uses the FMTONLY ON option to do so. The FMTONLY ON option disables any conditional logic, set variable statements, or statements that modify data, in this case, the creation of the temp table is blocked by this option, and thus, the stored procedure does not return anything that K2 can understand.

 

Resolution

The following was added to the stored procedure to work around the FMTONLY option
CREATE PROCEDURE.... AS

DECLARE @fmtonlyON BIT
Set @fmtonlyON = 0
IF (1=0) SET @fmtonlyON = 1
SET FMTONLY OFF

..... (body of stored proc)...

IF @fmtonlyOn = 1

SET FMTONLY ON

END

This causes execution when registering the service instance to follow this path:

SET FMTONLY ON

exec StoredProcedure()

(inside stored proc)
SET FMTONLY OFF
(body of stored proc)
SET FMTONLY ON

(stored proc completes)
SET FMTONLY OFF

Further details on this behavior can be found in http://help.k2.com/kb001451. This KB article does not describe this particular workaround, but rather offers a few other options on resolving this issue.