Database Reference
In-Depth Information
constraint PK_ETLStatuses
primary key clustered (ProcessID)
)
Go
-- Mark procedure to run on SQL Server Startup
exec sp_procoption N'CreateETLStatusesTable', 'startup', 'on'
Listing 12-22 shows a possible implementation of the procedure that performs one of the stages of ETL
processing using the
ETLStatuses
table to validate process state information.
Listing 12-22.
Creating a table in tempdb with the startup stored procedure
-- Either defined in user db or in tempdb
create proc dbo.ETL_Process1Stage2
as
begin
-- Returns
-- 0: Success
-- -1: ETL tables do not exist - something is wrong
-- -2: ETLStatuses table does not have the record for the process
-- -3: Invalid stage
set xact_abort on
declare
@StageNo smallint
,@Status varchar(16)
if object_id(N'tempdb.dbo.ETLStatuses') is null or object_id(N'tempdb.dbo.ETLData') is null
return -1
select @StageNo = StageNo, @Status = [Status]
from tempdb.dbo.ETLStatuses
where ProcessId = 1
if @@rowcount = 0
return -2
if @StageNo <> 1 or @Status <> 'COMPLETED'
return -3
-- This implementation rolls back all the changes in case of the error
-- and throw the exception to the client application.
Begin tran
update tempdb.dbo.ETLStatuses
set ActivityTime = getutcdate(), StageNo = 2, [Status] = 'STARTED'
where ProcessId = 1
/* Processing */
update tempdb.dbo.ETLStatuses
set ActivityTime = getutcdate(), [Status] = 'COMPLETED'
where ProcessId = 1
commit
return 0
end
