Maintenance plan warning for SP2
Due to a regression bug introduced in SP2 some of
the maintenance plan tasks that can operate on multiple databases may not perform
as expected.
If a maintenance plan component uses TSQL of this nature
USE [DBFred]
GO
DBCC CHECKDB....
GO
USE [DBSimon]
GO
DBCC
CHECKDB....
To handle errors in the process the batch is split into multiple commands
(split by the GO keyword) so that if one fails the others can still be run.
Because a new the server connection is being made for each statement the
context of the connection is always the database specified in the connection
string. Rather than the one specified in the previous USE statement.
I have found a partial workaround for the executesql task (the
maintenance plan one), which is to open the maintenance plan in BI Studio and
change the properties of the connection to not reset the connection.
However this does not work for the Database Integrity component.
I therefore suggest anyone that has upgraded to SP2 and is using the Database
Integrity component to change and generate the SQL that would have been used and
use either the normal SSIS ExecuteSQL Task or the Maintenance ExecuteSQL Task
with the above work around.
-