Register SQL Service Instance: Timeout Expired
kbt152045
PRODUCTK2 Five
K2 blackpearl
TAGSSmartObjects
Integration
SQL
This article was created in response to a support issue logged with K2. The content may include typographical errors and may be revised at any time without notice. This article is not considered official documentation for K2 software and is provided "as is" with no warranties.
Issue
Registering an SQL Service Instance eventually times out:"VALIDATION Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
Symptoms
This generally occurs when there are too many objects (tables, stored procedures and views) within the custom database. K2 will try to create ServiceObjects for every object in the database it has access to.It is recommended to limit the permissions the authenticated user has on the database so K2 will not have to create ServiceObjects for every object it discovers. This is listed under the Considerations of the SQL Server Service Type.
Troubleshooting Steps
Running an SQL Profiler trace shows the following queries being run against the custom database when registering a SQL Service Instance as the authenticated user:
- Tables SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME, CASE ( SELECT MAX ( CASE so.xtype WHEN 'PK' THEN 1 ELSE 0 END) FROM sysobjects AS so WHERE so.parent_obj = OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) ) WHEN 1 THEN 'yes' ELSE 'no' END AS [hasKey] FROM INFORMATION_SCHEMA.TABLES AS tbls WHERE TABLE_TYPE = N'BASE TABLE' AND [TABLE_SCHEMA] NOT IN (N'cdc') AND [TABLE_NAME] NOT IN (N'sysdiagrams', N'systranschemas', N'__RefactorLog') ORDER BY TABLE_SCHEMA, TABLE_NAME;
- Stored Procedures SELECT DISTINCT SPECIFIC_SCHEMA, SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = N'PROCEDURE' AND [SPECIFIC_SCHEMA] NOT IN (N'cdc') AND [SPECIFIC_NAME] NOT IN (N'sp_alterdiagram',N'sp_creatediagram',N'sp_dropdiagram',N'sp_helpdiagramdefinition',N'sp_helpdiagrams',N'sp_renamediagram',N'sp_upgraddiagrams') ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME;
- Views SELECT TABLE_SCHEMA, TABLE_NAME, IS_UPDATABLE FROM INFORMATION_SCHEMA.VIEWS WHERE [TABLE_SCHEMA] NOT IN (N'cdc') ORDER BY TABLE_SCHEMA, TABLE_NAME;
Execute each of the queries above and take note of the execution time. You will need to take the total duration (in seconds) into account when setting the base value for the Command Timeout setting.
