September 1, 2015 at 8:40 am
I did not look at this code for weeks and it worked fine now I'm getting errors.
-- USE PrismDataArchive
-- EXEC usp_ArchivetblCall
-- DROP PROCEDURE usp_ArchivetblCall
--CREATE PROCEDURE usp_ArchivetblCall
--AS
DECLARE @TableName VARCHAR (50)
DECLARE @BatchSize int
DECLARE @ArchiveYear CHAR(4)
DECLARE @RowCount int
SET @TableName = 'tblCall'
SET @BatchSize = (SELECT BatchSize FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = @TableName)
SET @ArchiveYear = (SELECT ArchiveYear FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = @TableName)
DECLARE @Call_Date Date
SET @Call_Date = (SELECT TOP 1 Call_date
FROM [PrismDataSource].[dbo].[tblCall] tc)
DECLARE @Active_Indicator bit
SET @Active_Indicator =
COALESCE((SELECT 1 FROM PrismDataArchive.dbo.ArchiveDriver
WHERE Active_Indicator = 1 AND TableName = @TableName),0)
WHILE @BatchSize > 0 AND @TableName = 'tblCall' AND @Active_Indicator = 1
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
IF @Call_Date IS NULL
BEGIN
DELETE TOP (@BatchSize) FROM tc
OUTPUT DELETED.* INTO PrismDataArchive.dbo.tblCallArchive
FROM [PrismDataSource].[dbo].[tblCall] tc
WHERE tc.Call_Date IS NULL
END
ELSE
BEGIN
DELETE TOP (@BatchSize) FROM tc
OUTPUT DELETED.* INTO PrismDataArchive.dbo.tblCallArchive
FROM [PrismDataSource].[dbo].[tblCall] tc
WHERE tc.Call_Date >= DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0) --jan 1 of the year of the date param passed.
AND tc.Call_Date < DATEADD(yy,1,DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0)) --add one year:
END
SET @BatchSize = @@ROWCOUNT; -- capture number of rows affected
IF @BatchSize > 0
BEGIN
INSERT INTO ArchiveLog (TableName,ArchiveYear, BatchSize)
VALUES (@TableName,@ArchiveYear,@BatchSize)
END
--COMMIT TRANSACTION;
END TRY
BEGIN CATCH
--ROLLBACK TRANSACTION;
END CATCH
END
------------------------------------------------------------------------------------------------------
--For testing remove before promotion to production
--USE PrismDataSource
--GO
--SELECT DATEPART(yyyy,Call_Date) AS Call_Date, COUNT(*) AS RecordCount -- 1999 --7967
--FROM tblCall WITH (NOLOCK)
----WHERE CALL_Date < '1900-01-01'
--GROUP BY DATEPART(yyyy,Call_Date)
--ORDER BY Call_Date
--SELECT *
--FROM tblCall WITH (NOLOCK)
--WHERE CALL_Date < '1900-01-01'
--GROUP BY DATEPART(yyyy,Call_Date)
----ORDER BY Call_Date
--USE PrismDataArchive
--GO
--SELECT DATEPART(yyyy,Call_Date) AS Call_Date, COUNT(*) AS RecordCount
--FROM tblCallArchive WITH (NOLOCK)
--GROUP BY DATEPART(yyyy,Call_Date)
--ORDER BY Call_Date
--SELECT *
--FROM tblCallArchive WITH (NOLOCK)
----WHERE CALL_Date = '1899-12-30 00:00:00.000'
--SELECT *
--FROM ArchiveLog
-- TRUNCATE TABLE ArchiveLog
-------------------------------------------------------------------------------------------------------
Do you see what I am doing wrong with my try catch?
I never got an error and it worked properly.
I have another Another archive procedure and I'm getting the same error.
I commented out the Commit and Rollback for testing purposes and everything worked fine.
I'm also getting blocking?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 1, 2015 at 9:07 am
I did a repair and it appears that it is working.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 1, 2015 at 9:11 am
The archive procedure for the 1st table works but the second one is blocking it self?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 1, 2015 at 10:32 am
Is there a reason that the COMMIT and ROLLBACK are commented out in your original post?
September 1, 2015 at 10:38 am
For testing purposes.
I did not want have to restore the Database after each test.
The problem is resolved.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 1, 2015 at 10:47 am
Welsh Corgi (9/1/2015)
For testing purposes.I did not want have to restore the Database after each test.
The problem is resolved.
Well, that is going to cause an issue as you have a BEGIN TRANSACTION still being executed in the code during testing. To save restoring the database, make a copy of the table using another name, and use it to restore the deleted data between tests.
September 1, 2015 at 10:56 am
Issue resolved.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 1, 2015 at 11:15 am
Welsh Corgi (9/1/2015)
Issue resolved.
Right, you did a repair (what ever that is).
September 1, 2015 at 1:05 pm
DBCC CHECKDB
( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 1, 2015 at 1:28 pm
Welsh Corgi (9/1/2015)
DBCC CHECKDB( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
That tells me you had data corruption in your database and that you may have lost data running the DBCC CHECKDB using REPAIR_ALLOW_DATA_LOSS. This should be a recovery scenario used as a last resort should you have no database backups or your backups are corrupt.
September 1, 2015 at 1:48 pm
CheckDB cannot fix code problems, and unless the errors being thrown were severity 24, probably did nothing at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2015 at 2:45 pm
Lynn Pettis (9/1/2015)
Welsh Corgi (9/1/2015)
DBCC CHECKDB( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
That tells me you had data corruption in your database and that you may have lost data running the DBCC CHECKDB using REPAIR_ALLOW_DATA_LOSS. This should be a recovery scenario used as a last resort should you have no database backups or your backups are corrupt.
It is in Development not Production.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 1, 2015 at 2:52 pm
GilaMonster (9/1/2015)
CheckDB cannot fix code problems, and unless the errors being thrown were severity 24, probably did nothing at all.
I appreciate the input.
The code was working fine and I had not any changes.
The problem went away after I ran the repair.
Regards.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 1, 2015 at 4:27 pm
Then someone else changed something, or you changed something that you didn't think relevant. CheckDB CANNOT have fixed the error you posted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2015 at 6:13 pm
GilaMonster (9/1/2015)
Then someone else changed something, or you changed something that you didn't think relevant. CheckDB CANNOT have fixed the error you posted.
I suspected that someone changed something. I did not tough the code for weeks and all of a sudden it bombed out.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply