SQL stored procedure based SmartObject suddenly stops working with an error: "ServiceObject definition does not contain method"
kbt167049
PRODUCTIssue
The SmartObject based on a SQL Server stored procedure suddenly stops working with an error:
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:
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:
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."
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.