sp_createstats

mgm · 2013-01-21

I am running the following script daily:

exec sp_updatestats
go
exec sp_createstats 'indexonly'
go

The sp_createstats raises an error:

Msg 1927, Level 16, State 2, Line 1
There are already statistics on table 'XXX$Detail Line' named 'B-Account'.

If I set the property MaintainSQLIndex to NO on the specific key. The script runs fine.
But of course, this is not the solution.

stryk · 2013-01-25

What Key/Indexes are on this table?

stryk · 2013-01-25

Important: set the db property "Auto. Create Stats" to FALSE

Maybe clean up previously created Auto Stats using this little script:

DECLARE curAllStats CURSOR  FOR
SELECT o.name ObjName, s.Name StName
FROM   sys.stats s, sys.objects o
WHERE s.auto_created = 1 AND
      s.user_created = 0 AND
      s.object_id = o.object_id AND 
      o.type in ('U','V')
DECLARE  @ObjectName NVARCHAR(512)
DECLARE  @StatName NVARCHAR(512)
OPEN curAllStats
FETCH NEXT FROM curAllStats INTO @ObjectName, @StatName
WHILE @@FETCH_STATUS = 0
  BEGIN
    PRINT 'DROP STATISTICS [' + @ObjectName + '].[' + @StatName + ']'
    EXEC('DROP STATISTICS [' + @ObjectName + '].[' + @StatName + ']')
    FETCH NEXT FROM curAllStats INTO @ObjectName, @StatName
  END
CLOSE curAllStats
DEALLOCATE curAllStats
GO

mgm · 2013-01-25

Jorg,

Thanks for the fast reply

Unfortunately the script doesn't resolve the error.

The following keys exist on Table Detail Line:
Enabled Key SumIndexFields
Yes Transaction No.
Yes Account Type,Serial No. (Entry),Status,Date Amount,Amount (Entry)
Yes Account Type,Account No.,Bank,Transaction Mode,Currency Code,Date
Yes Our Bank,Status,Connect Batches,Connect Lines,Date Amount,Amount (Entry)
Yes Account Type,Serial No. (Entry),Status,Transaction No.,Subcontracting,B-Account Amount,Amount (Entry)

I did the following and found weird results:
1. I removed field "B-Account" from the key > sp_createstats works fine.
2. I added field "B-Account" to the key again > sp_createstats does NOT work.
3. I renamed field "B-Account" to "BAccount" > sp_createstats works fine! Strange.
4. I renamed field "BAccount" back to "B-Account" again > sp_createstats works fine! Very strange!

Anyway. It works now.

stryk · 2013-01-25

My Test:

1) I created this table:

Field No.	Field Name	Data Type	Length
1	Entry No.	Integer	
2	Text	Text	30
3	Code	Code	10
4	B-Account Amount	Decimal

2) I created these Keys/Indexes:

Key	Clustered	MaintainSQLIndex
Entry No.	Yes	Yes
Text,Code,B-Account Amount	No	Yes

3) I run sp_createstats 'indexoly':

NAV_403_PTB.dbo.CRONUS International Ltd_$Stats Test-Tabelle: Die Statistiken für die folgenden Spalten werden erstellt:
     Code
     B-Account Amount

No problemo ... :-k

What collation do you use? Which SQL build? NAV build?

Does anything change if you delete al stats created by sp_createstats using that script?:

SET STATISTICS IO OFF
SET NOCOUNT ON
GO

DECLARE curAllStats CURSOR  FOR
SELECT o.name ObjName, s.Name StName
FROM   sys.stats s, sys.objects o
WHERE s.auto_created = 0 AND
      s.user_created = 1 AND
      s.object_id = o.object_id AND 
      o.type in ('U','V')
DECLARE  @ObjectName NVARCHAR(512)
DECLARE  @StatName NVARCHAR(512)
OPEN curAllStats
FETCH NEXT FROM curAllStats INTO @ObjectName, @StatName
WHILE @@FETCH_STATUS = 0
  BEGIN
    PRINT 'DROP STATISTICS [' + @ObjectName + '].[' + @StatName + ']'
    EXEC('DROP STATISTICS [' + @ObjectName + '].[' + @StatName + ']')
    FETCH NEXT FROM curAllStats INTO @ObjectName, @StatName
  END
CLOSE curAllStats
DEALLOCATE curAllStats
GO

mgm · 2013-01-25

Jorg,

Collation: Latin1_General_CI_AS
SQL Server version: 10.50.2550.0
NAV build: 32480

I also can not reproduce the error anymore.
Seems renaming the field solved the problem...

I don't know what went wrong and since when.
For me the issue is closed.
But if you find the cause, I'd like to know

stryk · 2013-01-25

Well, you need to have in mind that stats generated by sp_createstats are "User Stats". Thus, sometime you get such kind of errors if you e.g. change the name or datatype of a field, which is already part of an index. NAV cannot automatically deal with those stats.

If that happens I suggest to erase the users stats using that script (if possible by adding a filter on the relevant tablename):

SET STATISTICS IO OFF
SET NOCOUNT ON
GO

DECLARE curAllStats CURSOR  FOR
SELECT o.name ObjName, s.Name StName
FROM   sys.stats s, sys.objects o
WHERE s.auto_created = 0 AND
      s.user_created = 1 AND
      s.object_id = o.object_id AND
      o.type in ('U','V')
DECLARE  @ObjectName NVARCHAR(512)
DECLARE  @StatName NVARCHAR(512)
OPEN curAllStats
FETCH NEXT FROM curAllStats INTO @ObjectName, @StatName
WHILE @@FETCH_STATUS = 0
  BEGIN
    PRINT 'DROP STATISTICS [' + @ObjectName + '].[' + @StatName + ']'
    EXEC('DROP STATISTICS [' + @ObjectName + '].[' + @StatName + ']')
    FETCH NEXT FROM curAllStats INTO @ObjectName, @StatName
  END
CLOSE curAllStats
DEALLOCATE curAllStats
GO

Then I try to recreate the stats from the scratch, using sp_createstats 'indexonly' ...

sp_createstats

Answers

Categories