Update Statistics Failure with Computed Column
-
After adding a computed column to a table (SQL Server 2000 sp3a), the db maintenance plan failed when updating statistics. The error logged is
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
I changed the computed column to an int column and the plan executes without error. I changed it back to a computed column and the error returned. I rebuilt the indexes and the problem appears to have gone away for now.
Can somebody enlighten me on this? Do I have to worry about the job failing in the future?
Thanks, Randy
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
PS The computed column uses the formula
(convert(tinyint,case when ([check_count] = 0) then 0 when ([check_sum] = [pay_line_sum]) then 0 else 1 end))
I initially cast to a bit. I changed to a tinyint thinking that perhaps that the bit was the problem. Once I understand the problem, I will wish to use a bit if possible.
I also do not wish to write a script to run the update statistics separately.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
I had the same problem. I read an article in Microsoft about why this is but I can't find the link now.
Anyway I found a script by T. Pullen in another forum which will reindex your table dynamically. I had a job scheduled to exec this sp and generate a output file so I can see the result of the sp.
You will need to modify the script if your tables are not owned by dbo.
Hope this helps.
See below:-
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'ssp_defrag_idx'
AND type = 'P')
DROP PROCEDURE ssp_defrag_idx
GO
CREATE PROCEDURE ssp_defrag_idx @maxfrag DECIMAL
AS
-- T.Pullen 29.11.2001
--
-- This stored procedure checks index fragmentation in a database and defragments
-- indexes whose scan densities fall below a specified threshold, @magfrag, which
-- is passed to the SP.
--
-- Must be run in the database to be defragmented.
-- Declare variables
SET NOCOUNT ON
-- SET QUOTED_IDENTIFIER ON
-- SET ARITHABORT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @indexname CHAR(255)
DECLARE @dbname sysname
DECLARE @emailaddress varchar(50)
SELECT @dbname = db_name()
IF @dbname IN ('master', 'msdb', 'model', 'tempdb','ServerAdmin')
BEGIN
SELECT @emailAddress=Email_Address FROM msdb..sysoperators WHERE Name='Your Name'
EXEC master..xp_sendmail
@recipients =@emailaddress,
@subject = 'WARNING: SP in the wrong database',
@message = 'This procedure should not be run in system databases.'
END
-- Declare cursor
DECLARE tables CURSOR FOR
select so.[name]
from sysobjects so,
sysindexes si,
sysusers su
where
so.[id]=si.[id] and
so.[uid]=su.[uid] and
so.type='U' and
si.indid 0 and
su.uid = 1 -- where owner is 'dbo'
order by so.[name]
-- Create the table
CREATE TABLE #fraglist
(
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
SELECT * FROM #fraglist
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexName, ScanDensity
FROM #fraglist
WHERE ScanDensity 0
SELECT @emailAddress=Email_Address FROM msdb..sysoperators WHERE Name='Your Name'
EXEC master..xp_sendmail
@recipients =@emailaddress,
@subject = 'Defrag indexes started',
@query = "SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE(),113)"
--"SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())"
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexname, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @execstr = 'DBCC DBREINDEX (' + RTRIM(@tablename) + ',' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'
SELECT (@execstr)
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexname, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
SELECT @emailAddress=Email_Address FROM msdb..sysoperators WHERE Name='Your Name'
EXEC master..xp_sendmail
@recipients =@emailaddress,
@subject = 'Defrag indexes finished',
@query = "SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE(),113)"
--"SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())"
-- Delete the temporary table
DROP TABLE #fraglist
GO
-
Microsoft Article # 301292 I had this issue come up all of a sudden when running DBCC Updatestats via Maint. wizard. It worked fine for months then all of a sudden started failing.
-
Thank you for the reference. I'll try the following in the job, as suggested in the article.
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
EXECUTE master.dbo.xp_sqlmaint N'-PlanID ....
-Randy
It's not a bug - it's a feature!
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
I think I did try it but still wouldn't work on my DB. Hence the script.
-
I guess what I don't understand is this db has existed for 4 months, running fine and then all of a sudden the failure.... Why now, why not from the beginning ?
-
I also rebuilt my indexes first. Perhaps that made a difference.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply