Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Who is affected?

You are affected if you use SQL Server 2005 maintenance plans, those plans include a History Cleanup Task or a Maintenance Cleanup Task, and you install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance plans are not affected.

What is the issue?

Some maintenance plans might include the Maintenance Cleanup Task and the History Cleanup Task, which allow users to delete information older than a specified interval. When SQL Server 2005 was released, this interval was measured in days, weeks, months, or years. In response to customer feedback, SQL Server 2005 SP2 includes significant enhancements to maintenance plans, including an enhancement that allows users to specify the cleanup interval in hours. After upgrading to SP2, and until you update cleanup tasks using SQL Server SP2 tools as described below, existing cleanup task intervals are misinterpreted. This leads to earlier data cleanup than was intended.

What if I have already installed SP2 on my server?

You can restore your maintenance plans to their former behavior by opening them up in the Maintenance Plan Designer, opening any cleanup tasks, adjusting the age units to the proper value, and saving the maintenance plan. Use SQL Server 2005 SP2 tools to make these changes.

What if I haven't yet installed SP2 on my server?

We are currently investigating our approach to this problem and will provide guidance shortly. If you depend on the Maintenance Cleanup Task or the History Cleanup Task and are not able to verify and possibly update cleanup tasks using matching server and tool versions, you may want to wait for that guidance before installing SP2.

I installed sp2 on my server. It failed on database engine service. And my maintance plans corrupted. I am not able to open existing plan or create new plan.

Gives the following error:

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Interface not registered (Exception from HRESULT: 0x80040155). (Microsoft.SqlServer.ManagedDTS)

Any help?

|||Good to see cleanup task in maintenance plan. But the task can only delete one type of files. If I want to delete db backup files, log backup files and plan log files, I need to add two more cleanup tasks after creating plan. Why can't add cleanup option in backup and logging page like sql2k's plan does?|||I have the same error. Does anybody out there have an answer to this? Please email me a bkellman@.hcfmw.com|||There is new post sp2 fix, check kb934458.|||

the update to SP2 did not fix it for me.

however this solution in a different blog did: regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll”

|||

Rmiao,

You don't need to create new tasks to perform the cleanup that you want. You can define one type of clean up task in a miantenance plan and then add addtional steps to the plan to clean up other types of files as well. The Wizard does not deal with this very well, expecially since you want to remove several diferent types of files.

Once you have created the Maintenance Plan that you desire, edit the maintenance plan. With this method the plan will appear as a flow chart with all of the steps shows as nodes connected by conditional lines. You are free to add as many addtional tasks as you wish at this point. I further suggest that conditions be added so that previous backup files are not deleted in case a new one is not created for some reason (i.e. full disk drive, missing/bad media, etc.)

|||Jeff, that's what I did. But why can't Microsoft look at sql2k's maintenance plan when design sql2k5's?|||The patch that fixes the maintenance plan bug is supposedly

SQLServer2005-KB933508-x86-ENU.exe, which addresses KB933508. Unfortunately the installer seems to be buggy. When I try to install the patch, I get to a point where I cannot select anything to install from the tree, and the Next button is grey (disabled).

Anyone experience this, or know of another patch that includes this fix that actually installs?

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Who is affected?

You are affected if you use SQL Server 2005 maintenance plans, those plans include a History Cleanup Task or a Maintenance Cleanup Task, and you install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance plans are not affected.

What is the issue?

Some maintenance plans might include the Maintenance Cleanup Task and the History Cleanup Task, which allow users to delete information older than a specified interval. When SQL Server 2005 was released, this interval was measured in days, weeks, months, or years. In response to customer feedback, SQL Server 2005 SP2 includes significant enhancements to maintenance plans, including an enhancement that allows users to specify the cleanup interval in hours. After upgrading to SP2, and until you update cleanup tasks using SQL Server SP2 tools as described below, existing cleanup task intervals are misinterpreted. This leads to earlier data cleanup than was intended.

What if I have already installed SP2 on my server?

You can restore your maintenance plans to their former behavior by opening them up in the Maintenance Plan Designer, opening any cleanup tasks, adjusting the age units to the proper value, and saving the maintenance plan. Use SQL Server 2005 SP2 tools to make these changes.

What if I haven't yet installed SP2 on my server?

We are currently investigating our approach to this problem and will provide guidance shortly. If you depend on the Maintenance Cleanup Task or the History Cleanup Task and are not able to verify and possibly update cleanup tasks using matching server and tool versions, you may want to wait for that guidance before installing SP2.

I installed sp2 on my server. It failed on database engine service. And my maintance plans corrupted. I am not able to open existing plan or create new plan.

Gives the following error:

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Interface not registered (Exception from HRESULT: 0x80040155). (Microsoft.SqlServer.ManagedDTS)

Any help?

|||Good to see cleanup task in maintenance plan. But the task can only delete one type of files. If I want to delete db backup files, log backup files and plan log files, I need to add two more cleanup tasks after creating plan. Why can't add cleanup option in backup and logging page like sql2k's plan does?|||I have the same error. Does anybody out there have an answer to this? Please email me a bkellman@.hcfmw.com|||There is new post sp2 fix, check kb934458.|||

the update to SP2 did not fix it for me.

however this solution in a different blog did: regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll”

|||

Rmiao,

You don't need to create new tasks to perform the cleanup that you want. You can define one type of clean up task in a miantenance plan and then add addtional steps to the plan to clean up other types of files as well. The Wizard does not deal with this very well, expecially since you want to remove several diferent types of files.

Once you have created the Maintenance Plan that you desire, edit the maintenance plan. With this method the plan will appear as a flow chart with all of the steps shows as nodes connected by conditional lines. You are free to add as many addtional tasks as you wish at this point. I further suggest that conditions be added so that previous backup files are not deleted in case a new one is not created for some reason (i.e. full disk drive, missing/bad media, etc.)

|||Jeff, that's what I did. But why can't Microsoft look at sql2k's maintenance plan when design sql2k5's?|||The patch that fixes the maintenance plan bug is supposedly

SQLServer2005-KB933508-x86-ENU.exe, which addresses KB933508. Unfortunately the installer seems to be buggy. When I try to install the patch, I get to a point where I cannot select anything to install from the tree, and the Next button is grey (disabled).

Anyone experience this, or know of another patch that includes this fix that actually installs?

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in
SP2 that affects existing cleanup tasks until updated using SQL Server SP2
tools.
Who is affected?
You are affected if you use SQL Server 2005 maintenance plans, those plans
include a History Cleanup Task or a Maintenance Cleanup Task, and you
install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
plans are not affected.
What is the issue?
Some maintenance plans might include the Maintenance Cleanup Task and the
History Cleanup Task, which allow users to delete information older than a
specified interval. When SQL Server 2005 was released, this interval was
measured in days, weeks, months, or years. In response to customer feedback,
SQL Server 2005 SP2 includes significant enhancements to maintenance plans,
including an enhancement that allows users to specify the cleanup interval
in hours. After upgrading to SP2, and until you update cleanup tasks using
SQL Server SP2 tools as described below, existing cleanup task intervals are
misinterpreted. This leads to earlier data cleanup than was intended.
What if I have already installed SP2 on my server?
You can restore your maintenance plans to their former behavior by opening
them up in the Maintenance Plan Designer, opening any cleanup tasks,
adjusting the age units to the proper value, and saving the maintenance
plan. Use SQL Server 2005 SP2 tools to make these changes.
What if I haven't yet installed SP2 on my server?
We are currently investigating our approach to this problem and will provide
guidance shortly. If you depend on the Maintenance Cleanup Task or the
History Cleanup Task and are not able to verify and possibly update cleanup
tasks using matching server and tool versions, you may want to wait for that
guidance before installing SP2.Wow, how many dba's will loose their jobs over that one? How did something
like that get past QA or even the developers of that feature?
Andrew J. Kelly SQL MVP
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uzdQ8B$VHHA.488@.TK2MSFTNGP06.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>|||Ed,
Pre SP2 the maintenance plans created SSIS packages. Is this still the
behaviour in SP2?
Thanks
Chris
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uzdQ8B$VHHA.488@.TK2MSFTNGP06.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>sql

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Who is affected?

You are affected if you use SQL Server 2005 maintenance plans, those plans include a History Cleanup Task or a Maintenance Cleanup Task, and you install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance plans are not affected.

What is the issue?

Some maintenance plans might include the Maintenance Cleanup Task and the History Cleanup Task, which allow users to delete information older than a specified interval. When SQL Server 2005 was released, this interval was measured in days, weeks, months, or years. In response to customer feedback, SQL Server 2005 SP2 includes significant enhancements to maintenance plans, including an enhancement that allows users to specify the cleanup interval in hours. After upgrading to SP2, and until you update cleanup tasks using SQL Server SP2 tools as described below, existing cleanup task intervals are misinterpreted. This leads to earlier data cleanup than was intended.

What if I have already installed SP2 on my server?

You can restore your maintenance plans to their former behavior by opening them up in the Maintenance Plan Designer, opening any cleanup tasks, adjusting the age units to the proper value, and saving the maintenance plan. Use SQL Server 2005 SP2 tools to make these changes.

What if I haven't yet installed SP2 on my server?

We are currently investigating our approach to this problem and will provide guidance shortly. If you depend on the Maintenance Cleanup Task or the History Cleanup Task and are not able to verify and possibly update cleanup tasks using matching server and tool versions, you may want to wait for that guidance before installing SP2.

I installed sp2 on my server. It failed on database engine service. And my maintance plans corrupted. I am not able to open existing plan or create new plan.

Gives the following error:

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Interface not registered (Exception from HRESULT: 0x80040155). (Microsoft.SqlServer.ManagedDTS)

Any help?

|||Good to see cleanup task in maintenance plan. But the task can only delete one type of files. If I want to delete db backup files, log backup files and plan log files, I need to add two more cleanup tasks after creating plan. Why can't add cleanup option in backup and logging page like sql2k's plan does?|||I have the same error. Does anybody out there have an answer to this? Please email me a bkellman@.hcfmw.com|||There is new post sp2 fix, check kb934458.|||

the update to SP2 did not fix it for me.

however this solution in a different blog did: regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll”

|||

Rmiao,

You don't need to create new tasks to perform the cleanup that you want. You can define one type of clean up task in a miantenance plan and then add addtional steps to the plan to clean up other types of files as well. The Wizard does not deal with this very well, expecially since you want to remove several diferent types of files.

Once you have created the Maintenance Plan that you desire, edit the maintenance plan. With this method the plan will appear as a flow chart with all of the steps shows as nodes connected by conditional lines. You are free to add as many addtional tasks as you wish at this point. I further suggest that conditions be added so that previous backup files are not deleted in case a new one is not created for some reason (i.e. full disk drive, missing/bad media, etc.)

|||Jeff, that's what I did. But why can't Microsoft look at sql2k's maintenance plan when design sql2k5's?|||The patch that fixes the maintenance plan bug is supposedly

SQLServer2005-KB933508-x86-ENU.exe, which addresses KB933508. Unfortunately the installer seems to be buggy. When I try to install the patch, I get to a point where I cannot select anything to install from the tree, and the Next button is grey (disabled).

Anyone experience this, or know of another patch that includes this fix that actually installs?

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Who is affected?

You are affected if you use SQL Server 2005 maintenance plans, those plans include a History Cleanup Task or a Maintenance Cleanup Task, and you install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance plans are not affected.

What is the issue?

Some maintenance plans might include the Maintenance Cleanup Task and the History Cleanup Task, which allow users to delete information older than a specified interval. When SQL Server 2005 was released, this interval was measured in days, weeks, months, or years. In response to customer feedback, SQL Server 2005 SP2 includes significant enhancements to maintenance plans, including an enhancement that allows users to specify the cleanup interval in hours. After upgrading to SP2, and until you update cleanup tasks using SQL Server SP2 tools as described below, existing cleanup task intervals are misinterpreted. This leads to earlier data cleanup than was intended.

What if I have already installed SP2 on my server?

