June 29, 2011 at 1:32 pm
I have the stored procedure below I am working to create. If I run all the pieces individually they work. When I complie the stored procedure and call it from the exec command the creation of the archive table is skipped. The records are then deleted. The insert transaction does not throw an error or rollback.
Any ideas?
ALTER PROCEDURE [dbo].[pArchiveLogs]
(
@Days INT = 120
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Date DATETIME
SELECT@Days = (@Days * -1),
@Date = DATEADD(d, @Days, GETDATE())
IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'ArchiveTable')
BEGIN
SELECT * INTO ArchiveTable FROM Over WHERE 1 = 2
END
BEGIN TRAN
INSERT INTO ArchiveTable
SELECT *
FROM Over
WHEREinsertdatetime >= @Date
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occurred while archiving data', 16, 1)
RETURN -1
PRINT (@@ERROR)
END
DELETE FROMdbo.Over
WHEREinsertdatetime >= @Date
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
June 29, 2011 at 1:41 pm
Two points, first the critique.. Using select into in this context could cause problems if the table was changed and your sproc should include a CREATE TABLE with the full definition and since this table is persisted you should probably just create it and leave it there.
I'm guessing some wierd query plan problem but I have a way around it, if you execute the build of the table as dynamic SQL it should resolve the issue.
EXEC ( 'IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = ''ArchiveTable'') SELECT * INTO dbo.ArchiveTable FROM dbo.Over WHERE 1 = 2' )
CEWII
June 29, 2011 at 1:56 pm
I think I figured it out. The main table has an identity column and that is created when I do the SELECT INTO. Then no records are getting added because of the IDENTITY FAILURE.
So I have to find a way to dynamically create the table without the identity.
June 29, 2011 at 2:25 pm
One solution is to put the whole DDL statement to create the table in the code instead of the SELECT into. For the field with a identity, change it to just a number. Your data move from Over to Archive should work then.
Another solution is to specify exact columns in the INSERT INTO Archive (COL2, COL3, .. COLX) SELECT COL2, COL3, .. COLX.
Please note, I am skipping over the indentity field since it is incremented and updated by SQL Server.
John Miner
Crafty DBA
www.craftydba.com
June 30, 2011 at 2:52 am
There's no need for the transaction or separate insert/delete operation, and the identity issue can be resolved by using a SET option:
IFOBJECT_ID(N'dbo.pArchiveLogs', N'P')
IS NULL
EXECUTE ('CREATE PROCEDURE dbo.pArchiveLogs AS');
ALTER PROCEDURE dbo.pArchiveLogs
(
@Days INTEGER = 120
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
-- Create the archive table if necessary
IFOBJECT_ID(N'dbo.ArchiveTable'
IS NULL
BEGIN
SELECT TOP (0)
*
INTO dbo.ArchiveTable
FROM dbo.SourceTable;
END
-- Allow identity column inserts
SET IDENTITY_INSERT dbo.ArchiveTable ON;
-- Atomic delete and insert
INSERT dbo.ArchiveTable WITH (TABLOCKX)
SELECT
d.*
FROM
(
DELETE dbo.SourceTable
OUTPUT DELETED.*
WHERE insert_date_time >= DATEADD(DAY, -@Days, SYSDATETIME())
) AS d;
SET IDENTITY_INSERT dbo.ArchiveTable OFF;
END;
GO
Use real column names rather than the star syntax I used (I can't see the table definition from here).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 30, 2011 at 6:09 am
Hi Crazy Eights,
I have used the identity insert before on bulk loads when I wanted to keep the identity column the same and not change the value to a plain old number.
I do not understand the advantage of a insert/delete with a exclusive lock over two statements in a transaction.
If both statements do not complete successfully in the same transaction, I think the rollback will return the data to the previous state?
I might see the advantage of your solution if you think someone is going to execute this code more than once at the same time ...
Cheers
John
John Miner
Crafty DBA
www.craftydba.com
June 30, 2011 at 6:18 am
j.miner (6/30/2011)
I do not understand the advantage of a insert/delete with a exclusive lock over two statements in a transaction.
Hi John,
The TABLOCKX isn't required - I just added it out of habit because it may enable minimal logging with INSERT on SQL Server 2008 and above. The INSERT...DELETE combination is naturally atomic and does not require an explicit transaction. Another advantage is that it results in a single query plan whereas the two-statement approach executes twice, inevitably performing more work.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 30, 2011 at 12:41 pm
CELKO (6/30/2011)
We would have a column for the status each row instead of physically moving them. “Archived” would be one of the status values. No need for an extra table. No need for extra disk access. No need to worry about duplicated effort. And it less code.
That is certainly an option, one could also partition the table on Enterprise Edition (or use a local partitioned view on other editions), so that old data could be stored read-only on separate a file group. There might be disadvantages to keeping all the data in the same, unpartitioned, table.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply