June 6, 2005 at 1:20 am
Hi All,
Just trying to log an error, can't seem to get it going.
All i want to do is get the names of all views that are working. If in case, it's not working, then log the error and the view name.
Sounds simple right?. First, I was thinking that I can suppress errors, found out that I can't. So I created a permanent table where I log view names and row returned or view name plus error and error number.
When it gets to 'SELECT * FROM TempTable1' and if there are errors, (e.g. an invalud view) then it raises an error, which is ok, but then the error doesn't get logged in the permanent table.
thanks for any help/insights.
cheers!
CREATE PROCEDURE [dbo].[GetViewsThatReturnRecords]
AS
SET NOCOUNT ON
--CREATE TABLE #TempTable1 (VwName Varchar(250) PRIMARY KEY, RecordCount INT)
CREATE TABLE #TempTable (row_id INT identity (1,1), ViewName VARCHAR(50) PRIMARY KEY)
INSERT INTO #TempTable (ViewName) (SELECT Table_Name FROM Information_Schema.Views WHERE SUBSTRING(Table_Name,1,3) <> 'sys')
DECLARE @Lookup VARCHAR(50)
DECLARE @i INT
DECLARE @max-2 INT
DECLARE @sql NVARCHAR(250)
DECLARE @RecordCount BIGINT
DECLARE @myerror VARCHAR(100)
DECLARE @ErrNum INT
DECLARE @ErrNumT INT
SET @max-2 = @@rowcount
SET @i = 1
DELETE FROM TempTable1
WHILE @i <= @max-2
BEGIN
SELECT @Lookup = ViewName FROM #TempTable WHERE row_id = @i
SET @Sql = 'Select @count = count (*) From ' + @LookUp
EXEC sp_executesql @Sql, N' @count bigint output', @RecordCount OUTPUT
SELECT @ErrNum = @@Error
IF @ErrNum > 0
BEGIN
GOTO ErrorEncountered
END
ELSE
BEGIN
INSERT INTO TempTable1 (VwName,RecordCount) VALUES (@Lookup,@RecordCount)
SELECT @ErrNum = @@Error
IF @ErrNum > 0
GOTO ErrorEncountered
END
GotoToNextView:
SET @i = @i + 1
END
SELECT * FROM TempTable1
SELECT @ErrNum = @@Error
IF @ErrNum > 0
GOTO ErrorEncountered
ErrorEncountered:
IF @ErrNum > 0
BEGIN
SELECT @myerror = (SELECT description FROM master..sysmessages WHERE error = @ErrNum)
SELECT @ErrNumT = @ErrNum
SET @ErrNum = 0
INSERT INTO TempTable1 (VwName,RecordCount) VALUES (@Lookup + 'Error: ' + @myerror, @ErrnumT)
SELECT @ErrNum = @@Error
IF @ErrNum > 0
BEGIN
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
RETURN @ErrNum
END
GOTO GotoToNextView
END
GO
June 9, 2005 at 8:00 am
This was removed by the editor as SPAM
September 28, 2005 at 6:39 pm
Hi All,
Just want to close this issue. I've since realized that what I'm trying to do isn't possible, (trying to suppress error)
Thanks anyway.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply