Determine SmartObject method 'List' or 'Execute' when SmartObject was created from SQL Store Procedure
kbt136024
PRODUCTIssue
In 4.6.9, the advanced SmartObject (data from stored procedure) returns List Method, but after upgrade to 4.7 the form has a missing object. The method in service object has changed from 'List' to 'Execute' and SmartObject doesn't return any object back.
Symptoms
Verify that the logical determine method 'List' or 'Execute' of SmartObject for 4.6.9 and 4.7 is the same.
If a stored procedure just has an Update statement, then the return from the stored proc is nothing, or possibly a result code for successful (but not a table). Next, create an Execute method for the stored proc, because no results are expected back.
If the stored proc returns a table (selects data and returns it), then Create a List method is used, because it is sending data that can be filtered.
If there was a List method previously, then it needed to return some data in a table for it to have been seen as such. But an upgrade should not change this to execute if it still returns a data table.
There's a possibility that there was a select in the stored proc previously when the SMO was created, and the select was removed but the SMO was never updated. After the upgrade refresh, there was no data table returned.
This could also be because of a TempTable which is not supported. If the the table it selects from is changed from a normal table to a temp table, there won't be a table returned.
Troubleshooting Steps
It is recommended that each individual scenario is investigated. There's a possibility that there were changes to the stored proc.
The K2 server needs to get the schema to build a list method and to determine the schema of the stored procedure, an equivalent of the following SQL query was run in the server's code:
USE [DatabaseAppName]
GO
SET FMTONLY ON
DECLARE @return_value int,
@ApplicationID varchar(20)
EXEC @return_value = [dbo].[StoreName]
@ApplicationID = @ApplicationID OUTPUT
SELECT @ApplicationID as N'@ApplicationID'
SELECT 'Return Value' = @return_value
SET FMTONLY OFF
If any error appears in the SQL management studio, which the K2 server will also get in its codes, treat it as an Execute method because it doesn't get any results from SQL about the schema.
Use this code to test execute store procedure on SQL Management Studio.