I need help with my SQL script and how it performs transaction rollbacks if I get an error in my cursor. Does the data commit after each fetch or something? It appears that if something breaks in my routine that the updates performed do not get rolled back. Can anyone see a problem with how I have this set up that it won't work? Thanks
regards,
Brian
Code:
CREATE proc sp__DeployCMSContent
@job varchar(50) = ' ',
@deployedBy varchar(100) = ' '
as
begin
begin tran
DECLARE @tableName sysname,
@queueSql varchar(1000),
@keyValue int,
@keyName varchar(50),
@errorcode int
select @errorcode = 0
DECLARE deploy_cursor CURSOR FOR
SELECT table_name FROM information_schema.tables
where lower(table_catalog) = 'petroferm'
and lower(table_type) = 'base table'
and left(lower(table_name),7) = 'tbltest'
and right(lower(table_name),5) <> '_live'
and right(lower(table_name),4) <> '_bak'
and right(lower(table_name),9) <> '_bak_live'
OPEN deploy_cursor
FETCH NEXT FROM deploy_cursor
INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
select @keyName = dbo.fn__GetPrimaryKey(@tableName)
-- load all records pending deployment into our deployment queue table
print '--load all records pending deployment into our deployment queue table'
select @queueSql = 'insert into DeploymentQueue (TableName, KeyName, KeyNameValue, RecordAction, JobName, DeploymentDate, DeployedBy, ActiveFlag) '
select @queueSql = @queueSql + 'select ''' + @tableName + ''', ''' + @keyName + ''', ' + @keyName + ', '
select @queueSql = @queueSql + 'case MarkedForDeletion when 1 then ''DELETE'' else ''INSERT'' end '
select @queueSql = @queueSql + ', ''' + @job + ''', getdate(), ''' + @deployedBy + ''', 1 from ' + @tableName + ' where UPPER(WFStatus) = ''PENDING DEPLOYMENT'''
print @queueSql
print ' '
exec(@queueSql)
-- delete all records from the live table where they exist for the current deployment batch job in our deployment queue table
print '--delete all records from the live table where they exist for the current deployment batch job in our deployment queue table'
select @queueSql = 'delete ' + @tableName + '_LIVE from DeploymentQueue q, ' + @tableName + '_LIVE l where q.KeyNameValue = l.' + @keyName
select @queueSql = @queueSql + ' and q.JobName = ''' + @job + ''' and q.TableName = ''' + @tableName
select @queueSql = @queueSql + ''' and UPPER(q.RecordAction) in (''DELETE'',''INSERT'') and q.ActiveFlag = 1'
print @queueSql
print ' '
exec(@queueSql)
-- deploy all records marked with a RecordAction of [insert] in our deployment queue table to the live tables
print '--deploy all records marked with a RecordAction of [insert] in our deployment queue table to the live tables'
select @queueSql = 'insert into ' + @tableName + '_LIVE select cms.* from ' + @tableName + ' cms, DeploymentQueue q where q.KeyNameValue = cms.' + @keyName
select @queueSql = @queueSql + ' and q.JobName = ''' + @job + ''' and q.TableName = ''' + @tableName
select @queueSql = @queueSql + ''' and UPPER(q.RecordAction) = ''INSERT'' and q.ActiveFlag = 1'
print @queueSql
print ' '
exec(@queueSql)
-- update records on current LIVE table. We will reset some of the values since they do not apply in that environment
print 'update records on current LIVE table. We will reset some of the values since they do not apply in that environment'
select @queueSql = 'update ' + @tableName + '_LIVE set '
select @queueSql = @queueSql + 'MarkedForDeletion = 0, '
select @queueSql = @queueSql + 'WFStatus = ''LIVE'', '
select @queueSql = @queueSql + 'DeploymentJobID = NULL '
select @queueSql = @queueSql + 'from DeploymentQueue q, ' + @tableName + '_LIVE l '
select @queueSql = @queueSql + 'where q.KeyNameValue = l.' + @keyName
select @queueSql = @queueSql + ' and q.TableName = ''' + @tableName + ''''
select @queueSql = @queueSql + ' and q.JobName = ''' + @job + ''''
select @queueSql = @queueSql + ' and UPPER(q.RecordAction) = ''INSERT'''
select @queueSql = @queueSql + ' and q.ActiveFlag = 1'
print @queueSql
print ' '
exec(@queueSql)
-- reset the current cms table settings now that we have deployed the changes to the live website instance.
print 'reset the current cms table settings now that we have deployed the changes to the live website instance.'
print ' '
-- delete all records from the current CMS table. We do not need these any longer since we deployed them.
print '--delete all records from the current CMS table. We do not need these any longer since we deployed them.'
select @queueSql = 'delete ' + @tableName + ' from DeploymentQueue q, ' + @tableName + ' cms where q.KeyNameValue = cms.' + @keyName
select @queueSql = @queueSql + ' and q.JobName = ''' + @job + ''' and q.TableName = ''' + @tableName
select @queueSql = @queueSql + ''' and UPPER(q.RecordAction) = ''DELETE'' and q.ActiveFlag = 1'
print @queueSql
print ' '
exec(@queueSql)
-- update all records on current CMS table. We will reset them to where they can now be modified by someone else with a different deployment job
print 'update all records on current CMS table. We will reset them to where they can now be modified by someone else with a different deployment job'
select @queueSql = 'update ' + @tableName + ' set '
select @queueSql = @queueSql + 'MarkedForDeletion = 0, '
select @queueSql = @queueSql + 'WFStatus = ''LIVE'', '
select @queueSql = @queueSql + 'DeploymentJobID = NULL '
select @queueSql = @queueSql + 'from DeploymentQueue q, ' + @tableName + ' cms '
select @queueSql = @queueSql + 'where q.KeyNameValue = cms.' + @keyName
select @queueSql = @queueSql + ' and q.TableName = ''' + @tableName + ''''
select @queueSql = @queueSql + ' and q.JobName = ''' + @job + ''''
select @queueSql = @queueSql + ' and UPPER(q.RecordAction) = ''INSERT'''
select @queueSql = @queueSql + ' and q.ActiveFlag = 1'
print @queueSql
print ' '
exec(@queueSql)
FETCH NEXT FROM deploy_cursor
INTO @tableName
END
CLOSE deploy_cursor
DEALLOCATE deploy_cursor
print 'Transfer our deployment records into a history table that can be archived. We only keep the current information in our queue table.'
insert into DeploymentQueueHistory select * from DeploymentQueue where JobName = @job and ActiveFlag = 1
print 'Updating the history table with the final deployment date of the job'
update DeploymentQueueHistory set DeploymentDate = getdate() where JobName = @job
print 'Remove the queue records for the current job'
delete from DeploymentQueue where JobName = @job and ActiveFlag = 1
if (@@error <> 0)
begin
rollback tran -- rollback all changes if our deployment failed at any time.
end
else
begin
commit tran -- commit our transaction if our deployment was successful
end
endregards,
Brian
