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