Can the Gen r7.6 Client Server Encyclopedia be installed against a SQL Server 2005 Named Instance?

Document ID : KB000052154
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

The Gen r7.6 Client Server Encyclopedia (CSE) can be installed against a SQL Server 2005 Named Instance using some workarounds documented below. However it should be noted that only a default instance of SQL Server is formally certified and supported with the Gen CSE and so if a named instance is used it is at the user's own risk and subject to the Support Policy in the Gen r7.6 Technical Requirements document.

Solution:

Installation/Configuration

Consider a scenario where the Data Source name for the CSE database is defined under a SQL Server named instance e.g. Microsoft SQL Server 2005 Express Edition might have instance name Server\SQLEXPRESS.

The CSE configuration program (cse_config.exe) will run without errors except for the loading of the schema tables. This step is executed using the command file loadschema.cmd (directory C:\Program Files\CA\AllFusion Gen\CSE\cse_msqls) and will result in the log file loadschema.log containing errors e.g.

Load Schema Server DATABASE userid password
load schema80 SASC
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'userid'.
SQLState = 08S01, NativeError = 4
Error = [Microsoft][SQL Native Client]Shared Memory Provider: The system cannot open the file.
SQLState = 08S01, NativeError = 4
Error = [Microsoft][SQL Native Client]Communication link failure
SQLState = 42000, NativeError = 4060
Error = [Microsoft][SQL Native Client][SQL Server]Cannot open database requested in login 'DATABASE' . Login fails.
...

The errors are due to the CSE configuration program using the default instance name "Server" instead of "Server\SQLEXPRESS" when calling the bcp program. This can be easily worked around by manually running the command file as follows.

  1. Rename loadschema.log to loadschema_failed.log

  2. Perform following steps:

    1. Start a Command Prompt Window and cd to directory C:\Program Files\CA\AllFusion Gen\CSE\cse_msqls

    2. Run this command "loadschema .\ Server\SQLEXPRESS DATABASE userid password" (note that DATABASE is the actual database name and not the data source name)

  3. Review the new loadschema.log file to confirm success e.g.

    Load Schema Server\SQLEXPRESS DATABASE userid password
    load schema80 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
    7346 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total : 13297 Average : (552.46 rows per sec.)
    ...

  4. The CSE should now be ready to be started.

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 links for further information:

    http://support.microsoft.com/kb/137890.
    http://www.microsoft.com/downloads/details.aspx?FamilyID=9D467A69-57FF-4AE7-96EE-B18C4790CFFD&displaylang=en.