You can restore your maintenance plans to their former behavior by opening them up in the Maintenance Plan Designer, opening any cleanup tasks, adjusting the age units to the proper value, and saving the maintenance plan. Use SQL Server 2005 SP2 tools to make these changes.

What if I haven't yet installed SP2 on my server?

We are currently investigating our approach to this problem and will provide guidance shortly. If you depend on the Maintenance Cleanup Task or the History Cleanup Task and are not able to verify and possibly update cleanup tasks using matching server and tool versions, you may want to wait for that guidance before installing SP2.

I installed sp2 on my server. It failed on database engine service. And my maintance plans corrupted. I am not able to open existing plan or create new plan.

Gives the following error:

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Interface not registered (Exception from HRESULT: 0x80040155). (Microsoft.SqlServer.ManagedDTS)

Any help?

|||Good to see cleanup task in maintenance plan. But the task can only delete one type of files. If I want to delete db backup files, log backup files and plan log files, I need to add two more cleanup tasks after creating plan. Why can't add cleanup option in backup and logging page like sql2k's plan does?|||I have the same error. Does anybody out there have an answer to this? Please email me a bkellman@.hcfmw.com|||There is new post sp2 fix, check kb934458.|||

the update to SP2 did not fix it for me.

however this solution in a different blog did: regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll”

|||

Rmiao,

You don't need to create new tasks to perform the cleanup that you want. You can define one type of clean up task in a miantenance plan and then add addtional steps to the plan to clean up other types of files as well. The Wizard does not deal with this very well, expecially since you want to remove several diferent types of files.

Once you have created the Maintenance Plan that you desire, edit the maintenance plan. With this method the plan will appear as a flow chart with all of the steps shows as nodes connected by conditional lines. You are free to add as many addtional tasks as you wish at this point. I further suggest that conditions be added so that previous backup files are not deleted in case a new one is not created for some reason (i.e. full disk drive, missing/bad media, etc.)

|||Jeff, that's what I did. But why can't Microsoft look at sql2k's maintenance plan when design sql2k5's?|||The patch that fixes the maintenance plan bug is supposedly

SQLServer2005-KB933508-x86-ENU.exe, which addresses KB933508. Unfortunately the installer seems to be buggy. When I try to install the patch, I get to a point where I cannot select anything to install from the tree, and the Next button is grey (disabled).

Anyone experience this, or know of another patch that includes this fix that actually installs?

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Who is affected?

You are affected if you use SQL Server 2005 maintenance plans, those plans include a History Cleanup Task or a Maintenance Cleanup Task, and you install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance plans are not affected.

What is the issue?

Some maintenance plans might include the Maintenance Cleanup Task and the History Cleanup Task, which allow users to delete information older than a specified interval. When SQL Server 2005 was released, this interval was measured in days, weeks, months, or years. In response to customer feedback, SQL Server 2005 SP2 includes significant enhancements to maintenance plans, including an enhancement that allows users to specify the cleanup interval in hours. After upgrading to SP2, and until you update cleanup tasks using SQL Server SP2 tools as described below, existing cleanup task intervals are misinterpreted. This leads to earlier data cleanup than was intended.

What if I have already installed SP2 on my server?

You can restore your maintenance plans to their former behavior by opening them up in the Maintenance Plan Designer, opening any cleanup tasks, adjusting the age units to the proper value, and saving the maintenance plan. Use SQL Server 2005 SP2 tools to make these changes.

What if I haven't yet installed SP2 on my server?

We are currently investigating our approach to this problem and will provide guidance shortly. If you depend on the Maintenance Cleanup Task or the History Cleanup Task and are not able to verify and possibly update cleanup tasks using matching server and tool versions, you may want to wait for that guidance before installing SP2.

Users, also please make a note of README for SP2:

The following error message is returned if you use a tool from a previous version to open an SP2 maintenance plan created with the maintenance plan designer:

"This maintenance plan has been modified using a tool other than the maintenance plan designer. You must use SQL Server Integration Services to modify this maintenance plan from now on."

To resolve this issue, we recommend that you upgrade your tools to SQL Server 2005 SP2. We also recommend using the maintenance plan designer to modify the plan, rather than Integration Services as mentioned in the error message.

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Who is affected?

You are affected if you use SQL Server 2005 maintenance plans, those plans include a History Cleanup Task or a Maintenance Cleanup Task, and you install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance plans are not affected.

What is the issue?

Some maintenance plans might include the Maintenance Cleanup Task and the History Cleanup Task, which allow users to delete information older than a specified interval. When SQL Server 2005 was released, this interval was measured in days, weeks, months, or years. In response to customer feedback, SQL Server 2005 SP2 includes significant enhancements to maintenance plans, including an enhancement that allows users to specify the cleanup interval in hours. After upgrading to SP2, and until you update cleanup tasks using SQL Server SP2 tools as described below, existing cleanup task intervals are misinterpreted. This leads to earlier data cleanup than was intended.

What if I have already installed SP2 on my server?

You can restore your maintenance plans to their former behavior by opening them up in the Maintenance Plan Designer, opening any cleanup tasks, adjusting the age units to the proper value, and saving the maintenance plan. Use SQL Server 2005 SP2 tools to make these changes.

What if I haven't yet installed SP2 on my server?

We are currently investigating our approach to this problem and will provide guidance shortly. If you depend on the Maintenance Cleanup Task or the History Cleanup Task and are not able to verify and possibly update cleanup tasks using matching server and tool versions, you may want to wait for that guidance before installing SP2.

Users, also please make a note of README for SP2:

The following error message is returned if you use a tool from a previous version to open an SP2 maintenance plan created with the maintenance plan designer:

"This maintenance plan has been modified using a tool other than the maintenance plan designer. You must use SQL Server Integration Services to modify this maintenance plan from now on."

To resolve this issue, we recommend that you upgrade your tools to SQL Server 2005 SP2. We also recommend using the maintenance plan designer to modify the plan, rather than Integration Services as mentioned in the error message.

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in
SP2 that affects existing cleanup tasks until updated using SQL Server SP2
tools.
Who is affected?
You are affected if you use SQL Server 2005 maintenance plans, those plans
include a History Cleanup Task or a Maintenance Cleanup Task, and you
install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
plans are not affected.
What is the issue?
Some maintenance plans might include the Maintenance Cleanup Task and the
History Cleanup Task, which allow users to delete information older than a
specified interval. When SQL Server 2005 was released, this interval was
measured in days, weeks, months, or years. In response to customer feedback,
SQL Server 2005 SP2 includes significant enhancements to maintenance plans,
including an enhancement that allows users to specify the cleanup interval
in hours. After upgrading to SP2, and until you update cleanup tasks using
SQL Server SP2 tools as described below, existing cleanup task intervals are
misinterpreted. This leads to earlier data cleanup than was intended.
What if I have already installed SP2 on my server?
You can restore your maintenance plans to their former behavior by opening
them up in the Maintenance Plan Designer, opening any cleanup tasks,
adjusting the age units to the proper value, and saving the maintenance
plan. Use SQL Server 2005 SP2 tools to make these changes.
What if I haven't yet installed SP2 on my server?
We are currently investigating our approach to this problem and will provide
guidance shortly. If you depend on the Maintenance Cleanup Task or the
History Cleanup Task and are not able to verify and possibly update cleanup
tasks using matching server and tool versions, you may want to wait for that
guidance before installing SP2.
Ed,
Pre SP2 the maintenance plans created SSIS packages. Is this still the
behaviour in SP2?
Thanks
Chris
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uKlNt%23%23VHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>
|||Chris Wood (anonymous@.discussions.microsoft.com) writes:
> Pre SP2 the maintenance plans created SSIS packages. Is this still the
> behaviour in SP2?
I'm not into maintenance plans myself, but I believe that in SP2,
maintenance plans are part of the engine, and SSIS is not required.
There is a discussion on this in "What's New in SP2" in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Yes, SP2 Maintenance Plans still create SSIS packages. We do not plan on
changing this in the near future.
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%232F$oTeWHHA.1016@.TK2MSFTNGP04.phx.gbl...
> Ed,
> Pre SP2 the maintenance plans created SSIS packages. Is this still the
> behaviour in SP2?
> Thanks
> Chris
> "Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
> news:uKlNt%23%23VHHA.4668@.TK2MSFTNGP04.phx.gbl...
>
|||Hello,
I used method:
public void AddAgentJob(ServerConnection LocalConnObj, string proxyName)
(class
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan)
but after installing SP2 for MS SQL Server, I can't find it.
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uKlNt%23%23VHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>
sql

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in
SP2 that affects existing cleanup tasks until updated using SQL Server SP2
tools.
Who is affected?
You are affected if you use SQL Server 2005 maintenance plans, those plans
include a History Cleanup Task or a Maintenance Cleanup Task, and you
install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
plans are not affected.
What is the issue?
Some maintenance plans might include the Maintenance Cleanup Task and the
History Cleanup Task, which allow users to delete information older than a
specified interval. When SQL Server 2005 was released, this interval was
measured in days, weeks, months, or years. In response to customer feedback,
SQL Server 2005 SP2 includes significant enhancements to maintenance plans,
including an enhancement that allows users to specify the cleanup interval
in hours. After upgrading to SP2, and until you update cleanup tasks using
SQL Server SP2 tools as described below, existing cleanup task intervals are
misinterpreted. This leads to earlier data cleanup than was intended.
What if I have already installed SP2 on my server?
You can restore your maintenance plans to their former behavior by opening
them up in the Maintenance Plan Designer, opening any cleanup tasks,
adjusting the age units to the proper value, and saving the maintenance
plan. Use SQL Server 2005 SP2 tools to make these changes.
What if I haven't yet installed SP2 on my server?
We are currently investigating our approach to this problem and will provide
guidance shortly. If you depend on the Maintenance Cleanup Task or the
History Cleanup Task and are not able to verify and possibly update cleanup
tasks using matching server and tool versions, you may want to wait for that
guidance before installing SP2.
Wow, how many dba's will loose their jobs over that one? How did something
like that get past QA or even the developers of that feature?
Andrew J. Kelly SQL MVP
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uzdQ8B$VHHA.488@.TK2MSFTNGP06.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>
|||Ed,
Pre SP2 the maintenance plans created SSIS packages. Is this still the
behaviour in SP2?
Thanks
Chris
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uzdQ8B$VHHA.488@.TK2MSFTNGP06.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>

Maintenance Plan Changes in SQL Server 2005 SP2

Users of SQL Server 2005 maintenance plans should be aware of a change in
SP2 that affects existing cleanup tasks until updated using SQL Server SP2
tools.
Who is affected?
You are affected if you use SQL Server 2005 maintenance plans, those plans
include a History Cleanup Task or a Maintenance Cleanup Task, and you
install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
plans are not affected.
What is the issue?
Some maintenance plans might include the Maintenance Cleanup Task and the
History Cleanup Task, which allow users to delete information older than a
specified interval. When SQL Server 2005 was released, this interval was
measured in days, weeks, months, or years. In response to customer feedback,
SQL Server 2005 SP2 includes significant enhancements to maintenance plans,
including an enhancement that allows users to specify the cleanup interval
in hours. After upgrading to SP2, and until you update cleanup tasks using
SQL Server SP2 tools as described below, existing cleanup task intervals are
misinterpreted. This leads to earlier data cleanup than was intended.
What if I have already installed SP2 on my server?
You can restore your maintenance plans to their former behavior by opening
them up in the Maintenance Plan Designer, opening any cleanup tasks,
adjusting the age units to the proper value, and saving the maintenance
plan. Use SQL Server 2005 SP2 tools to make these changes.
What if I haven't yet installed SP2 on my server?
We are currently investigating our approach to this problem and will provide
guidance shortly. If you depend on the Maintenance Cleanup Task or the
History Cleanup Task and are not able to verify and possibly update cleanup
tasks using matching server and tool versions, you may want to wait for that
guidance before installing SP2.Wow, how many dba's will loose their jobs over that one? How did something
like that get past QA or even the developers of that feature?
--
Andrew J. Kelly SQL MVP
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uzdQ8B$VHHA.488@.TK2MSFTNGP06.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>|||Ed,
Pre SP2 the maintenance plans created SSIS packages. Is this still the
behaviour in SP2?
Thanks
Chris
"Ed Lehman [MSFT]" <edle@.online.microsoft.com> wrote in message
news:uzdQ8B$VHHA.488@.TK2MSFTNGP06.phx.gbl...
> Users of SQL Server 2005 maintenance plans should be aware of a change in
> SP2 that affects existing cleanup tasks until updated using SQL Server SP2
> tools.
> Who is affected?
> You are affected if you use SQL Server 2005 maintenance plans, those plans
> include a History Cleanup Task or a Maintenance Cleanup Task, and you
> install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance
> plans are not affected.
> What is the issue?
> Some maintenance plans might include the Maintenance Cleanup Task and the
> History Cleanup Task, which allow users to delete information older than a
> specified interval. When SQL Server 2005 was released, this interval was
> measured in days, weeks, months, or years. In response to customer
> feedback, SQL Server 2005 SP2 includes significant enhancements to
> maintenance plans, including an enhancement that allows users to specify
> the cleanup interval in hours. After upgrading to SP2, and until you
> update cleanup tasks using SQL Server SP2 tools as described below,
> existing cleanup task intervals are misinterpreted. This leads to earlier
> data cleanup than was intended.
> What if I have already installed SP2 on my server?
> You can restore your maintenance plans to their former behavior by opening
> them up in the Maintenance Plan Designer, opening any cleanup tasks,
> adjusting the age units to the proper value, and saving the maintenance
> plan. Use SQL Server 2005 SP2 tools to make these changes.
> What if I haven't yet installed SP2 on my server?
> We are currently investigating our approach to this problem and will
> provide guidance shortly. If you depend on the Maintenance Cleanup Task or
> the History Cleanup Task and are not able to verify and possibly update
> cleanup tasks using matching server and tool versions, you may want to
> wait for that guidance before installing SP2.
>

Maintenance Job failure

I'm getting the following error when my database is
integrity checked. Error 5070: Database state cannot be
changed while other users are using database.
Is there anyway to tell who or what is using the database?Yes, you can run sp_who to see who is in the database currently.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx
.gbl...
I'm getting the following error when my database is
integrity checked. Error 5070: Database state cannot be
changed while other users are using database.
Is there anyway to tell who or what is using the database?|||Run sp_who2 from within Query Analyzer, to see who are accessing the
database you are checking.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx
.gbl...
> I'm getting the following error when my database is
> integrity checked. Error 5070: Database state cannot be
> changed while other users are using database.
> Is there anyway to tell who or what is using the database?|||You should not run the automatic repair option as part of checking
consistency (this option has been removed in SQL Server 2005). Firstly it
requires changing the database state to single_user mode and secondly you
should always do root cause analysis of any inconsistencies.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx
.gbl...
> I'm getting the following error when my database is
> integrity checked. Error 5070: Database state cannot be
> changed while other users are using database.
> Is there anyway to tell who or what is using the database?

Maintenance Job failure

I'm getting the following error when my database is
integrity checked. Error 5070: Database state cannot be
changed while other users are using database.
Is there anyway to tell who or what is using the database?
Yes, you can run sp_who to see who is in the database currently.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
I'm getting the following error when my database is
integrity checked. Error 5070: Database state cannot be
changed while other users are using database.
Is there anyway to tell who or what is using the database?
|||Yes, you can run sp_who to see who is in the database currently.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
I'm getting the following error when my database is
integrity checked. Error 5070: Database state cannot be
changed while other users are using database.
Is there anyway to tell who or what is using the database?
|||Run sp_who2 from within Query Analyzer, to see who are accessing the
database you are checking.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
> I'm getting the following error when my database is
> integrity checked. Error 5070: Database state cannot be
> changed while other users are using database.
> Is there anyway to tell who or what is using the database?
|||Run sp_who2 from within Query Analyzer, to see who are accessing the
database you are checking.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
> I'm getting the following error when my database is
> integrity checked. Error 5070: Database state cannot be
> changed while other users are using database.
> Is there anyway to tell who or what is using the database?
|||You should not run the automatic repair option as part of checking
consistency (this option has been removed in SQL Server 2005). Firstly it
requires changing the database state to single_user mode and secondly you
should always do root cause analysis of any inconsistencies.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
> I'm getting the following error when my database is
> integrity checked. Error 5070: Database state cannot be
> changed while other users are using database.
> Is there anyway to tell who or what is using the database?
|||You should not run the automatic repair option as part of checking
consistency (this option has been removed in SQL Server 2005). Firstly it
requires changing the database state to single_user mode and secondly you
should always do root cause analysis of any inconsistencies.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
> I'm getting the following error when my database is
> integrity checked. Error 5070: Database state cannot be
> changed while other users are using database.
> Is there anyway to tell who or what is using the database?
sql

Maintenance Job failure

I'm getting the following error when my database is
integrity checked. Error 5070: Database state cannot be
changed while other users are using database.
Is there anyway to tell who or what is using the database?Yes, you can run sp_who to see who is in the database currently.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
I'm getting the following error when my database is
integrity checked. Error 5070: Database state cannot be
changed while other users are using database.
Is there anyway to tell who or what is using the database?|||Run sp_who2 from within Query Analyzer, to see who are accessing the
database you are checking.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
> I'm getting the following error when my database is
> integrity checked. Error 5070: Database state cannot be
> changed while other users are using database.
> Is there anyway to tell who or what is using the database?|||You should not run the automatic repair option as part of checking
consistency (this option has been removed in SQL Server 2005). Firstly it
requires changing the database state to single_user mode and secondly you
should always do root cause analysis of any inconsistencies.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
> I'm getting the following error when my database is
> integrity checked. Error 5070: Database state cannot be
> changed while other users are using database.
> Is there anyway to tell who or what is using the database?

Maintaining user formatting in multiline textbox

In a multiline textbox, how do you maintain paragraph breaks that users may create (like what you see on this forum)?

Are the returns replaced by something else just before being saved to the database? Any assistance would be appreciated!

MichaelWhat I have done with classic ASP is replace all vbCRLF with <BR>. I believe it is the same thing with ASP.NET.

Terri

Maintaining a log for the users connected to sql server db.

Hi !

I need to maintain a record such as how many time any user (e.g, sa) connects to the sql server. Means whenever any person is connecting to the database through application or directly, then i need to know that through which sql user(e.g sa), any body connected.

Regards,
Shabber Abbas Rizvi.create server side trace file for security Audit events.For more information visit

http://search.atomz.com/search/?sp-q=trace&sp-a=sp10010351

Madness?!? This is SQL!

I'm charged with building a web service that accepts data from
multiple locations and stores it to be queried by users. The data we
receive will vary from business unit to business unit and we intend to
add more units as the project progresses. Each unit's system we add
will return data in a different format, and I really don't want to try
and replicate all of their databases.
Someone tell me whether the way I have this planned is a good way of
doing it, or if it's pure madness.
Lets say I get data from 2 different business units as follows:
Unit 1 Data:
<employees>
<employee id="1">
<name>Doe, Jane</name>
<phone>999-999-9999</phone>
</employee>
<employee>
<name>Doe, John</name>
<phone>888-888-8888</phone>
</employee>
</employees>
Unit 2 Data:
<systems>
<system id="123" name="wrk001">
<ip>1.1.1.1</ip>
<os>WindowsXP</os>
<location>D105</location>
</system>
<system id="234" name="wrk002">
<ip>1.1.1.2</ip>
<os>WindowsXP</os>
<location>D106</location>
</system>
</systems>
When a user needs to look up data from unit 1, they'll need to be able
to supply the employee's name and or id, while users searching unit 2
will need to know an ip address and or location name.
First, lets say I have a System Table:
TBL_System
---
| System_ID | System_Name
+--+--
| 1 | Business Unit 1
+--+--
| 2 | Business Unit 2
+--+--
Then, I create a table that explains what keys users will be searching
for. These keys will be based off of the XML document structure.
---
| System_ID | System_Key | Key_Name
+--+--+--
| 1 | 1 | id
+--+--+--
| 1 | 2 | name
+--+--+--
| 2 | 1 | location
+--+--+--
| 2 | 2 | ip
+--+--+--
Then, lets say I create a Table called TBL_DataStore to hold this
info.
TBL_DataStore
---
| DS_ID | DS_Data
+--+--
| 1 | <employee id="z1">
| | <name>Doe, Jane</name>
| | <phone>999-999-9999</phone>
| | </employee>
+--+--
| 2 | <employee id="z2">
| | <name>Doe, John</name>
| | <phone>888-888-8888</phone>
| | </employee>
+--+--
| 3 | <system id="123" name="wrk001">
| | <ip>1.1.1.1</ip>
| | <os>WindowsXP</os>
| | <location>D105</location>
| | </system>
+--+--
| 4 | <system id="234" name="wrk002">
| | <ip>1.1.1.2</ip>
| | <os>WindowsXP</os>
| | <location>D106</location>
| | </system>
+--+--
Finally, I create a Lookup table that holds key values that the user
will be searching for
TBL_Lookup
---
| System_ID | System_Key | DS_ID | Key_Value
+--+--+--+--
| 1 | 1 | 1 | z1
+--+--+--+--
| 1 | 2 | 1 | Doe, Jane
+--+--+--+--
| 1 | 1 | 2 | z2
+--+--+--+--
| 1 | 2 | 2 | Doe, John
+--+--+--+--
| 2 | 1 | 3 | D105
+--+--+--+--
| 2 | 2 | 3 | 1.1.1.1
+--+--+--+--
| 2 | 1 | 4 | D106
+--+--+--+--
| 2 | 2 | 4 | 1.1.1.2
+--+--+--+--
Now, based on this structure, I can load data from any business unit
without having to change the data structure to add more businesses.
Further, In order to query the data, I don't have to know the specific
xPath of a piece of information.
Is this a good way to accomplish what I'm trying to achieve, or should
I figure out some way to use OpenXML() queries based on XML supplied
by the different business units. My concern about OpenXML() is it's
speed and flexability.Hello Kris,
What volumes are you dealing with?
My view is the effort in creating a table far out ways the effort to support
a system like the one you are suggesting.
One simple view is to use full text, which allows full text to filter down
in a rough manner and then filter further using normal search predicates.
i.e. system and/or an xpath query
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> I'm charged with building a web service that accepts data from
> multiple locations and stores it to be queried by users. The data we
> receive will vary from business unit to business unit and we intend to
> add more units as the project progresses. Each unit's system we add
> will return data in a different format, and I really don't want to try
> and replicate all of their databases.
> Someone tell me whether the way I have this planned is a good way of
> doing it, or if it's pure madness.
> Lets say I get data from 2 different business units as follows:
> Unit 1 Data:
> <employees>
> <employee id="1">
> <name>Doe, Jane</name>
> <phone>999-999-9999</phone>
> </employee>
> <employee>
> <name>Doe, John</name>
> <phone>888-888-8888</phone>
> </employee>
> </employees>
> Unit 2 Data:
> <systems>
> <system id="123" name="wrk001">
> <ip>1.1.1.1</ip>
> <os>WindowsXP</os>
> <location>D105</location>
> </system>
> <system id="234" name="wrk002">
> <ip>1.1.1.2</ip>
> <os>WindowsXP</os>
> <location>D106</location>
> </system>
> </systems>
> When a user needs to look up data from unit 1, they'll need to be able
> to supply the employee's name and or id, while users searching unit 2
> will need to know an ip address and or location name.
> First, lets say I have a System Table:
> TBL_System
> ---
> | System_ID | System_Name
> +--+--
> | 1 | Business Unit 1
> +--+--
> | 2 | Business Unit 2
> +--+--
> Then, I create a table that explains what keys users will be searching
> for. These keys will be based off of the XML document structure.
> ---
> | System_ID | System_Key | Key_Name
> +--+--+--
> | 1 | 1 | id
> +--+--+--
> | 1 | 2 | name
> +--+--+--
> | 2 | 1 | location
> +--+--+--
> | 2 | 2 | ip
> +--+--+--
> Then, lets say I create a Table called TBL_DataStore to hold this
> info.
> TBL_DataStore
> ---
> | DS_ID | DS_Data
> +--+--
> | 1 | <employee id="z1">
> | | <name>Doe, Jane</name>
> | | <phone>999-999-9999</phone>
> | | </employee>
> +--+--
> | 2 | <employee id="z2">
> | | <name>Doe, John</name>
> | | <phone>888-888-8888</phone>
> | | </employee>
> +--+--
> | 3 | <system id="123" name="wrk001">
> | | <ip>1.1.1.1</ip>
> | | <os>WindowsXP</os>
> | | <location>D105</location>
> | | </system>
> +--+--
> | 4 | <system id="234" name="wrk002">
> | | <ip>1.1.1.2</ip>
> | | <os>WindowsXP</os>
> | | <location>D106</location>
> | | </system>
> +--+--
> Finally, I create a Lookup table that holds key values that the user
> will be searching for
> TBL_Lookup
> ---
> | System_ID | System_Key | DS_ID | Key_Value
> +--+--+--+--
> | 1 | 1 | 1 | z1
> +--+--+--+--
> | 1 | 2 | 1 | Doe, Jane
> +--+--+--+--
> | 1 | 1 | 2 | z2
> +--+--+--+--
> | 1 | 2 | 2 | Doe, John
> +--+--+--+--
> | 2 | 1 | 3 | D105
> +--+--+--+--
> | 2 | 2 | 3 | 1.1.1.1
> +--+--+--+--
> | 2 | 1 | 4 | D106
> +--+--+--+--
> | 2 | 2 | 4 | 1.1.1.2
> +--+--+--+--
> Now, based on this structure, I can load data from any business unit
> without having to change the data structure to add more businesses.
> Further, In order to query the data, I don't have to know the specific
> xPath of a piece of information.
> Is this a good way to accomplish what I'm trying to achieve, or should
> I figure out some way to use OpenXML() queries based on XML supplied
> by the different business units. My concern about OpenXML() is it's
> speed and flexability.
>

Madness?!? This is SQL!

I'm charged with building a web service that accepts data from
multiple locations and stores it to be queried by users. The data we
receive will vary from business unit to business unit and we intend to
add more units as the project progresses. Each unit's system we add
will return data in a different format, and I really don't want to try
and replicate all of their databases.
Someone tell me whether the way I have this planned is a good way of
doing it, or if it's pure madness.
Lets say I get data from 2 different business units as follows:
Unit 1 Data:
<employees>
<employee id="1">
<name>Doe, Jane</name>
<phone>999-999-9999</phone>
</employee>
<employee>
<name>Doe, John</name>
<phone>888-888-8888</phone>
</employee>
</employees>
Unit 2 Data:
<systems>
<system id="123" name="wrk001">
<ip>1.1.1.1</ip>
<os>WindowsXP</os>
<location>D105</location>
</system>
<system id="234" name="wrk002">
<ip>1.1.1.2</ip>
<os>WindowsXP</os>
<location>D106</location>
</system>
</systems>
When a user needs to look up data from unit 1, they'll need to be able
to supply the employee's name and or id, while users searching unit 2
will need to know an ip address and or location name.
First, lets say I have a System Table:
TBL_System
| System_ID | System_Name
+--+--
| 1 | Business Unit 1
+--+--
| 2 | Business Unit 2
+--+--
Then, I create a table that explains what keys users will be searching
for. These keys will be based off of the XML document structure.
| System_ID | System_Key | Key_Name
+--+--+--
| 1 | 1 | id
+--+--+--
| 1 | 2 | name
+--+--+--
| 2 | 1 | location
+--+--+--
| 2 | 2 | ip
+--+--+--
Then, lets say I create a Table called TBL_DataStore to hold this
info.
TBL_DataStore
| DS_ID | DS_Data
+--+--
| 1 | <employee id="z1">
| | <name>Doe, Jane</name>
| |<phone>999-999-9999</phone>
|| </employee>
+--+--
| 2 | <employee id="z2">
| | <name>Doe, John</name>
| | <phone>888-888-8888</phone>
| | </employee>
+--+--
| 3 | <system id="123" name="wrk001">
| | <ip>1.1.1.1</ip>
| | <os>WindowsXP</os>
| | <location>D105</location>
| | </system>
+--+--
| 4 | <system id="234" name="wrk002">
| | <ip>1.1.1.2</ip>
| | <os>WindowsXP</os>
| | <location>D106</location>
| | </system>
+--+--
Finally, I create a Lookup table that holds key values that the user
will be searching for
TBL_Lookup
| System_ID | System_Key | DS_ID | Key_Value
+--+--+--+--
| 1 | 1 | 1 | z1
+--+--+--+--
| 1 | 2 | 1 | Doe, Jane
+--+--+--+--
| 1 | 1 | 2 | z2
+--+--+--+--
| 1 | 2 | 2 | Doe, John
+--+--+--+--
| 2 | 1 | 3 | D105
+--+--+--+--
| 2 | 2 | 3 | 1.1.1.1
+--+--+--+--
| 2 | 1 | 4 | D106
+--+--+--+--
| 2 | 2 | 4 | 1.1.1.2
+--+--+--+--
Now, based on this structure, I can load data from any business unit
without having to change the data structure to add more businesses.
Further, In order to query the data, I don't have to know the specific
xPath of a piece of information.
Is this a good way to accomplish what I'm trying to achieve, or should
I figure out some way to use OpenXML() queries based on XML supplied
by the different business units. My concern about OpenXML() is it's
speed and flexability.
Hello Kris,
What volumes are you dealing with?
My view is the effort in creating a table far out ways the effort to support
a system like the one you are suggesting.
One simple view is to use full text, which allows full text to filter down
in a rough manner and then filter further using normal search predicates.
i.e. system and/or an xpath query
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> I'm charged with building a web service that accepts data from
> multiple locations and stores it to be queried by users. The data we
> receive will vary from business unit to business unit and we intend to
> add more units as the project progresses. Each unit's system we add
> will return data in a different format, and I really don't want to try
> and replicate all of their databases.
> Someone tell me whether the way I have this planned is a good way of
> doing it, or if it's pure madness.
> Lets say I get data from 2 different business units as follows:
> Unit 1 Data:
> <employees>
> <employee id="1">
> <name>Doe, Jane</name>
> <phone>999-999-9999</phone>
> </employee>
> <employee>
> <name>Doe, John</name>
> <phone>888-888-8888</phone>
> </employee>
> </employees>
> Unit 2 Data:
> <systems>
> <system id="123" name="wrk001">
> <ip>1.1.1.1</ip>
> <os>WindowsXP</os>
> <location>D105</location>
> </system>
> <system id="234" name="wrk002">
> <ip>1.1.1.2</ip>
> <os>WindowsXP</os>
> <location>D106</location>
> </system>
> </systems>
> When a user needs to look up data from unit 1, they'll need to be able
> to supply the employee's name and or id, while users searching unit 2
> will need to know an ip address and or location name.
> First, lets say I have a System Table:
> TBL_System
> | System_ID | System_Name
> +--+--
> | 1 | Business Unit 1
> +--+--
> | 2 | Business Unit 2
> +--+--
> Then, I create a table that explains what keys users will be searching
> for. These keys will be based off of the XML document structure.
> | System_ID | System_Key | Key_Name
> +--+--+--
> | 1 | 1 | id
> +--+--+--
> | 1 | 2 | name
> +--+--+--
> | 2 | 1 | location
> +--+--+--
> | 2 | 2 | ip
> +--+--+--
> Then, lets say I create a Table called TBL_DataStore to hold this
> info.
> TBL_DataStore
> | DS_ID | DS_Data
> +--+--
> | 1 | <employee id="z1">
> | | <name>Doe, Jane</name>
> | |<phone>999-999-9999</phone>
> || </employee>
> +--+--
> | 2 | <employee id="z2">
> | | <name>Doe, John</name>
> | | <phone>888-888-8888</phone>
> | | </employee>
> +--+--
> | 3 | <system id="123" name="wrk001">
> | | <ip>1.1.1.1</ip>
> | | <os>WindowsXP</os>
> | | <location>D105</location>
> | | </system>
> +--+--
> | 4 | <system id="234" name="wrk002">
> | | <ip>1.1.1.2</ip>
> | | <os>WindowsXP</os>
> | | <location>D106</location>
> | | </system>
> +--+--
> Finally, I create a Lookup table that holds key values that the user
> will be searching for
> TBL_Lookup
> | System_ID | System_Key | DS_ID | Key_Value
> +--+--+--+--
> | 1 | 1 | 1 | z1
> +--+--+--+--
> | 1 | 2 | 1 | Doe, Jane
> +--+--+--+--
> | 1 | 1 | 2 | z2
> +--+--+--+--
> | 1 | 2 | 2 | Doe, John
> +--+--+--+--
> | 2 | 1 | 3 | D105
> +--+--+--+--
> | 2 | 2 | 3 | 1.1.1.1
> +--+--+--+--
> | 2 | 1 | 4 | D106
> +--+--+--+--
> | 2 | 2 | 4 | 1.1.1.2
> +--+--+--+--
> Now, based on this structure, I can load data from any business unit
> without having to change the data structure to add more businesses.
> Further, In order to query the data, I don't have to know the specific
> xPath of a piece of information.
> Is this a good way to accomplish what I'm trying to achieve, or should
> I figure out some way to use OpenXML() queries based on XML supplied
> by the different business units. My concern about OpenXML() is it's
> speed and flexability.
>

