SQL stored procedure based SmartObject suddenly stops working with an error: "ServiceObject definition does not contain method"

  • 16 February 2021
  • 0 replies
  • 74 views

Userlevel 5
Badge +20
 

SQL stored procedure based SmartObject suddenly stops working with an error: "ServiceObject definition does not contain method"

kbt167049

PRODUCT
K2 Five
K2 blackpearl
BASED ON
K2 Five (all)
SEE ALSO
 
TAGS
Microsoft SQL
This article was created in response to a support issue logged with K2. The content may include typographical errors and may be revised at any time without notice. This article is not considered official documentation for K2 software and is provided "as is" with no warranties.

Issue

The SmartObject based on a SQL Server stored procedure suddenly stops working with an error:
 

ServiceObject definition does not contain method. ServiceObject: [%SQL_Stored_Procedure_Based_SmO_Name%]. Method: [List]

Symptoms

You have a SmartObject based on a SQL Server stored procedure which suddenly stops working with the following symptoms:

On View Level you getting the following error:
 

The SmartObject Unknown or one of its components could not be resolved. Error Details: ServiceObject definition does not contain method. ServiceObject: [%SQL_Stored_Procedure_Based_SmO_Name%]. Method: [List]

In the SmartObject Services Tester tool this error appears immediately after you select the Execute action from the SmartObject context menu, before bringing UI with parameters etc:
 

ServiceObject definition does not contain method. ServiceObject: [%SQL_Stored_Procedure_Based_SmO_Name%]. Method: [List] It is also possible to see in Tester Tool that broken SmartObject has List method marked with a red cross instead of expected method (Execute).

The affected SmartObject can work fine for prolonged periods of time and the error appears very infrequently without apparent reasons/changes which can be considered as a trigger to the problem.

Resolution

This issue can be caused by the fact that SQL stored procedure uses temp table(s). This is not supported by SQL Service Broker as indicated in product documentation which says:

 

"Stored Procedures that use Temp Tables (i.e. SELECT...INTO) cannot be represented as Service Objects since the resulting table structure is unpredictable."

Source: K2 Five User Guide - SQL Server Service Type

 

Key word in the quote above is "unpredictable" - that means that it may work but can also get broken at times, which is what described in symptoms section above. It looks like during update of parent Service Instance SmartObject gets marked as "inconsistent" and enters into "broken" state due to the fact that temp tables are used in stored procedure.

Immediate/quick workaround is to recreate broken SmartObject with the same GUID, but long term solution is to remove use of temp tables from underlying SQL Server stored procedure. Alternatively you can try to call your stored procedure from another simple stored procedure without temp tables, and use this second one as a source for your SmartObject.


0 replies

Be the first to reply!

Reply