Symptoms
I have a sproc that is a smartobject. I changed a parameter and run the sproc in the database. Then I upload the SQL Server Service in the SmartObject Tester. When I try to enter to edit my smartform, it says "Object reference not set to an instance of an object"
What are the steps when I need to change the parameters of an smartobject sproc that is already created?
Diagnoses
The issue is with the smartobjects used on the form. The SQL was not playing along.
The SmartObject system was throwing the error when the form was being opened for edit. We could see this when attempting to access the smartobject as well as the service object in the Smartobject Tester. We looked at the stored procedure and noticed that it contained various IF-ELSE statements which is known to cause issues when attempting to create a smartobject from these complex stored procedures.
Resolution
We commented out the IF-ELSE statements after which we had no issues.
Set the FMTOnly setting by editing the stored procedure as follows:
--This goes in top of the Stored Procedure:
ALTER PROCEDURE [dbo].[sSomeStoredProcedure]
AS
/* see http://devio.wordpress.com/2013/01/15/the-amazing-fmtonly-trick/ regarding fmt OPTION */
DECLARE @fmtonlyON BIT
Set @fmtonlyON = 0
IF (1=0) SET @fmtonlyON = 1
SET FMTONLY OFF
--This goes at the end of the Stored Procedure (after all other statements):
IF @fmtonlyON = 1 SET FMTONLY ON
