April 16, 2015 at 4:32 pm
Error:
Error 16958, Severity 16, State 3, Procedure usp_updateStats_DBS_best, Line 76
Could not complete cursor operation because the set options have changed since the cursor was declared.
********************************
USE [Master]
GO
/****** Object: StoredProcedure [dbo].[usp_updateStats_DBS_best] Script Date: 4/15/2015 11:50:24 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[USP_UPDATESTATS_DBS_BEST]
AS
BEGIN
BEGIN TRY
SET ANSI_NULLS ON
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
SET ANSI_WARNINGS ON
--Set quoted_identifier off
DECLARE @tablename varchar(max)
DECLARE @tablename_header varchar(max)
DECLARE @dataname varchar(max)
DECLARE @dataname_header varchar(max)
IF (SELECT Cursor_status('global', 'datanames_cursor')) >= -1
BEGIN
IF (SELECT Cursor_status('global', 'datanames_cursor')) >
-1
BEGIN
CLOSE datanames_cursor
END
DEALLOCATE datanames_cursor
END
IF (SELECT Cursor_status('global', 'tnames_cursor')) >= -1
BEGIN
IF (SELECT Cursor_status('global', 'tnames_cursor')) >
-1
BEGIN
CLOSE tnames_cursor
END
DEALLOCATE tnames_cursor
END
DECLARE datanames_cursor CURSOR FOR
select name from sys.databases where database_id in (
SELECT dbid
FROM sys.sysdatabases
EXCEPT
SELECT database_id
FROM msdb..suspect_pages)
AND name NOT IN ('master','msdb','tempdb','model','distribution')
AND state_desc = 'ONLINE';
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM datanames_cursor INTO @dataname
CONTINUE
END
SELECT @dataname_header = 'Database ' + RTRIM(UPPER(@dataname))
PRINT ' '
PRINT @dataname_header
PRINT ' '
EXEC ('USE ' + @dataname + ' DECLARE tnames_cursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [' + @dataname + '].INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE''' )
Select @dataname_header = RTRIM(UPPER(@dataname))
Exec ('Use ' + @dataname)
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM tnames_cursor INTO @tablename
CONTINUE
END
SELECT @tablename_header = ' Updating ' + RTRIM(UPPER(@tablename))
PRINT ' '
PRINT @tablename_header
EXEC ('USE ' + @dataname + ' UPDATE STATISTICS ' + @tablename + 'WITH FULLSCAN, NORECOMPUTE')
FETCH NEXT FROM tnames_cursor INTO @tablename
END
--CLOSE tnames_cursor
DEALLOCATE tnames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
END
CLOSE datanames_cursor
DEALLOCATE datanames_cursor
PRINT ' '
PRINT ' '
PRINT ' **** Update Stats Task is completed for All User Databases. ******'
SET ANSI_NULLS OFF
SET ARITHABORT OFF
SET QUOTED_IDENTIFIER OFF
SET ANSI_WARNINGS OFF
END TRY
BEGIN catch
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(4000);
DECLARE @ErrorMessage NVARCHAR(4000);
SELECT @ErrorNumber = Error_number(),
@ErrorSeverity = Error_severity(),
@ErrorState = Error_state(),
@ErrorLine = Error_line(),
@ErrorProcedure = Error_procedure();
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
+ 'Message: ' + Error_message();
SELECT @ErrorMessage AS [Error_Message];
SELECT @ErrorProcedure AS [Error_Procedure];
PRINT 'Error '
+ CONVERT(VARCHAR(50), Error_number())
+ ', Severity '
+ CONVERT(VARCHAR(5), Error_severity())
+ ', State '
+ CONVERT(VARCHAR(5), Error_state())
+ ', Procedure '
+ Isnull(Error_procedure(), '-') + ', Line '
+ CONVERT(VARCHAR(5), Error_line());
PRINT Error_message();
END catch
END
--SELECT * from sysobjects where type = 'U'
--select * from sys.schemas
--SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
--FROM AdventureWorks2012.INFORMATION_SCHEMA.COLUMNS
--SELECT * FROM INFORMATION_SCHEMA.TABLES
-- WHERE table_type = 'BASE TABLE'
GO
Thanks.
April 17, 2015 at 2:46 am
There's no need to reinvent the wheel. Use Ola Hallengren's maintenance solution. http://ola.hallengren.com
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply