Microsoft KB Archive/156025

= BUG: DOC: Update Statistics Example to Update All Tables Wrong =

Article ID: 156025

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q156025





SYMPTOMS
The UPDATE STATISTICS Statement article in the Transact-SQL Reference documentation has an error. Syntax is missing the script listed in the section titled "C. Use Cursors to Run UPDATE STATISTICS on All Tables."

If you try to execute this script, the following errors are returned:

Msg 170, Level 15, State 1

Line 18: Incorrect syntax near 'RTRIM'.

Msg 170, Level 15, State 1

Line 20: Incorrect syntax near '@tablename'.

Msg 170, Level 15, State 1

Line 27: Incorrect syntax near ' *************'.

Msg 2812, Level 16, State 4

Stored procedure 'update_all_stats' not found.



WORKAROUND
The script needs to be modified by adding three additional plus signs (+). The script below will run without error:

CREATE PROCEDURE update_all_stats AS

/*  This procedure will run UPDATE STATISTICS against all user-defined tables within this database.

DECLARE @tablename varchar(30) DECLARE @tablename_header varchar(75) DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U' OPEN tnames_cursor FETCH NEXT FROM tnames_cursor INTO @tablename WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN SELECT @tablename_header = "Updating " + RTRIM(UPPER(@tablename)) PRINT @tablename_header EXEC ("UPDATE STATISTICS " + @tablename ) END FETCH NEXT FROM tnames_cursor INTO @tablename END PRINT " " PRINT " " SELECT @tablename_header = "************* NO MORE TABLES" + " *************" PRINT @tablename_header PRINT " " PRINT "Statistics have been updated for all tables." DEALLOCATE tnames_cursor go update_all_stats go



STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0 and 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Additional query words: index truncate delete insert row

Keywords: kbbug kbnetwork KB156025

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.