SQL Server 2014 SP1 installation failure - bug in SSIS_hotfix_install.sql
Solution 1:
Start SQL Server with trace flag 902. This will bypass the execution of upgrade script.
Start -> All Programs -> Microsoft SQL Server 2008 R2 or the highest version installed -> Configuration Tools -> SQL Server Configuration Manager
In SQL Server Configuration Manager, click SQL Server Services.
In the right pane, right-click SQL Server (), and then click Properties.
On the Startup Parameters tab, in the Specify a startup parameter box, type the parameter (in this case the trace flag -T902), and then click Add. You will now see the parameters similar to below SQL Server Istance Startup Parameters SQL Server Istance Startup Parameters
Click OK.
- Restart the Database Engine.
- Connect to SQL Server instance from SQL Server Management Studio and take corrective actions to resolve the errors causing script upgrade to fail.
- Now finally remove the -T902 trace flag from SQL Server Configuration manager
- Restart SQL Server Instance
- Verify Errorlog to make sure script upgrade finished successfully
Then, afterwards, manually execute the SSIS_hotfix_install.sql script (after having added SSISDB. in line 3188).
Solution 2:
I tried the suggested procedure - it did not work which was somewhat foreseeable. Upon subsequent startup, after removing -T902 trace flag, the server tries to execute the same script compiled into dll (see above), which I cannot modify. The script is dumb in not detecting that the changes contained in it are already applied. A dirty workaround which I could think of is creating that table in master database to allow script to build the index on it and succeed. I tested this and it worked. The whole procedure:
- After failed installation set -T902 startup parameter.
- Execute the following code on your server:
USE master;
GO
create schema internal;
go
CREATE TABLE [internal].[object_parameters](
[parameter_id] [bigint] IDENTITY(1,1) NOT NULL,
[project_id] [bigint] NOT NULL,
[project_version_lsn] [bigint] NOT NULL,
[object_type] [smallint] NOT NULL,
[object_name] nvarchar NOT NULL,
[parameter_name] [sysname] NOT NULL,
[parameter_data_type] nvarchar NOT NULL,
[required] [bit] NOT NULL,
[sensitive] [bit] NOT NULL,
[description] nvarchar NULL,
[design_default_value] [sql_variant] NULL,
[default_value] [sql_variant] NULL,
[sensitive_default_value] varbinary NULL,
[base_data_type] nvarchar NULL,
[value_type] char NOT NULL,
[value_set] [bit] NOT NULL,
[referenced_variable_name] nvarchar NULL,
[validation_status] char NOT NULL,
[last_validation_time] datetimeoffset NULL,
CONSTRAINT [PK_Object_Parameters] PRIMARY KEY CLUSTERED
(
[parameter_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Remove the -T902 startup parameter and restart server
Clean up master database by executing
use master;
drop table [internal].[object_parameters];
drop schema internal;
Execute SSIS_hotfix_install.sql manually after correcting line 3188 as detailed above.
Restart server.
The reason for executing corrected SSIS_hotfix_install.sql at the end of the procedure is to prevent the buggy script embedded in dll from dropping the index upon subsequent startup, before SQL Server considers upgrade as completed successfully.