Symptoms
Customer configured SQL server service instance and created smart objects for the database tables. While the read method works correctly, invoking the list method produces the following error message:
"Invalid object name '%OBJECT_NAME%'"
When performing the capture of TCP traffic between K2 server and database server it is visible that when invoking List method the service broker does not send any queries to the database, but query is visible for the Read method.
Diagnoses
Initially SQL server service instance was configured to use "Native SQL Execution", this configuration set-ting applies only to List methods and generally improves performance. Here is the short video which shows performance gain when using this setting:
http://www.youtube.com/watch?v=OQPiiQLvRIA
To quote K2 documentation about Native SQL Execution setting:
"This setting will enable the ability to execute and filter queries directly against the SQL server where the complex queries have been specified in ADO.NET and reduce complex data structures being passed be-tween K2 and the databases. When this option is set to true a SQL query is returned instead of a data ta-ble."
So with this setting enabled you most likely won't see plain SQL query being sent to K2 database.
What was strange for me about your resolution that it does not seem to be consistent with our documen-tation located here:
https://help.k2.com/onlinehelp/k2blackpearl/userguide/4.6.7/webframe.html_sic02.html
If you look at the table with Options descriptions you will see the following information:
Use Native SQL Execution - This will enable the ability to execute and filter queries directly against the SQL server where the complex queries have been specified in ADO.NET and reduce complex data structures being passed between K2 and the databases. When this option is set to true a SQL query is returned instead of a data table.
Note: This only applies to LIST methods. All other method types will be executed directly against the data-base.
Refer to the options section below for more information.
On Different SQL Server - Specifies whether the specified Database is located on a different server than the server where the K2 specific Databases are located. Refer to the options section below for more infor-mation.
This is more or less clear but if you look at the Options section to which both descriptions referring to it is there some things looks confusing. Namely Options table in documentation says that Option 4 which matches your working settings (Native SQL Execution = False On Different SQL Server = True) can be con-figured, but it will actually work as Option 2 (Native Execution = False and On Different SQL Server = False). Plus there is also this warning under the table: "On Different SQL Server' will ONLY ever be used (true) when 'Use Native SQL Execution' is also set to true." So it is very unclear from this documentation how it all works – either On Different SQL Server is not necessary for accessing a database on different server, or Native SQL Execution is not required for it to work (most likely the latter).
If we look at the same section in Appit documentation:
http://help.k2.com/onlinehelp/k2appit/userguide/current/default.htm_service_types_and_instances/sql_server_service.htm_On
There is no confusing options descriptions I mentioned. It looks more consistent and also says in Use Na-tive SQL Execution option description: “If you get runtime errors when executing methods against the tar-get SQL database, try to change this setting to False. Setting the value to False might have a performance impact. See "On Different SQL Server setting and linked servers" for more information.”
Resolution
Customer was able to resolve this issue by configuring SQL Server service instance with Use Native SQL Execution = False and On Different SQL Server = True parameters. This resolved the issue with error mes-sage "Invalid object name '%OBJECT_NAME%'" being thrown whenever List method is used. I also can con-firm that we see similar other tickets where solution was the same.
This solution looks inconsistent with our documentation and we logged documentation update request to update/clarify this (TFS item 527449).
Related Links:
Use Native SQL Execution and Performance impact:
http://www.youtube.com/watch?v=OQPiiQLvRIA
SQL Server Service Service Instance Configuration documentation section of K2 blackpearl user guide :
https://help.k2.com/onlinehelp/k2blackpearl/userguide/4.6.7/webframe.html_sic02.html
SQL Server Service Service Instance Configuration documentation section of K2 Appit user guide:
http://help.k2.com/onlinehelp/k2appit/userguide/current/default.htm_service_types_and_instances/sql_server_service.htm_On
