April 15, 2015 at 11:46 am
USE [DB_ADMIN]
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT ON
GO
SET ARITHABORT ON
GO
alter Proc USP_UPDATESTATSALLDBS_1
AS
BEGIN
BEGIN TRY
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
SET ANSI_NULLS ON 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
SET ANSI_NULLS ON
CONTINUE
END
SELECT @dataname_header = 'Database ' + RTRIM(UPPER(@dataname))
PRINT ' '
PRINT @dataname_header
PRINT ' '
SET ANSI_NULLS ON
EXEC ('USE ' + @dataname + ' SET ANSI_NULLS ON 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. ******'
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
SET ANSI_NULLS OFF
GO
SET ANSI_PADDING OFF
GO
SET ANSI_WARNINGS OFF
GO
SET CONCAT_NULL_YIELDS_NULL OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET ARITHABORT OFF
Thanks.
April 15, 2015 at 1:28 pm
I ran the code twice with no errors. Sorry I can't be of any help.
April 15, 2015 at 1:30 pm
In which version of sql server did u run the script?
Thanks.
April 15, 2015 at 1:32 pm
SQL Server 2008 R2 SE and SQL Server 2012 DE.
April 15, 2015 at 2:07 pm
You check for a "GLOBAL" cursor but you don't explicitly specify a GLOBAL cursor when you DECLARE it. Whether a cursor is local or global can vary based on current db context.
You should explicitly declare the cursor as GLOBAL if you want a global cursor. Likewise, when you DEALLOCATE the cursor, be sure to specify GLOBAL there as well.
Btw, you don't need to CLOSE the cursor first, just DEALLOCATE it -- you can DEALLOCATE a currently open cursor.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply