Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Refresh All Views Expand / Collapse
Author
Message
Posted Monday, December 3, 2007 4:01 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:36 AM
Points: 1,158, Visits: 878
Comments posted to this topic are about the item Refresh All Views
Post #429031
Posted Monday, January 28, 2008 7:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:33 PM
Points: 1,187, Visits: 1,985
Numerous issues with this script. A lot of extra logic. It can be streamlined.
1. Why all the dynamic SQL?
2. The names of the views should be obtained from information_schema.tables WHERE table_type = 'VIEW'.
3. When refreshing a view, you should also include the owner (schema). If there is more than one schema in the database, an error will result when you're refreshing the view and are executing as another owner (e.g., "dbo").
4. There is no need for dynamic SQL when executing sp_refreshview.
5. If dynamic SQL is to be used, procedure sp_executesql should be used vs. EXECUTE.
6. I don't see the need for a temp table. a LOCAL FAST_FORWARD cursor against information_schema will work.

Simpler logic attached.



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.


  Post Attachments 
RefreshAllViews.txt (77 views, 1.22 KB)
Post #448297
Posted Thursday, June 26, 2008 11:24 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 11:14 PM
Points: 339, Visits: 596
Hi BeoWulf and John,

Both the scripts provided are good and working as well.

But whenever any of the view is not getting refreshed due to Binding errors it is exiting out from the cursor and it is not refreshing views after that failed view.

Can anyone suggest me how to fix this.Means I wanted to exclude (or print the view name which is not refreshed) the un refreshed view and
refresh the other views.

Post #524766
Posted Friday, June 27, 2008 7:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:33 PM
Points: 1,187, Visits: 1,985
Can anyone suggest me how to fix this.Means I wanted to exclude (or print the view name which is not refreshed) the un refreshed view and refresh the other views.

The script is coded to exit on the first error. So add a TRY ... CATCH around the refreshing statement. Print out the name in the CATCH block.

If you don't know about the TRY ... CATCH syntax introduced in SQL Server 2005, this is a good script to work your first implementation. See the BOL for syntax as well as a search on this forum. There are numerous threads.



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #524963
Posted Monday, November 9, 2009 3:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 18, 2011 4:51 AM
Points: 2, Visits: 24
You can simply use this stored procedure to refresh all database views with one command (including dependent views problem handling - Binding errors)

Note : this procedure is examined on SQL Server 2005 .

CREATE procedure [dbo].[refviews] ( @varViewName VARCHAR(500) = NULL )
--Created on 08 Feb 2009 By Ahmed Omara
as
declare @p_viewname nvarchar(500)
BEGIN
IF (@varViewName IS NOT NULL OR @varViewName <>'')
--Validate certain view specified.
BEGIN
SET @varViewName = ('['+@varViewName+']');
PRINT 'Refreshing View... ' + @varViewName;
EXEC sp_refreshview @varViewName;
END;
ELSE
BEGIN
--CREATE CURSOR TO READ THE DATABASE VIEWS IN ORDER TO VALIDATE
Declare @CrsrView Cursor
Set @CrsrView = Cursor For
SELECT [name]
FROM sysObjects
WHERE xType = 'V'
AND [name] NOT IN ('syssegments', 'sysconstraints')
AND category=0
ORDER BY crdate


-- fetch the first record in the Cursor
Open @CrsrView
Fetch Next From @CrsrView Into @p_viewname

While (@@FETCH_STATUS = 0) --WHILE THE CURSER STILL INCLUDE RECORDS
BEGIN
--refresh the view
SET @varViewName = ('['+@varViewName+']');
PRINT 'Refreshing View... ' + @p_viewname;
EXEC sp_refreshview @p_viewname;

-- fetch the next record in the Cursor
Fetch Next From @CrsrView Into @p_viewname
END
Close @CrsrView
Deallocate @CrsrView
PRINT 'Refresh process is done successfully'
END;
End;

/*
To Run the procedure for all views type:
EXEC refviews

To Run the procedure for certain view (Ex. view "VWMMS_MR") type :
EXEC refviews VWMMS_MR
*/
Post #815714
Posted Tuesday, March 13, 2012 4:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:25 AM
Points: 45, Visits: 114
Just in case anyone was looking for a post SS 2k version that avoids cursors (and dare I say more readable?) version.
/*
uspRefreshViews
Date: 2012-03-13
purpose: to refresh the schema bindings of the current db
usage: exec dbo.uspRefreshViews
comments:
omitted error handling (everyone has their own-rolled way which is best) and single view refresh

*/
create proc dbo.uspRefreshViews
as
begin
set nocount on
declare @views table
( id int identity(1,1) not null,
schemaName NVARCHAR(128),
viewName nvarchar(128) not null
);
declare
@maxID int,
@id int = 0,
@aViewName nvarchar(128) = '',
@aSchemaViewName NVARCHAR(258)

insert into @views
select sch.name, vw.name
FROM sys.views vw
inner join sys.schemas sch on sch.schema_id = vw.schema_id

select @maxID = coalesce(@@ROWCOUNT,0)

while @id <> @maxID
begin
select top 1 @id = id, @aSchemaViewName = schemaName + '.' + viewName
from @views
where @id < id
order by id asc

print 'Refreshing '+@aSchemaViewName
EXECUTE sp_refreshview @aSchemaViewName
end
end

Kind regards

Derek
Post #1265793
Posted Wednesday, June 20, 2012 12:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 19, 2012 5:51 PM
Points: 5, Visits: 20
While this works great when views are valid it crashes the proc if a view won't refresh correctly.
It seems to be fine when running this with the sp_refreshsqlmodule on functions and stored procedures...if validation fails it reports it and lets you move on to the next one.
On views it always errors out...it shows the validation error and then crashes on the next view with the following error:


refreshing...[dbo].[vwA]
Validation failed for : [dbo].[vwA], Error:Invalid column name 'address'.
refreshing...[dbo].[vwB]
Validation failed for : [dbo].[vwB], Error:The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Any ideas on how to trap this and move on to the next...I'm trying to generate a list of faulty views.

thanks

PS Try Catch does NOT work on views....works like a charm on Stored Procs

here is my try catch logic:

WHILE @Cnt <= @Tot BEGIN
SELECT @spname = spname
FROM @v
WHERE RecID = @Cnt

PRINT 'refreshing...' + @spname


BEGIN TRY
EXEC sp_refreshsqlmodule @spname
END TRY
BEGIN CATCH
PRINT 'Validation failed for : ' +
@spname + ', Error:' +
ERROR_MESSAGE()
END CATCH
SET @Cnt = @cnt + 1
END
Post #1318941
Posted Wednesday, June 20, 2012 2:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:25 AM
Points: 45, Visits: 114
Have you tried calling the proc recursively in the catch block with a parameter indicating the progress?
Post #1318984
Posted Wednesday, June 20, 2012 4:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 19, 2012 5:51 PM
Points: 5, Visits: 20
I figured it out...works like a charm

SET XACT_ABORT ON;
BEGIN TRY
-- refresh the stored procedure

BEGIN TRANSACTION;
-- refresh the proc/view/function
EXEC sp_refreshsqlmodule @spname
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
PRINT 'Validation failed for : Type ' +
@type + ', Name: ' + @spname + ', Error:' + ERROR_MESSAGE()

ROLLBACK TRANSACTION;
END;
END CATCH

Post #1319011
Posted Saturday, May 4, 2013 7:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 4, 2013 7:04 AM
Points: 1, Visits: 1
You are Fantastic.... This is the best solution I ever got... :)
Thank you :)
Post #1449419
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse