RE: Refreshing Views and Recompiling Stored Procs
-
There was no discussion forum for the scripts, but I'd like to express my delight and dismay pertaining to this script.
First of all, it looks like a great solution so I do not need to recompile my views inside a VB program anymore. Yeah, I never thought to invest much time into that project.
Then I realized, this script won't continue to run after incurring high error levels. XACT_ABORT OFF doesn't solve this problems. Any suggestions?
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1902
-
A small change to the script will allow you to run each recompile separately. You can also check what's actually going to run with the PRINT command before using the EXEC.
DECLARE @sqlCommand VARCHAR(4000)
SET @sqlCommand = ''
DECLARE cursor_views CURSOR FOR
SELECT [name] FROM sysobjects WHERE xtype='V'
FOR READ ONLY
OPEN cursor_views
DECLARE @name sysname
FETCH NEXT FROM cursor_views INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
SET @sqlCommand = @sqlCommand + 'PRINT ''Refreshing view: ' + @name + '''' + CHAR(13) + CHAR(10)
SET @sqlCommand = @sqlCommand + 'EXECUTE sp_refreshview ' + @name + CHAR(13) + CHAR(10)
SET @sqlCommand = @sqlCommand + 'GO' + CHAR(13) + CHAR(10)
FETCH NEXT FROM cursor_views INTO @name
END
CLOSE cursor_views
DEALLOCATE cursor_views
PRINT @sqlCommand
-- EXEC @sqlCommand
SET @sqlCommand = ''
DECLARE cursor_procs CURSOR FOR
SELECT [name] FROM sysobjects WHERE xtype='P'
FOR READ ONLY
OPEN cursor_procs
FETCH NEXT FROM cursor_procs INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
SET @sqlCommand = @sqlCommand + 'PRINT ''Recompiling proc: ' + @name + '''' + CHAR(13) + CHAR(10)
SET @sqlCommand = @sqlCommand + 'EXECUTE sp_recompile ' + @name + CHAR(13) + CHAR(10)
SET @sqlCommand = @sqlCommand + 'GO' + CHAR(13) + CHAR(10)
FETCH NEXT FROM cursor_procs INTO @name
END
CLOSE cursor_procs
DEALLOCATE cursor_procs
PRINT @sqlCommand
-- EXEC @sqlCommand
-
the limitation of characters for a varchar to 8000 doesn't help here. and you cannot use text as a local variable. Also appears you need to use sp_sqlexec. Close, but no cigar!
-
You could move the EXEC in to the cursor and change the first line starting SET @sqlCommand = @sqlCommand + 'PRINT.... to SET @sqlCommand ='PRINT...
Would this not help?
-
yeah, which removes the automation portion of it. It's still better than nothing 🙂
-
"which removes the automation portion of it."
I'm missing something here...
If the EXEC is moved in to the cursor, it will execute each one separately; it will still do them all without any need for interaction. Single failures will not stop the process. -
Oh, I am sorry! I read that wrong! I thought you meant to move the "PRINT" inside the cursor loops. If you move the EXEC inside, you return to the original problem where the script bombs when the error level is too high.
-
Here's a simple SP to refresh all views - if anyone is interested, I have another to do the recompiles........
CREATE PROCEDURE dbo.p_RefreshAllViews
as
BEGIN
SET NOCOUNT ON
DECLARE @viewName sysname
DECLARE @cmd varchar(1000)
DECLARE @error_var int
DECLARE @TotalCount int
DECLARE @ErrorCount int
DECLARE @GoodCount int
SET @GoodCount = 0
SET @ErrorCount = 0
SET @TotalCount = 0
DECLARE #curRefreshViews scroll cursor for
SELECT 'name'=TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE (TABLE_NAME like 'view_%')
OPEN #curRefreshViews
FETCH next from #curRefreshViews into @viewName
WHILE (@@fetch_status <> -1)
BEGIN
SET @TotalCount = @TotalCount + 1
SET @cmd = 'sp_refreshview ' + @viewName
EXECUTE(@cmd)
SELECT @error_var = @@ERROR
IF @error_var = 0
BEGIN
SET @GoodCount = @GoodCount + 1
END
ELSE
BEGIN
SET @ErrorCount = @ErrorCount + 1
PRINT 'ERROR. CODE: ' + cast(@error_var as varchar) + ' ON REFRESH OF VIEW: ' + @viewName
END
FETCH next from #curRefreshViews into @viewName
END
CLOSE #curRefreshViews
DEALLOCATE #curRefreshViews
PRINT 'Views Refreshed: ' + cast(@GoodCount as varchar)
PRINT 'Views With Errors: ' + cast(@ErrorCount as varchar)
PRINT 'Views Total: ' + cast(@TotalCount as varchar)
END
GO
Good luck,
Harley
-
Okay, still hovering somewhere around the original script.... This stored procedure takes a database name and output file name (for locating errors) and creates a scheduled Sql Server Agent Job. Each step on success or failure will go to the next step.
IF EXISTS (SELECT * FROM sysobjects WHERE name= 'spScheduleRecompile' AND xtype = 'P')
DROP PROCEDURE spScheduleRecompile
GO
CREATE PROCEDURE spScheduleRecompile (
@database VARCHAR(200),
@outFileName VARCHAR(200)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @rc INT
DECLARE @sqlCommand VARCHAR(400)
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @jobName VARCHAR(200)
DECLARE @stepName VARCHAR(200)
DECLARE @scheduleName VARCHAR(200)
DECLARE @scheduleDate DATETIME
DECLARE @activeStartDate CHAR(8)
DECLARE @activeStartTime CHAR(6)
-- Create Recompile Job
SET @jobName = 'RecompileViewsAndProcs_' + @database
EXEC @rc = msdb.dbo.sp_add_job
@job_Name = @jobName,
@enabled = 1,
@description = 'Recompiles All Views and Procedures in specified database',
@start_step_id = 1,
@delete_level = 3,
@job_id = @job_id OUTPUT
IF @rc <> 0
BEGIN
RAISERROR ('Recompile Job Not Created', 16, 1)
GOTO EndProc
END
DECLARE cursor_views CURSOR FOR
SELECT [name] FROM sysobjects WHERE xtype='V'
FOR READ ONLY
OPEN cursor_views
DECLARE @name sysname
FETCH NEXT FROM cursor_views INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
-- Create Job Step per recompile
SELECT @stepName = 'Refreshing view: ' + @name,
@sqlCommand = 'EXECUTE sp_refreshview ' + @name
EXEC @rc = msdb.dbo.sp_add_jobstep
@job_id = @job_id,
@step_name = @stepName,
@subsystem = 'TSQL',
@command = @sqlCommand,
@on_success_action = 3,
@on_fail_action = 3,
@database_name = @database,
@database_user_name = 'dbo',
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = @outFilename,
@flags = 2
IF @rc <> 0
BEGIN
RAISERROR (@stepName , 16, 1)
GOTO EndProc
END
FETCH NEXT FROM cursor_views INTO @name
END
CLOSE cursor_views
DEALLOCATE cursor_views
DECLARE cursor_procs CURSOR FOR
SELECT [name] FROM sysobjects WHERE xtype='P'
FOR READ ONLY
OPEN cursor_procs
FETCH NEXT FROM cursor_procs INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
-- Create Job Step per recompile
SELECT @stepName = 'Recompiling Procedure: ' + @name,
@sqlCommand = 'EXECUTE sp_recompile ' + @name
EXEC @rc = msdb.dbo.sp_add_jobstep
@job_id = @job_id,
@step_name = @stepName,
@subsystem = 'TSQL',
@command = @sqlCommand,
@on_success_action = 3,
@on_fail_action = 3,
@database_name = @database,
@database_user_name = 'dbo',
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = @outFilename,
@flags = 2
IF @rc <> 0
BEGIN
RAISERROR (@stepName , 16, 1)
GOTO EndProc
END
FETCH NEXT FROM cursor_procs INTO @name
END
CLOSE cursor_procs
DEALLOCATE cursor_procs
-- Add Job Server
EXEC @rc = msdb.dbo.sp_add_jobserver
@job_id = @job_id,
@server_name = '(local)'
IF @rc <> 0
BEGIN
RAISERROR ('Error Creating Target Server' , 16, 1)
GOTO EndProc
END
-- Add Job Schedule (2 minutes time in this case)
SELECT @scheduleName = 'RecompileViewsAndProcs_' + @database + '_Schedule',
@activeStartDate = CONVERT(CHAR(8), GETDATE(), 112),
@activeStartTime = REPLACE(CONVERT(CHAR(8), DATEADD(mi, 2, GETDATE()), 108), ':', '')
EXEC @rc = msdb.dbo.sp_add_jobschedule
@job_id = @job_id,
@job_name = NULL,
@name = 'QueueJob',
@enabled = 1,
@freq_type = 1,
@freq_interval = 1,
@freq_subday_type = 0,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_start_date = @activeStartDate,
@active_end_date = NULL,
@active_start_time = @activeStartTime,
@active_end_time = NULL
IF @rc <> 0
BEGIN
RAISERROR ('Error Creating Job Schedule' , 16, 1)
GOTO EndProc
END
EndProc:
END
GO
-------------------------------------------------------------------------
DECLARE @database VARCHAR(200)
DECLARE @outFileName VARCHAR(200)
DECLARE @createFile VARCHAR(300)
SET @database = 'database'
SET @outfileName = 'c:\logs\recompile_' + @database + '.txt'
--Create Recompile Job
EXEC spScheduleRecompile @database, @outFileName
GO
You can then search the resultant text file for "Msg" to locate any errors that occured.
-
Adrian,
To put it simply... WOW!
I will look this through when I get time later (hopefully). I hate finding short-comings in MSSQL, because they drive me nutzo as well.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply