Symptoms

CRM Service and SQL View
 

Diagnoses

We are trying to create a SmartObject that connects to the StringMap sql view for CRM. StringMap hosts all of the CRM optionset values, and we need to expose user friendly labels for optionset fields in CRM.

When we expand the CRM service instance, we do NOT see StringMap listed in the ServiceObject schema. We suspect it's because StringMap is not a true CRM entity, and rather it is a sql view that joins fields from various entities.

If that's the case, can we expose a SQL view displaying CRM information using the CRM service broker? Or do we have to make to create a separate SmartObject to make a connection to the SQL database itself?
 

Resolution

The StringMap is not an CRM entity that the CRM broker support/exposes please see documentation below:

https://www.k2.com/onlinehelp/k2blackpearl/userguide/current/webframe.html_crm_requirements.html

As such, I believe perhaps using a SQL service broker instead which can expose tables, procedures and views may work.

If that data is also exposed as a supported web service endpoints, perhaps using the out-of-the-box web service broker may also work:

http://help.k2.com/onlinehelp/k2blackpearl/userguide/current/webframe.html_Endpoint_Service_Brokers.html

If you are plannig on using the K2 Service account to authenticate against this database in a SQL service instance, usually you will need to give the K2 service account "db_owner", or "db_datareader" and "db_datawriter" database role on this database.

If this database contains a lot of objects that you do not need to expose, you can also just give the K2 service account "public" database role on this database, and then grant the K2 service account permissions on the objects level (in this case the view and any tables that the view makes use of).

Please see the documentation below for more information on the SQL service broker:

https://help.k2.com/onlinehelp/k2blackpearl/userguide/current/webframe.html_sic02.html

Option 4: Use native execution = false and On different SQL server = true (This option can be selected however, this will be ignored by the service and will function similar to Option 2, no linked server object will be created).

These options will effect only "List" type methods and the "Use native execution" key will use a created linked server object to improve performance (Option 1: the K2 service account will need the necessary permission on the SQL instance hosting the K2 database to create the linked server object alternative, one can temporarily grant the K2 service account SysAdmin permission, so that during the list method's first execution, the linked server object will be created this permission can be revoked once the linked server has been created):

http://help.k2.com/displaycontent.aspx?id=5998