Error when running optimizer maintenance plan

Hi
I have 140GB data base with maintenance plan schedule
every sunday. when it run Optimizer plan
I get the error and the job failed if I rerun the job
some time it will completed but 1 out of 4 time it will
failed on
middle of reindex with the error [Microsoft SQL-DMO (ODBC
SQLState: 01000)] Error 0: This server has been
disconnected.
Have any one known what caused this problem?
Thank in advanceThe message indicates that SQL Server became unavailable to the client
connection. Check your SQL error log for any exceptions of access
violations. Also check to see if someone manually shutdown SQL Server.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Error when rebuilding indexes

Hi
I have created a database maintenance plan which includes rebuilding indexes
and checkdb. For one of the databases I get the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'.
Does anyone know how I can resolve this?
TIA!
Hi
Have a look at the following:
http://support.microsoft.com/default...b;en-us;301292
http://support.microsoft.com/default...b;en-us;902388
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"N" wrote:

> Hi
> I have created a database maintenance plan which includes rebuilding indexes
> and checkdb. For one of the databases I get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> Server Driver][SQL Server]DBCC failed because the following SET options have
> incorrect settings: 'QUOTED_IDENTIFIER'.
> Does anyone know how I can resolve this?
> TIA!
>
sql

Error when rebuilding indexes

Hi
I have created a database maintenance plan which includes rebuilding indexes
and checkdb. For one of the databases I get the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options
have
incorrect settings: 'QUOTED_IDENTIFIER'.
Does anyone know how I can resolve this?
TIA!Hi
Have a look at the following:
http://support.microsoft.com/defaul...kb;en-us;301292
http://support.microsoft.com/defaul...kb;en-us;902388
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"N" wrote:

> Hi
> I have created a database maintenance plan which includes rebuilding index
es
> and checkdb. For one of the databases I get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]DBCC failed because the following SET option
s have
> incorrect settings: 'QUOTED_IDENTIFIER'.
> Does anyone know how I can resolve this?
> TIA!
>

Error when rebuilding indexes

Hi
I have created a database maintenance plan which includes rebuilding indexes
and checkdb. For one of the databases I get the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'.
Does anyone know how I can resolve this?
TIA!Hi
Have a look at the following:
http://support.microsoft.com/default.aspx?scid=kb;en-us;301292
http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"N" wrote:
> Hi
> I have created a database maintenance plan which includes rebuilding indexes
> and checkdb. For one of the databases I get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> Server Driver][SQL Server]DBCC failed because the following SET options have
> incorrect settings: 'QUOTED_IDENTIFIER'.
> Does anyone know how I can resolve this?
> TIA!
>

Error when rebuild table index

I ran maintenance plan trying to rebuild index for the database tables, and I
got following error.
[22] Database CmpData: Index Rebuild (leaving 10%% free space)...
Rebuilding indexes for table 'cliStores'
Rebuilding indexes for table 'cmpItems'
Rebuilding indexes for table 'cmpStores'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
I run following set command.
SET ANSI_DEFAULTS ON
But error remains. Please help. Thanks,
Charts
Sounds like you have a computed column in the table. The maintenance wizard
can not properly handle those. If you have SP4 then try this:
http://support.microsoft.com/?kbid=902388
Otherwise the solution is to create your own scheduled job that issues a
DBCC DBREINDEX for that table (or all of them) and set the proper SET
settings there.
Andrew J. Kelly SQL MVP
"Charts" <Acharts@.newsgroup.nospam> wrote in message
news:6752D209-A390-44C7-95EC-28B2B6BBF135@.microsoft.com...
>I ran maintenance plan trying to rebuild index for the database tables, and
>I
> got following error.
> [22] Database CmpData: Index Rebuild (leaving 10%% free space)...
> Rebuilding indexes for table 'cliStores'
> Rebuilding indexes for table 'cmpItems'
> Rebuilding indexes for table 'cmpStores'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
> have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> I run following set command.
> SET ANSI_DEFAULTS ON
> But error remains. Please help. Thanks,
> Charts
>
|||Hi Andrew,
You are right, and The table has a computed column. Since my SQL Server is
SP4. I followed the instruction in the article and added -CkDB
-SupportComputedColumn' in the job command. The job command is as follows.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
AC13D3D2-F803-49E4-A562-2B4C25B7C77D -Rpt "C:\Program Files\Microsoft SQL
Server\myservername\LOG\DB Maintenance Plan-correction0.txt" -DelTxtRpt
4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 -CkDB
-SupportComputedColumn'
Now I got another error, and says.
Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC
SQLState: 08001)] Error 0: [Microsoft][ODBC SQL Server Driver][Shared
Memory]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
The original command is as follows.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
AC13D3D2-F803-49E4-A562-2B4C25B7C77D -Rpt "C:\Program Files\Microsoft SQL
Server\ myservername \LOG\DB Maintenance Plan-correction0.txt" -DelTxtRpt
4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '
By the way, is there way to directly add SupportComputedColumn' parameter in
the property of maintenance plan wizard instead of command line?
Thanks for your help.
Charts
"Andrew J. Kelly" wrote:

> Sounds like you have a computed column in the table. The maintenance wizard
> can not properly handle those. If you have SP4 then try this:
> http://support.microsoft.com/?kbid=902388
> Otherwise the solution is to create your own scheduled job that issues a
> DBCC DBREINDEX for that table (or all of them) and set the proper SET
> settings there.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Charts" <Acharts@.newsgroup.nospam> wrote in message
> news:6752D209-A390-44C7-95EC-28B2B6BBF135@.microsoft.com...
>
>
|||I really don't know why you are getting the new error. There is no way to
embed it that I know of. Personally I never recommend the MP wizard. My
recommendation is to simply create your own job that does the reindexing the
way you need it.
Andrew J. Kelly SQL MVP
"Charts" <Acharts@.newsgroup.nospam> wrote in message
news:3E936E12-AA09-4880-BEB1-8F0CA5BE613B@.microsoft.com...[vbcol=seagreen]
> Hi Andrew,
> You are right, and The table has a computed column. Since my SQL Server is
> SP4. I followed the instruction in the article and added -CkDB
> -SupportComputedColumn' in the job command. The job command is as
> follows.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> AC13D3D2-F803-49E4-A562-2B4C25B7C77D -Rpt "C:\Program Files\Microsoft SQL
> Server\myservername\LOG\DB Maintenance Plan-correction0.txt" -DelTxtRpt
> 4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 -CkDB
> -SupportComputedColumn'
> Now I got another error, and says.
> Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC
> SQLState: 08001)] Error 0: [Microsoft][ODBC SQL Server Driver][Shared
> Memory]SQL Server does not exist or access denied.
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
> (Connect()).
> The original command is as follows.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> AC13D3D2-F803-49E4-A562-2B4C25B7C77D -Rpt "C:\Program Files\Microsoft SQL
> Server\ myservername \LOG\DB Maintenance Plan-correction0.txt" -DelTxtRpt
> 4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '
> By the way, is there way to directly add SupportComputedColumn' parameter
> in
> the property of maintenance plan wizard instead of command line?
> Thanks for your help.
> Charts
>
> "Andrew J. Kelly" wrote:
|||Charts,
I have the EXACT same problem.. computed column and use of
-SupportComputedColumn is causing ODBC connection issues. Have you
found any resolution? I have been unable to find any help anywhere
including BOL, MSKB etc...
Thanks in advance.
MJM
Charts wrote:[vbcol=seagreen]
> Hi Andrew,
> You are right, and The table has a computed column. Since my SQL Server is
> SP4. I followed the instruction in the article and added -CkDB
> -SupportComputedColumn' in the job command. The job command is as follows.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> AC13D3D2-F803-49E4-A562-2B4C25B7C77D -Rpt "C:\Program Files\Microsoft SQL
> Server\myservername\LOG\DB Maintenance Plan-correction0.txt" -DelTxtRpt
> 4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 -CkDB
> -SupportComputedColumn'
> Now I got another error, and says.
> Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC
> SQLState: 08001)] Error 0: [Microsoft][ODBC SQL Server Driver][Shared
> Memory]SQL Server does not exist or access denied.
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
> The original command is as follows.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> AC13D3D2-F803-49E4-A562-2B4C25B7C77D -Rpt "C:\Program Files\Microsoft SQL
> Server\ myservername \LOG\DB Maintenance Plan-correction0.txt" -DelTxtRpt
> 4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '
> By the way, is there way to directly add SupportComputedColumn' parameter in
> the property of maintenance plan wizard instead of command line?
> Thanks for your help.
> Charts
>
> "Andrew J. Kelly" wrote:
|||I have found that the SP4 option -SupportComputedColumn only works on a
default instance not a named instance. I'm contacting MS to see if
that can be supported.
Andrew J. Kelly wrote:[vbcol=seagreen]
> I really don't know why you are getting the new error. There is no way to
> embed it that I know of. Personally I never recommend the MP wizard. My
> recommendation is to simply create your own job that does the reindexing the
> way you need it.
> --
> Andrew J. Kelly SQL MVP
>
> "Charts" <Acharts@.newsgroup.nospam> wrote in message
> news:3E936E12-AA09-4880-BEB1-8F0CA5BE613B@.microsoft.com...

Error when opening Maintenance Plan in SQL 2000

