When installing Control Compliance Suite (CCS) 11 with the option to use pre created CSM_DB and CSM_Reports databases, the install fails with error messages relating to database creation issues. This information is in addition to the information presented in the CCS 11 Planning and Deployment Guide on page 73, Table 2-14.
The Install window reports the following error:
Exception occurred while executing: 'DECLARE @databasemdfname NVARCHAR(max)
DECLARE @databaseFilePath NVARCHAR(1000)
DECLARE @databaseFilePathTemp NVARCHAR(max)
SET @databasemdfname = DB_NAME() + '.mdf'
----Get Database file pat
set @databaseFilePathTemp = LOWER((SELECT top 1 [filename] FROM CSM_db.dbo.sysfiles where [filename] LIKE '%mdf%'))
select @databaseFilePath = SUBSTRING(@databaseFilePathTemp, 1,CHARINDEX(@databasemdfname,@databaseFilePathTemp) - 1)
declare @sqlstring nvarchar(max)
select @sqlstring = 'IF NOT EXISTS ( SELECT * FROM sys.filegroups WHERE Name= ''CCS_QUERIES'')
BEGIN ALTER DATABASE ' + DB_Name() + ' ADD FILEGROUP CCS_QUERIES'
select @sqlstring = @sqlstring + ' ALTER DATABASE ' + db_name()
+ ' ADD FILE( NAME = CCS_QUERIES,
FILENAME = N'''+ @databaseFilePath + 'CCS_QUERIES.ndf'',
SIZE = 50MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%)
TO FILEGROUP CCS_QUERIES END'
EXEC (@sqlstring)'. Error: 'Invalid length parameter passed to the LEFT or SUBSTRING function.'.
The installer attempts to create a database file called 'CCS_Queries.ndf' for the CSM_DB database, but unless this database file was also precreated by the sysadmin, the installer user does not have the necessary permissions to add this file to the database, thus the error.
Ensure that the CCS_Queries database file is created as a database file for the CSM_DB before running the installer. The information needed to create this file is as follows:
- Logical name: CCS_Queries
- File type: Rows Data
- Filegroup: Primary
- Initial size: 50 MB
- Autogrowth: by 10 percent , unrestricted growth
In addition, please note that the precreated databases should be completely empty of tables as well as data.
CCS 11 installation with pre created databases, Installing account has dbo permissions on the pre created databases, but not sysadmin permission on the SQL instance.