Home / server / Questions / 683174
Accepted
vaso
Asked: 2015-04-17 03:11:19 +0800 CST

SQL Server 2014 SP1 installation failure - bug in SSIS_hotfix_install.sql

  • 772

Installation of SQL Server 2014 SP1 fails if you have as SSIS Catalog on your server. After that, server does not start. Rebuilding system databases did not help (not sure, maybe because system databases were not actually corrupted, hence they were not really rebuilt maybe).

There's a bug in the script SSIS_hotfix_install.sql which ships with SP1 on line 3188:

CREATE NONCLUSTERED INDEX [IX_internal_object_parameters_inc] ON [internal].[object_parameters]

They used a 2-part name for the table while the script which is supposed to upgrade SSISDB is executed in the master database.

The script can by default be found in C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Install , but apparently they do not take it from there for execution upon server startup.
They probably take it from sqlscriptupgrade.dll found in C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn. Does anybody know how to bypass execution of that script and get server to start?

service-pack

2 Answers

  1. Best Answer

    Start SQL Server with trace flag 902. This will bypass the execution of upgrade script.

    1. Start -> All Programs -> Microsoft SQL Server 2008 R2 or the highest version installed -> Configuration Tools -> SQL Server Configuration Manager

    2. In SQL Server Configuration Manager, click SQL Server Services.

    3. In the right pane, right-click SQL Server (), and then click Properties.

    4. 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

    5. Click OK.

    6. Restart the Database Engine.
    7. Connect to SQL Server instance from SQL Server Management Studio and take corrective actions to resolve the errors causing script upgrade to fail.
    8. Now finally remove the -T902 trace flag from SQL Server Configuration manager
    9. Restart SQL Server Instance
    10. 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).

    • 10

  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:

    1. After failed installation set -T902 startup parameter.
    2. 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]

    1. Remove the -T902 startup parameter and restart server

    2. Clean up master database by executing

    use master; drop table [internal].[object_parameters]; drop schema internal;

    1. Execute SSIS_hotfix_install.sql manually after correcting line 3188 as detailed above.

    2. 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.

    • 4