Symptoms

Unable to refresh SQL Service Instance - refresh never completes (even if waiting for hours). Execute methods work but refresh is not possible. SQL SI contains stored procedure(s).
 

Diagnoses

Whenever SQL broker is created/refreshed for a SQL server service instance and a stored procedure is involved K2 executes that stored procedure with SET FMTONLY = ON.

See K2 documentation on this: http://help.k2.com/kb001451

The SET FMTONLY ON command may cause the stored procedure to unexpectedly return a large dataset, causing the Smart Object Tester to appear to freeze, or not run properly at all, returning only the Execute method in K2. In such case you may observe these symptoms:

• • Refreshing the SQL Service Instance for a database does not finish 

• • Other fixes fail to present the List method in K2
     

Resolution

Additional control logic needs to be added to the stored procedure to allow return of a list of columns to K2. See example below.

DECLARE

@fmtonlyON BIT = 0

--When SET FMTONLY is set to ON it ignores conditional statements

--Using this "trick", we can detect when SET FMTONLY is set to ON

IF (1=0) BEGIN SET @fmtonlyON = 1 END

--If SET FMTONLY is set to ON, set it to OFF to allow CREATE TABLE

IF @fmtonlyON = 1 BEGIN SET FMTONLY OFF END

CREATE TABLE #TempTable

( Id INT

)

--SET FMTONLY to ON to not return a dataset

IF @fmtonlyON = 1 SET FMTONLY ON;

SELECT FIELD1

FROM TABLE1

--SET FMTONLY to OFF to not skip the SET command and conditional statement

IF @fmtonlyON = 1 SET FMTONLY OFF;

SET @variable= 1

IF @variable = 1

BEGIN

RAISERROR(99999, 16, 1, 'Fund is NULL', @ObjectName)

SELECT @Error = @@ERROR

GOTO TheEnd

END

--SET FMTONLY back to ON at the end

IF @fmtonlyON = 1 SET FMTONLY ON;