Symptoms
SQL service broker unable to find Stored Procedures in database
Diagnoses
We recently migrated our dev database to a new server and moved all the tables, Stored Procs and Views.
However, for some reason none of the stored procs are getting detected even though they are present in the database (Please refer the screenshot of the service object tester). As a result the smartobjects and the forms are throwing errors.
Could you please help in troubleshooting this issue.
Resolution
Granted the K2 Service Account at least Read/Write and SELECT permission to the DB , then redeployed the Service Instances and the SMO's
So the permissions for the Tables are a little more lax than what's needed for discovering the sProcs . Making sure the K2 Service Account has full permissions to the DB allows K2 to fully describe the Schema's . The Root XML missing on the SMO's are usually caused by the Service Instance and the SMO's not being in Sycn . So when the Service Instance is registered without the sProcs having been described , the Service Instance became out of sycn with the Service Instance schema the SMO expects . Redeploying everything after you ensured that the permissions were granted allowed all the objects to deploy correctly with the correctly described schema's