I've recently installed SQL 2000 on a new server. Migrated production
databases and data from old SQL 6.5.
Everything checks out OK applications work fine, all data
came over no problem. However... when I try to open or create a
database maintenance plan I get is error:
Microsoft SQL-DMO (ODBC SQLState: 42S22)
Error 207: Invalid column name 'category_id'.
Invalid column name 'netsend_address'.
Invalid column name 'last_netsend_date'.
Invalid column name 'last_netsend_time'.
these colune names do not exist, do I need to create them and if so,
which table do they need to be in? Any help is greatly appreciated. Thanks
HI;
Columns mentioned below are in the sysoperators table in msdb database
netsend_address nvarchar 100 NOT NULL
last_netsend_date int 4 NULL
last_netsend_time int 4 NULL
category_id int 4 NULL
IT Looks to me that something happend to your msdb database.
Do you have a backup to restore it from before migration?
Danijel Novak
"Lisa Hoffman" <LisaHoffman@.reish.com> wrote in message
news:OH0vej2BGHA.3584@.TK2MSFTNGP11.phx.gbl...
> I've recently installed SQL 2000 on a new server. Migrated production
> databases and data from old SQL 6.5.
> Everything checks out OK applications work fine, all data
> came over no problem. However... when I try to open or create a
> database maintenance plan I get is error:
> Microsoft SQL-DMO (ODBC SQLState: 42S22)
> Error 207: Invalid column name 'category_id'.
> Invalid column name 'netsend_address'.
> Invalid column name 'last_netsend_date'.
> Invalid column name 'last_netsend_time'.
> these colune names do not exist, do I need to create them and if so, which
> table do they need to be in? Any help is greatly appreciated. Thanks
>
|||Hi Lisa
You may want to run a DBCC CHECKCATALOG or DBCC CHECKDB on your MSDB database.
If you need to restore MSDB check out
http://msdn.microsoft.com/library/de...kprst_2w1f.asp
John
"Lisa Hoffman" wrote:

> I've recently installed SQL 2000 on a new server. Migrated production
> databases and data from old SQL 6.5.
> Everything checks out OK applications work fine, all data
> came over no problem. However... when I try to open or create a
> database maintenance plan I get is error:
> Microsoft SQL-DMO (ODBC SQLState: 42S22)
> Error 207: Invalid column name 'category_id'.
> Invalid column name 'netsend_address'.
> Invalid column name 'last_netsend_date'.
> Invalid column name 'last_netsend_time'.
> these colune names do not exist, do I need to create them and if so,
> which table do they need to be in? Any help is greatly appreciated. Thanks
>
>
|||The MS article states "The model and msdb databases can only be restored
from backups created on a Microsoft? SQL Server? 2000 server. Restore of
backups of these databases made on SQL Server version 7.0 or earlier is
not supported."
This was a migration from 6.5 (and I do not have access to this db
anymore). Any other suggestions?
John Bell wrote:
[vbcol=seagreen]
> Hi Lisa
> You may want to run a DBCC CHECKCATALOG or DBCC CHECKDB on your MSDB database.
> If you need to restore MSDB check out
> http://msdn.microsoft.com/library/de...kprst_2w1f.asp
> John
> "Lisa Hoffman" wrote:
>
|||Hi
I would assume that MSDB was backed up after your installation and therefore
it could be resumed to it's previous state? Any jobs etc should be reviewed
and manually re-created.
John
"Lisa Hoffman" wrote:

> The MS article states "The model and msdb databases can only be restored
> from backups created on a Microsoft? SQL Server? 2000 server. Restore of
> backups of these databases made on SQL Server version 7.0 or earlier is
> not supported."
> This was a migration from 6.5 (and I do not have access to this db
> anymore). Any other suggestions?
> John Bell wrote:
>

Error when opening Maintenance Plan in SQL 2000

I've recently installed SQL 2000 on a new server. Migrated production
databases and data from old SQL 6.5.
Everything checks out OK applications work fine, all data
came over no problem. However... when I try to open or create a
database maintenance plan I get is error:
Microsoft SQL-DMO (ODBC SQLState: 42S22)
Error 207: Invalid column name 'category_id'.
Invalid column name 'netsend_address'.
Invalid column name 'last_netsend_date'.
Invalid column name 'last_netsend_time'.
these colune names do not exist, do I need to create them and if so,
which table do they need to be in? Any help is greatly appreciated. ThanksHI;
Columns mentioned below are in the sysoperators table in msdb database
netsend_address nvarchar 100 NOT NULL
last_netsend_date int 4 NULL
last_netsend_time int 4 NULL
category_id int 4 NULL
IT Looks to me that something happend to your msdb database.
Do you have a backup to restore it from before migration?
Danijel Novak
"Lisa Hoffman" <LisaHoffman@.reish.com> wrote in message
news:OH0vej2BGHA.3584@.TK2MSFTNGP11.phx.gbl...
> I've recently installed SQL 2000 on a new server. Migrated production
> databases and data from old SQL 6.5.
> Everything checks out OK applications work fine, all data
> came over no problem. However... when I try to open or create a
> database maintenance plan I get is error:
> Microsoft SQL-DMO (ODBC SQLState: 42S22)
> Error 207: Invalid column name 'category_id'.
> Invalid column name 'netsend_address'.
> Invalid column name 'last_netsend_date'.
> Invalid column name 'last_netsend_time'.
> these colune names do not exist, do I need to create them and if so, which
> table do they need to be in? Any help is greatly appreciated. Thanks
>|||Hi Lisa
You may want to run a DBCC CHECKCATALOG or DBCC CHECKDB on your MSDB databas
e.
If you need to restore MSDB check out
http://msdn.microsoft.com/library/d... />
t_2w1f.asp
John
"Lisa Hoffman" wrote:

> I've recently installed SQL 2000 on a new server. Migrated production
> databases and data from old SQL 6.5.
> Everything checks out OK applications work fine, all data
> came over no problem. However... when I try to open or create a
> database maintenance plan I get is error:
> Microsoft SQL-DMO (ODBC SQLState: 42S22)
> Error 207: Invalid column name 'category_id'.
> Invalid column name 'netsend_address'.
> Invalid column name 'last_netsend_date'.
> Invalid column name 'last_netsend_time'.
> these colune names do not exist, do I need to create them and if so,
> which table do they need to be in? Any help is greatly appreciated. Than
ks
>
>|||The MS article states "The model and msdb databases can only be restored
from backups created on a Microsoft? SQL Server? 2000 server. Restore of
backups of these databases made on SQL Server version 7.0 or earlier is
not supported."
This was a migration from 6.5 (and I do not have access to this db
anymore). Any other suggestions?
John Bell wrote:
[vbcol=seagreen]
> Hi Lisa
> You may want to run a DBCC CHECKCATALOG or DBCC CHECKDB on your MSDB datab
ase.
> If you need to restore MSDB check out
> http://msdn.microsoft.com/library/d...>
rst_2w1f.asp
> John
> "Lisa Hoffman" wrote:
>|||Hi
I would assume that MSDB was backed up after your installation and therefore
it could be resumed to it's previous state? Any jobs etc should be reviewed
and manually re-created.
John
"Lisa Hoffman" wrote:

> The MS article states "The model and msdb databases can only be restored
> from backups created on a Microsoft? SQL Server? 2000 server. Restore o
f
> backups of these databases made on SQL Server version 7.0 or earlier is
> not supported."
> This was a migration from 6.5 (and I do not have access to this db
> anymore). Any other suggestions?
> John Bell wrote:
>
>

Error when opening Maintenance Plan in SQL 2000

I've recently installed SQL 2000 on a new server. Migrated production
databases and data from old SQL 6.5.
Everything checks out OK applications work fine, all data
came over no problem. However... when I try to open or create a
database maintenance plan I get is error:
Microsoft SQL-DMO (ODBC SQLState: 42S22)
Error 207: Invalid column name 'category_id'.
Invalid column name 'netsend_address'.
Invalid column name 'last_netsend_date'.
Invalid column name 'last_netsend_time'.
these colune names do not exist, do I need to create them and if so,
which table do they need to be in? Any help is greatly appreciated. ThanksHI;
Columns mentioned below are in the sysoperators table in msdb database
netsend_address nvarchar 100 NOT NULL
last_netsend_date int 4 NULL
last_netsend_time int 4 NULL
category_id int 4 NULL
IT Looks to me that something happend to your msdb database.
Do you have a backup to restore it from before migration?
--
Danijel Novak
"Lisa Hoffman" <LisaHoffman@.reish.com> wrote in message
news:OH0vej2BGHA.3584@.TK2MSFTNGP11.phx.gbl...
> I've recently installed SQL 2000 on a new server. Migrated production
> databases and data from old SQL 6.5.
> Everything checks out OK applications work fine, all data
> came over no problem. However... when I try to open or create a
> database maintenance plan I get is error:
> Microsoft SQL-DMO (ODBC SQLState: 42S22)
> Error 207: Invalid column name 'category_id'.
> Invalid column name 'netsend_address'.
> Invalid column name 'last_netsend_date'.
> Invalid column name 'last_netsend_time'.
> these colune names do not exist, do I need to create them and if so, which
> table do they need to be in? Any help is greatly appreciated. Thanks
>|||Hi Lisa
You may want to run a DBCC CHECKCATALOG or DBCC CHECKDB on your MSDB database.
If you need to restore MSDB check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_2w1f.asp
John
"Lisa Hoffman" wrote:
> I've recently installed SQL 2000 on a new server. Migrated production
> databases and data from old SQL 6.5.
> Everything checks out OK applications work fine, all data
> came over no problem. However... when I try to open or create a
> database maintenance plan I get is error:
> Microsoft SQL-DMO (ODBC SQLState: 42S22)
> Error 207: Invalid column name 'category_id'.
> Invalid column name 'netsend_address'.
> Invalid column name 'last_netsend_date'.
> Invalid column name 'last_netsend_time'.
> these colune names do not exist, do I need to create them and if so,
> which table do they need to be in? Any help is greatly appreciated. Thanks
>
>|||The MS article states "The model and msdb databases can only be restored
from backups created on a Microsoft® SQL Server� 2000 server. Restore of
backups of these databases made on SQL Server version 7.0 or earlier is
not supported."
This was a migration from 6.5 (and I do not have access to this db
anymore). Any other suggestions?
John Bell wrote:
> Hi Lisa
> You may want to run a DBCC CHECKCATALOG or DBCC CHECKDB on your MSDB database.
> If you need to restore MSDB check out
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_2w1f.asp
> John
> "Lisa Hoffman" wrote:
>
>>I've recently installed SQL 2000 on a new server. Migrated production
>>databases and data from old SQL 6.5.
>>Everything checks out OK applications work fine, all data
>>came over no problem. However... when I try to open or create a
>>database maintenance plan I get is error:
>>Microsoft SQL-DMO (ODBC SQLState: 42S22)
>>Error 207: Invalid column name 'category_id'.
>>Invalid column name 'netsend_address'.
>>Invalid column name 'last_netsend_date'.
>>Invalid column name 'last_netsend_time'.
>>these colune names do not exist, do I need to create them and if so,
>>which table do they need to be in? Any help is greatly appreciated. Thanks
>>|||Hi
I would assume that MSDB was backed up after your installation and therefore
it could be resumed to it's previous state? Any jobs etc should be reviewed
and manually re-created.
John
"Lisa Hoffman" wrote:
> The MS article states "The model and msdb databases can only be restored
> from backups created on a Microsoft® SQL Server� 2000 server. Restore of
> backups of these databases made on SQL Server version 7.0 or earlier is
> not supported."
> This was a migration from 6.5 (and I do not have access to this db
> anymore). Any other suggestions?
> John Bell wrote:
> > Hi Lisa
> >
> > You may want to run a DBCC CHECKCATALOG or DBCC CHECKDB on your MSDB database.
> >
> > If you need to restore MSDB check out
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_2w1f.asp
> >
> > John
> >
> > "Lisa Hoffman" wrote:
> >
> >
> >>I've recently installed SQL 2000 on a new server. Migrated production
> >>databases and data from old SQL 6.5.
> >>Everything checks out OK applications work fine, all data
> >>came over no problem. However... when I try to open or create a
> >>database maintenance plan I get is error:
> >>
> >>Microsoft SQL-DMO (ODBC SQLState: 42S22)
> >>Error 207: Invalid column name 'category_id'.
> >>Invalid column name 'netsend_address'.
> >>Invalid column name 'last_netsend_date'.
> >>Invalid column name 'last_netsend_time'.
> >>
> >>these colune names do not exist, do I need to create them and if so,
> >>which table do they need to be in? Any help is greatly appreciated. Thanks
> >>
> >>
> >>
>

Error When Creating Maintenance Plan after SP2

Exception has been thrown by the target of an invocation (mscorlib)
Additional information:
Creating instance of the COM component with CLSID
{E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} from iClassFactory failed due
to the following
error: c001f011. (Microsoft.SQLServer.ManagedDTS)
IClassFactory
Microsoft.SQLServer.ManagedDTS
... any ideas?You could try the 3152 cumulative hotfix, which corrected a few issues with
maintenance plans that SP2 either introduced or didn't address.
http://support.microsoft.com/kb/933097/
There is also another GDR / QFE coming soon. See:
http://sqlblog.com/blogs/aaron_bertrand/default.aspx
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<michad76@.gmail.com> wrote in message
news:1174571688.759138.320150@.o5g2000hsb.googlegroups.com...
> Exception has been thrown by the target of an invocation (mscorlib)
> Additional information:
> Creating instance of the COM component with CLSID
> {E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} from iClassFactory failed due
> to the following
> error: c001f011. (Microsoft.SQLServer.ManagedDTS)
>
> IClassFactory
> Microsoft.SQLServer.ManagedDTS
> ... any ideas?
>

Error When Creating Maintenance Plan after SP2

Exception has been thrown by the target of an invocation (mscorlib)
Additional information:
Creating instance of the COM component with CLSID
{E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} from iClassFactory failed due
to the following
error: c001f011. (Microsoft.SQLServer.ManagedDTS)
IClassFactory
Microsoft.SQLServer.ManagedDTS
... any ideas?You could try the 3152 cumulative hotfix, which corrected a few issues with
maintenance plans that SP2 either introduced or didn't address.
http://support.microsoft.com/kb/933097/
There is also another GDR / QFE coming soon. See:
http://sqlblog.com/blogs/aaron_bertrand/default.aspx
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<michad76@.gmail.com> wrote in message
news:1174571688.759138.320150@.o5g2000hsb.googlegroups.com...
> Exception has been thrown by the target of an invocation (mscorlib)
> Additional information:
> Creating instance of the COM component with CLSID
> {E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} from iClassFactory failed due
> to the following
> error: c001f011. (Microsoft.SQLServer.ManagedDTS)
>
> IClassFactory
> Microsoft.SQLServer.ManagedDTS
> ... any ideas?
>

Error when creating a maintenance plan

When using the maintenance plan wizard on SQL 2005 (RTM) when pressing
the finish button the following error occurs when trying to create the
plan.
Maintenance Plan Wizard Progress
Create maintenance plan failed.
Additional Information:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
An Exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum-sqlagent_subsystems). (Microsoft SQL Server, Error 14234)
The job appears in the Job Activity Monitor but greyed out and the
maintenance plan is not listed in its section.
What has caused this issue and how can I resolve it?
You need to apply at least SP1 to your SQL Server instance to make
Maintanence Plan work properly. It'd be better if you apply SP2, it contains
lots of fixes.
Ekrem nsoy
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:9c65cf4b-d7b1-4224-bc11-46da57c143c3@.i12g2000prf.googlegroups.com...
> When using the maintenance plan wizard on SQL 2005 (RTM) when pressing
> the finish button the following error occurs when trying to create the
> plan.
> Maintenance Plan Wizard Progress
> Create maintenance plan failed.
> Additional Information:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> An Exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum-sqlagent_subsystems). (Microsoft SQL Server, Error 14234)
>
> The job appears in the Job Activity Monitor but greyed out and the
> maintenance plan is not listed in its section.
> What has caused this issue and how can I resolve it?
|||Addition: If you do not install SSIS you'll not be able to use Maintanence
Plans in RTM version of SQL Server 2005. It's possible to use Maintanence
Plans without SSIS if you apply SP2.
Ekrem nsoy
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:9c65cf4b-d7b1-4224-bc11-46da57c143c3@.i12g2000prf.googlegroups.com...
> When using the maintenance plan wizard on SQL 2005 (RTM) when pressing
> the finish button the following error occurs when trying to create the
> plan.
> Maintenance Plan Wizard Progress
> Create maintenance plan failed.
> Additional Information:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> An Exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum-sqlagent_subsystems). (Microsoft SQL Server, Error 14234)
>
> The job appears in the Job Activity Monitor but greyed out and the
> maintenance plan is not listed in its section.
> What has caused this issue and how can I resolve it?

Error when creating a maintenance plan

When using the maintenance plan wizard on SQL 2005 (RTM) when pressing
the finish button the following error occurs when trying to create the
plan.
Maintenance Plan Wizard Progress
Create maintenance plan failed.
Additional Information:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
An Exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum-sqlagent_subsystems). (Microsoft SQL Server, Error 14234)
The job appears in the Job Activity Monitor but greyed out and the
maintenance plan is not listed in its section.
What has caused this issue and how can I resolve it?You need to apply at least SP1 to your SQL Server instance to make
Maintanence Plan work properly. It'd be better if you apply SP2, it contains
lots of fixes.
Ekrem nsoy
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:9c65cf4b-d7b1-4224-bc11-46da57c143c3@.i12g2000prf.googlegroups.com...
> When using the maintenance plan wizard on SQL 2005 (RTM) when pressing
> the finish button the following error occurs when trying to create the
> plan.
> Maintenance Plan Wizard Progress
> Create maintenance plan failed.
> Additional Information:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> An Exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum-sqlagent_subsystems). (Microsoft SQL Server, Error 14234)
>
> The job appears in the Job Activity Monitor but greyed out and the
> maintenance plan is not listed in its section.
> What has caused this issue and how can I resolve it?|||Addition: If you do not install SSIS you'll not be able to use Maintanence
Plans in RTM version of SQL Server 2005. It's possible to use Maintanence
Plans without SSIS if you apply SP2.
Ekrem nsoy
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:9c65cf4b-d7b1-4224-bc11-46da57c143c3@.i12g2000prf.googlegroups.com...
> When using the maintenance plan wizard on SQL 2005 (RTM) when pressing
> the finish button the following error occurs when trying to create the
> plan.
> Maintenance Plan Wizard Progress
> Create maintenance plan failed.
> Additional Information:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> An Exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum-sqlagent_subsystems). (Microsoft SQL Server, Error 14234)
>
> The job appears in the Job Activity Monitor but greyed out and the
> maintenance plan is not listed in its section.
> What has caused this issue and how can I resolve it?

Error when creating a maintenance plan

When using the maintenance plan wizard on SQL 2005 (RTM) when pressing
the finish button the following error occurs when trying to create the
plan.
Maintenance Plan Wizard Progress
Create maintenance plan failed.
Additional Information:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
An Exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum-sqlagent_subsystems). (Microsoft SQL Server, Error 14234)
The job appears in the Job Activity Monitor but greyed out and the
maintenance plan is not listed in its section.
What has caused this issue and how can I resolve it?You need to apply at least SP1 to your SQL Server instance to make
Maintanence Plan work properly. It'd be better if you apply SP2, it contains
lots of fixes.
--
Ekrem Önsoy
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:9c65cf4b-d7b1-4224-bc11-46da57c143c3@.i12g2000prf.googlegroups.com...
> When using the maintenance plan wizard on SQL 2005 (RTM) when pressing
> the finish button the following error occurs when trying to create the
> plan.
> Maintenance Plan Wizard Progress
> Create maintenance plan failed.
> Additional Information:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> An Exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum-sqlagent_subsystems). (Microsoft SQL Server, Error 14234)
>
> The job appears in the Job Activity Monitor but greyed out and the
> maintenance plan is not listed in its section.
> What has caused this issue and how can I resolve it?|||Addition: If you do not install SSIS you'll not be able to use Maintanence
Plans in RTM version of SQL Server 2005. It's possible to use Maintanence
Plans without SSIS if you apply SP2.
--
Ekrem Önsoy
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:9c65cf4b-d7b1-4224-bc11-46da57c143c3@.i12g2000prf.googlegroups.com...
> When using the maintenance plan wizard on SQL 2005 (RTM) when pressing
> the finish button the following error occurs when trying to create the
> plan.
> Maintenance Plan Wizard Progress
> Create maintenance plan failed.
> Additional Information:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> An Exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum-sqlagent_subsystems). (Microsoft SQL Server, Error 14234)
>
> The job appears in the Job Activity Monitor but greyed out and the
> maintenance plan is not listed in its section.
> What has caused this issue and how can I resolve it?

