July 10, 2007 at 5:49 pm
I have sql server 2005 sp2, I am running maintenance wizard plann for (Re-building indexes, re-oraganize index, update statistic, check database integrity , log backup and database back up)
This is the error I get, how can I fix this?
Executing the query "UPDATE STATISTICS [dbo].[CaseNum]
WITH FULLSCAN
" failed with the following error: "SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
July 10, 2007 at 11:09 pm
You can see the ARITHABORT setting for your database by right clicking over db name -> script db; probably it is set to off. Check with BOL if, by changing the setting to ON, this would not affect your app. Just change it to ON using ALTER DATABASE statement. The message could appear if your db is in compatibility mode of 80, or you've got either a db or server DDL trigger.
Hope this helps.
July 10, 2007 at 11:19 pm
Do you have any computed columns in the database. This kind of error occurs if there is one so and if you use maint plan for reindexing.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 12, 2007 at 7:36 am
No I dont have any computed columns.
July 12, 2007 at 11:24 am
Based upon the error, I would look at the CaseNum table. Is it actually an indexed view instead of a table? Does an indexed view reference the table? Are there computed columns or columns of the xml data type?
Regards,
Rubes
July 12, 2007 at 11:31 am
I am almost sure that we dont have views. One more thing same database on different server worked fine with all maintenance in place. I restored that database from 32bit server to 64bit server with SP2. Now on new server I cannot make the maintenance wizard work.
July 12, 2007 at 11:53 am
Run an sp_help on CaseNum and post the results here.
Regards,
Rubes
July 13, 2007 at 12:41 am
Could yo post the table details of CaseNum it lloks there is a computed column in the table or view.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 13, 2007 at 4:54 am
also could you run this query against the database andd post the result.
select
* from syscolumns where iscomputed = 1
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 24, 2007 at 7:47 pm
I ran
select * from syscolumns where iscomputed = 1
zero record found
I ran sp_help casenum and I did not find any computed columns.
what could be wrong? I am running update statistic and reindexing job as part of maintenance for all database. will it execute maintenance plan on other tables and databases, or it will stop at the error?
July 24, 2007 at 9:29 pm
Bhavin,
If you took the time to run sp_help, take the time to post the results. As indicated by the error message you submitted, it *could* be an issue with computed columns but it could be something else as well. If you do that, I am confident that someone on this forum will find answer for your dilemma.
Regards,
Rubes
July 25, 2007 at 12:05 am
Can you post the metadata of the CaseNum table?
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 29, 2007 at 10:01 am
Please check this?
GO
/****** Object: Table [dbo].[CaseNum] SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CaseNum](
[casenum] [bigint] NOT NULL,
[warrRegNum] [varchar](9) NOT NULL,
[LName] [varchar](50) NOT NULL,
[FName] [varchar](50) NOT NULL,
[Address1] [varchar](50) NOT NULL,
[Address2] [varchar](50) NULL,
[City] [varchar](50) NOT NULL,
[State] [char](2) NOT NULL,
[Zip] [char](5) NOT NULL,
[Phone] [char](12) NOT NULL,
[Fax] [char](12) NULL,
[Email] [varchar](50) NULL,
[comments] [varchar](50) NOT NULL,
[status] [varchar](5) NOT NULL,
CONSTRAINT [PK_CaseNum] PRIMARY KEY CLUSTERED
(
[casenum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
RESULT OF sp_help Casenum
Name Owner Type Created_datetime
CaseNum dbo user table 2005-12-22 14:37:57.293
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
casenum bigint no 8 no (n/a) (n/a) NULL
warrRegNum varchar no 9 no no no SQL_Latin1_General_CP1_CI_AS
LName varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
FName varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
Address1 varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
Address2 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
City varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
State char no 2 no no no SQL_Latin1_General_CP1_CI_AS
Zip char no 5 no no no SQL_Latin1_General_CP1_CI_AS
Phone char no 12 no no no SQL_Latin1_General_CP1_CI_AS
Fax char no 12 yes no yes SQL_Latin1_General_CP1_CI_AS
Email varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
comments varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
status varchar no 5 no no no SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PK_CaseNum clustered, unique, primary key located on PRIMARY casenum
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
PRIMARY KEY (clustered) PK_CaseNum (n/a) (n/a) (n/a) (n/a) casenum
No foreign keys reference table 'casenum', or you do not have permissions on referencing tables.
No views with schema binding reference table 'casenum'.
July 30, 2007 at 7:17 am
Try to run "UPDATE STATISTICS [dbo].[CaseNum]
WITH FULLSCAN" manually.
Regards,
Rubes
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply