The SQL Server configuration option "Allow Updates" settings have affect on SQL 2005 as well as SQL 2008 regarding SSIS. If you will face issues with Maintenance plans or SSIS packages, with following error :
DTExec: The package execution returned DTSER_FAILURE (1).
The SQL Server configuration option "Allow Updates" was set to 1 causing the Integrity Check task to fail.
WORKAROUND:
sp_configure "Allow Updates" and check of current run_value is setup to 1, set it to 0. And run your maintenance plan again. It will complete successfully.
sp_configure 'allow updates', 0
reconfigure with override
Additional Information:
NOTE: This is not a LiteSpeed bug/defect. The issue is with Microsoft's SQL Server. This posting is informational only.
From Microsoft:
The SQL Server BOL topic "Setting Server Configuration Options" for allow updates now says: "Obsolete. Do not use. Will caus an error during reconfigure." And the BOL topic for the allow updates Option now says: Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts."
The "allow updates" option does not have its intended effect in SQL2005, i.e. you cannot update the system tables directly even when this option is ON.
However, you will still get the error (5808) when RECONFIGURE is called after turning on this option. This is the same behavior as SQL2000. This option was meant to take effect with
RECONFIGURE WITH OVERRIDE only. The same error behavior as SQL2000 has been preserved in SQL2005 although turning on the option does not have any effect on the ability to update system tables (which is always disallowed in SQL2005). It is recommended that you not use this option anymore and remove it from your scripts.
Also: http://msdn.microsoft.com/en-us/library/ms186267(v=sql.90).aspx
The allow updates option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported.
It has probably no affect to direct changing of system tables, but this settings have affect on SQL 2005 as well as SQL 2008 regarding SSIS. If you will face issues with Maintenance plans or SSIS packages, with following error :
DTExec: The package execution returned DTSER_FAILURE (1).
You should check "Allow updates" option.
sp_configure "Allow Updates" and check of current run_value is setup to 1, set it to 0. And run your maintenance plan again. It will complete successfully.
sp_configure 'allow updates', 0
reconfigure with override
