SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Refresh All Views


Refresh All Views

Author
Message
abmore
abmore
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1307 Visits: 931
Comments posted to this topic are about the item Refresh All Views
Mauve
Mauve
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1982 Visits: 2059
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.
Attachments
RefreshAllViews.txt (175 views, 1.00 KB)
Rao.V
Rao.V
SSChasing Mays
SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)

Group: General Forum Members
Points: 605 Visits: 692
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.
Mauve
Mauve
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1982 Visits: 2059
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.
Ahmed Omara
Ahmed Omara
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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
*/
Derek Robinson
Derek Robinson
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 116
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
thornig
thornig
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 30
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
Derek Robinson
Derek Robinson
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 116
Have you tried calling the proc recursively in the catch block with a parameter indicating the progress?
thornig
thornig
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 30
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
nirav4343
nirav4343
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
You are Fantastic.... This is the best solution I ever got... Smile
Thank you Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search