error when backup transaction log

hi guys,

I'm using sql 2000 and I got this error when I run backup transaction log in maintenence plan.
"The job failed. The Job was invoked by Schedule 38 (Schedule 1). The last step to run was step 1 (Step 1)."

but even it return an error the backup file is created.

please advice.

thanks

There has to be a more detailed error, if you did not choose to log the job execution, try to do this, as this. Otherwiese you will only get this common exception which does not really help very much.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

hi jens,

after I try to backup just 1 database. it went ok.

maybe it because I backup master and msdb also. but I wonder why is my transaction log file is keep on growing ?

should it get truncated everytime the backup is successfull ? either full backup or transaction log backup right

regards,

-dedys

error when backing up db using maintenance plan

I am gettnig following error when performing a backup using a maintenance plan:

Date 4/24/2007 11:01:23 AM
Log Job History (MaintenancePlan1.Subplan_1)

Step ID 1
Server 006-DEVSQL2005
Job Name MaintenancePlan1.Subplan_1
Step Name Subplan_1
Duration 00:00:09
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: tcs\svcDBASQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:01:24 AM. The return value was unknown. The process exit code was -1073741819. The step failed.

I am able to use the transact sql generated by the maint plan and create a backup. is this a 64 bit issue?

HI Shahab,

Check for the error in the error log and let us know. It should be logged in the error log at the time your backup failed.

Jag

error trying to start new maintenenace plan

Hi ,
I am new to sql 2005 and clustering I too have made the same mistake as
wayne see Geoff response on the 5th of Feb. I have a 2 node cluster and I
have installed SQL within the cluster. I just need clafication that I only
need to run the setup and just select integration services no other options
and It will install fine. Do I need to install this on the second node?
Regards
Steve
Try this
How to: Configure Integration Services on a Cluster
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"steve" <smithsls8@.hotmail.com> wrote in message
news:e2Z2dOTWHHA.4624@.TK2MSFTNGP03.phx.gbl...
> Hi ,
> I am new to sql 2005 and clustering I too have made the same mistake as
> wayne see Geoff response on the 5th of Feb. I have a 2 node cluster and I
> have installed SQL within the cluster. I just need clafication that I
> only need to run the setup and just select integration services no other
> options and It will install fine. Do I need to install this on the second
> node?
> Regards
> Steve
>
>

error trying to start new maintance plan

I have a SQL2K5 cluster and I have what I believe is a valid setup but I am
now getting the following error/warning when I try to start the maintenance
wizard or start a new maintenance plan. I have installed workstation tools
on both.
I have Windows 2003 64bit SQL2K5 SP1 with all current patches.
I am new to Clusters as well as SQL2K5 so I may be missing something.
Any guesses?
error message details below.
===================================
The action you attempted to perform on a remote instance of SQL Server has
failed because the action requires a SQL Server component that is not
installed on the remote computer. To proceed, install SQL Server 2005
Management Tools on the remote computer, and then try again. For more
information, see "How to: Install SQL Server 2005 (Setup)" in SQL Server
2005 Books Online, or find the article on MSDN at
http://go.microsoft.com/fwlink/?LinkID=57083 . (ObjectExplorer)
For help, click: http://go.microsoft.com/fwlink/?LinkID=57083
Program Location:
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintDesignerMenuHandler.Invoke()
One of the "improvements" for SQL 2005 is that the client tool installer is
not cluster aware. You have to run the installer on each cluster node and
add the client tools. Personally, I do this as a first step before
installing the cluster-aware components rather than as a folllow-on
activity.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wayne" <watkinson@.sd43.bc.ca> wrote in message
news:%232fESGxRHHA.3948@.TK2MSFTNGP05.phx.gbl...
>I have a SQL2K5 cluster and I have what I believe is a valid setup but I am
>now getting the following error/warning when I try to start the maintenance
>wizard or start a new maintenance plan. I have installed workstation tools
>on both.
> I have Windows 2003 64bit SQL2K5 SP1 with all current patches.
> I am new to Clusters as well as SQL2K5 so I may be missing something.
> Any guesses?
> error message details below.
> ===================================
> The action you attempted to perform on a remote instance of SQL Server has
> failed because the action requires a SQL Server component that is not
> installed on the remote computer. To proceed, install SQL Server 2005
> Management Tools on the remote computer, and then try again. For more
> information, see "How to: Install SQL Server 2005 (Setup)" in SQL Server
> 2005 Books Online, or find the article on MSDN at
> http://go.microsoft.com/fwlink/?LinkID=57083 . (ObjectExplorer)
> --
> For help, click: http://go.microsoft.com/fwlink/?LinkID=57083
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.DatabaseMaintenance .MaintDesignerMenuHandler.Invoke()
>
>
|||Thanks Geoff, but I have installed on both and now I am wondering if this is
a problem with SP1 as I am getting some hits on google for similar
problems...
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OkyQMMxRHHA.3996@.TK2MSFTNGP04.phx.gbl...
> One of the "improvements" for SQL 2005 is that the client tool installer
> is not cluster aware. You have to run the installer on each cluster node
> and add the client tools. Personally, I do this as a first step before
> installing the cluster-aware components rather than as a folllow-on
> activity.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Wayne" <watkinson@.sd43.bc.ca> wrote in message
> news:%232fESGxRHHA.3948@.TK2MSFTNGP05.phx.gbl...
>
|||Doh, I didn't install Intigration Services! Once I installed them, things
worked as they should.
Error messages were misleading
"Wayne" <watkinson@.sd43.bc.ca> wrote in message
news:e1PGNgxRHHA.3412@.TK2MSFTNGP05.phx.gbl...
> Thanks Geoff, but I have installed on both and now I am wondering if this
> is a problem with SP1 as I am getting some hits on google for similar
> problems...
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:OkyQMMxRHHA.3996@.TK2MSFTNGP04.phx.gbl...
>