Mac users

We are having issues with Mac users accessing our RS reports.
When they use Safari RS refuses their connection and when they use IE 5 they
are prompted for User/Password/Doman (which is OK) but the drop downs
(prompts) aren't showing up.
Is there a solution?
ThanksGive them a PC! :)
Sorry, couldn't resist.
Alex wrote:
> We are having issues with Mac users accessing our RS reports.
> When they use Safari RS refuses their connection and when they use IE
> 5 they are prompted for User/Password/Doman (which is OK) but the
> drop downs (prompts) aren't showing up.
> Is there a solution?
> Thanks|||For a more serious reply, I did some cross-browser testing recently,
both mac and pc and got really poor results. Some of the worst problems
were IE on Mac!
I posted on a microsoft forum somewhere but got no response.
Chris
Chris McGuigan wrote:
> Give them a PC! :)
> Sorry, couldn't resist.
>
> Alex wrote:
> > We are having issues with Mac users accessing our RS reports.
> > When they use Safari RS refuses their connection and when they use
> > IE 5 they are prompted for User/Password/Doman (which is OK) but the
> > drop downs (prompts) aren't showing up.
> >
> > Is there a solution?
> >
> > Thanks|||You want me to be lynched don't you
"Chris McGuigan" wrote:
> Give them a PC! :)
> Sorry, couldn't resist.
>
> Alex wrote:
> > We are having issues with Mac users accessing our RS reports.
> > When they use Safari RS refuses their connection and when they use IE
> > 5 they are prompted for User/Password/Doman (which is OK) but the
> > drop downs (prompts) aren't showing up.
> >
> > Is there a solution?
> >
> > Thanks
>|||I've had success with 1 user being able to get on RS and having the drop
downs show up and work.
They were even able to run reports.
The only hick right now is that when they choose a format to export the
"Export" button never gets activated.
Any idead, anyone
"Chris McGuigan" wrote:
> For a more serious reply, I did some cross-browser testing recently,
> both mac and pc and got really poor results. Some of the worst problems
> were IE on Mac!
> I posted on a microsoft forum somewhere but got no response.
> Chris
>
> Chris McGuigan wrote:
> > Give them a PC! :)
> >
> > Sorry, couldn't resist.
> >
> >
> > Alex wrote:
> >
> > > We are having issues with Mac users accessing our RS reports.
> > > When they use Safari RS refuses their connection and when they use
> > > IE 5 they are prompted for User/Password/Doman (which is OK) but the
> > > drop downs (prompts) aren't showing up.
> > >
> > > Is there a solution?
> > >
> > > Thanks
>