Truncate certain tables (or in this case Error-tables)

Here’s another stored procedure which truncates certain tables (in this case *_Errors tables).
In case you’re using Fast Load on your SSIS Destination, it may occur that it fully loads that gigantic table into your Error-table.
So make sure to monitor those or truncate them every few days.


-- =============================================
-- Author: Clint Huijbers
-- Create date: 2014-08-07
-- Description: This SP is used to truncate *_Errors tables
-- =============================================
CREATE PROCEDURE [dbo].[usp_TruncateErrorTables]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @SQL VARCHAR(800) --SQL query to execute

DECLARE CursorName CURSOR FOR
SELECT 'TRUNCATE TABLE [dbo].['+Name+']' AS SQLStatement
FROM sys.tables
WHERE is_ms_shipped = 0
AND Name LIKE '%_Errors'

OPEN CursorName
FETCH NEXT FROM CursorName INTO @SQL
WHILE (@@fetch_status = 0)
BEGIN

EXEC(@SQL)

PRINT @SQL+' --- Executed'

FETCH NEXT FROM CursorName INTO @SQL
END

CLOSE CursorName
DEALLOCATE CursorName

END








Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s