Gen Client Server Encyclopedia (CSE) using SQL Server named instance

Products

• Gen

Issue/Introduction

The Gen Client Server Encyclopedia (CSE) can be installed against a SQL Server Named Instance using some workarounds documented below.
However, it should be noted that only a default (non-named) instance of SQL Server is formally supported with the Windows CSE Configuration utility cse_config.exe, so if a named instance is used it is at the user's own risk and is also subject to the Gen 8.6 Technical Requirements General Comments > Support Policy:
Development Platform Environments - see "CSE & Host Encyclopedia"
For Windows CSE Servers running on a 64-bit Windows operating system using a Microsoft SQL Server database, the 32-bit version of the Microsoft SQL Server Management Studio or tools must be installed. Also, the CSE configuration utility is designed to work with a non-named SQL Server instance only.

Environment

Release: Gen 8.6
Component: Gen Client Server Encyclopedia

Cause

Consider a scenario where the CSE database is created under a SQL Server Server_Name with named instance Instance_Name. The corresponding 32-bit and 64-bit Data Source Name(s) are defined with the same name as the Database and connect to Server_Name\Instance_name or perhaps Server_Name\Instance_Name,Port_Number

The CSE configuration will end with "Configuration Completed -  OK" as if there have been no problems. However the file loadschema.log ("C:\ProgramData\CA\Gen 8.6\logs\CSE") will show errors, the nature of which may vary e.g.

a. If no default instance exists or is not running, the step "Loading Encyclopedia schema tables" takes a long time due to delays waiting for timeouts to the server and loadschema.log contains this type of error:
Load Schema Server_Name Database_Name Userid Password
load schema90 SASC 
SQLState = 08001, NativeError = 2
Error = [Microsoft][ODBC Driver 13 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [2]. 
SQLState = 08001, NativeError = 2
Error = [Microsoft][ODBC Driver 13 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired
...
Load Schema Done 

b. Alternatively if a default instance exists and is running, the step "Loading Encyclopedia schema tables" is quick but loadschema.log contains errors relating to not being able to find the database name:
Load Schema Server_Name Database_Name Userid Password
load schema90 SASC 
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cannot open database "Database_Name" requested by the login. The login failed.
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user 'Userid '.
...
Load Schema Done 


The errors are due to the CSE Configuration utility using the default instance name Server_Name instead of "Server_Name\Instance_name" when calling the bcp program.

Resolution

The errors can be easily worked around using either of these 2 options:

1. Manually re-run the loadschema.cmd script as follows:
Rename loadschema.log to loadschema_failed.log
Start a Command Prompt Window and cd to directory C:\Program Files (x86)\CA\Gen86\CSE\cse_msqls
Run this command "loadschema .\ Server_Name\Instance_name Database_Name userid password loadschema.log out_file.lst err_file.lst"
Review the new loadschema.log file to confirm success e.g.
Load Schema Server_Name Database_Name Userid Password
load schema90 SASC 
Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000
1000 rows sent to SQL Server. Total sent: 7000
7871 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 157    Average : (50133.76 rows per sec.)
...

2. Re-run the CSE Configuration utility after updating the loadschema.cmd file as follows:
Go to directory C:\Program Files (x86)\CA\Gen86\CSE\cse_msqls 
Copy loadschema.cmd to loadschema_default.cmd 
Edit loadschema.cmd and change all occurrences of %2 to Server_Name\Instance_name
Re-run the CSE Configuration utility cse_config.exe and after completion review the new loadschema.log file to confirm success e.g.
Load Schema Server_Name Database_Name Userid Password
load schema90 SASC 
Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000
1000 rows sent to SQL Server. Total sent: 7000
7871 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 156    Average : (50455.13 rows per sec.)
...

Additional Information

Running the CSE as a Windows Service - CSESvcMD

1. The above workaround for the loadschema step is enough to be able to successfully start the CSE interactively using "Start/Programs/CA/AllFusion/Gen r7.6/CSE Servers/Start CSE". However, if it is required to start the CSE as a service an additional workaround is required:
When the CSESvcMD service is installed the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\CSESvcMD has a DependOnService Value which lists RPCSS and MSSQLSERVER. This should be updated to also add the SQL Server named instance service being used for the CSE database e.g. MSSQL$SQLEXPRESS.
Note that MSSQLSERVER should not be removed from the DependOnService list for the reasons listed in #2 below.

2. The current CSESvcMD has an internal dependency on MSSQLSERVER and if such a service does not exist the CSESvcMD will fail to start. The MSSQLSERVER service will not exist if a default instance of SQL Server has not been installed on the machine running the CSESvcMD. Therefore to workaround this limitation the user can:
Either: Install a default instance of SQL Server (note that this instance will not be used by the CSE it is just required to enable the CSESvcMD service to start).
Or: Create a dummy service named MSSQLSERVER. This can be done quite easily using the Windows Resource Kit tool SRVANY.EXE. See the following Microsoft page for further information: How to Create a User-Defined Service
NOTE: It appears that the Windows NT Resource Kit and Windows 2003 Resource Kit Tools (rktools.exe) are no longer available directly from Microsoft. However, this web archive link can be used to download rktools.exe: https://web.archive.org/web/20070119115200/https://download.microsoft.com/download/8/e/c/8ec3a7d8-05b4-440a-a71e-ca3ee25fe057/rktools.exe
Alternatively, a zip file containing the required instsrv.exe and srvany.exe files has been attached to this article. Please note that Broadcom takes no support responsibility for the use of those 2 files.
For details on a support implementation of the dummy service MSSQLSERVER see KB article: Gen 8.6 CSE configuration for remote SQL Server database