Error trying to create Maintenance plan

We have a SQL server installation on a managed hosting environment and last night they upgraded our version from standard to enterprise. It looks as if they uninstalled, and then reinstalled. So now I need to re-configure our maintenance plans but when I try right-click on Maintenance Plans and select New Maintenance Plan in Management Studio I get the following error: An OLE DB error 0x800004005 occured while enumerating packages. A SQL statement was issued and failed.

I thought it had something to do with the new installation not running as a particular user/system account and the account it wass running under didnt have the proper permissions. As a smoke test I made the user it's running as (sqlservice) an admin in SQL Server, but that didnt fix the problem

Any help would be appreciated.Have you installed SSIS too in this case if that was also available previouslY?

ERROR sql2005 sobre win2000 ad server

hi
when i try to save a maintenance plan
i get an error like
"GUID must have 32 characters y 4 hyphens"
"xxxxxx-xxxxx-...-xxxxx"
this occurs in production
( sql2005 over win 2000 advanced server )
but, on dev environment
( over win 2003 server )
works fine
any idea ?
thanks
--
atte,
Hernánbajopalabra wrote:
> hi
> when i try to save a maintenance plan
> i get an error like
> "GUID must have 32 characters y 4 hyphens"
> "xxxxxx-xxxxx-...-xxxxx"
> this occurs in production
> ( sql2005 over win 2000 advanced server )
> but, on dev environment
> ( over win 2003 server )
> works fine
> any idea ?
> thanks
>
That sounds like a custom message coming from a trigger. Are there
triggers on that table in production that aren't in development?|||no triggers
this is the real error
"GUID should contain 32 digits with 4 dashes"
when trying to save the more easy simple plan
it seems to be a bug
both sql servers reports
different MDAC versions and files ...
could it be the point ' the MDAC '
atte,
Hernán
"Tracy McKibben" <tracy@.realsqlguy.com> escribió en el mensaje
news:ucm2uMVlGHA.5044@.TK2MSFTNGP02.phx.gbl...
| bajopalabra wrote:
| > hi
| > when i try to save a maintenance plan
| > i get an error like
| >
| > "GUID must have 32 characters y 4 hyphens"
| > "xxxxxx-xxxxx-...-xxxxx"
| >
| > this occurs in production
| > ( sql2005 over win 2000 advanced server )
| >
| > but, on dev environment
| > ( over win 2003 server )
| > works fine
| >
| > any idea ?
| > thanks
| >
|
| That sounds like a custom message coming from a trigger. Are there
| triggers on that table in production that aren't in development?|||bajopalabra wrote:
> no triggers
> this is the real error
> "GUID should contain 32 digits with 4 dashes"
> when trying to save the more easy simple plan
> it seems to be a bug
> both sql servers reports
> different MDAC versions and files ...
> could it be the point ' the MDAC '
>
Apparently this is a known bug that is fixed in Service Pack 1
http://support.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.tools&tid=ea561347-3d88-41c2-b475-88bfd478c3ce&p=1|||i think it's fixed on win2003
but not on win2000
i tested in both servers
i found a workareound
as i read on another newsgroup
if i create the plan via wizard
there are no problem
later, i can change the plan as i wish
very rare thing...
atte,
Hernán
"Tracy McKibben" <tracy@.realsqlguy.com> escribió en el mensaje
news:uJc89oVlGHA.3776@.TK2MSFTNGP03.phx.gbl...
| bajopalabra wrote:
| > no triggers
| > this is the real error
| >
| > "GUID should contain 32 digits with 4 dashes"
| >
| > when trying to save the more easy simple plan
| >
| > it seems to be a bug
| >
| > both sql servers reports
| > different MDAC versions and files ...
| > could it be the point ' the MDAC '
| >
| >
|
| Apparently this is a known bug that is fixed in Service Pack 1
|
http://support.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.tools&tid=ea561347-3d88-41c2-b475-88bfd478c3ce&p=1
|