SQLTeam.com | Weblogs | Forums

ALTER DATABASE statement not allowed within multi-statement transaction


#1

Hi,

I am new to MSSQL and have a problem with some code i am trying to use to cleanup some tables for a current system i am trying to administer.

I am getting the following error:

(1 row(s) affected)
Msg 226, Level 16, State 6, Procedure usp_ErrorMultiUser, Line 3
ALTER DATABASE statement not allowed within multi-statement transaction.

I am running the following code (which is removing records from a table, less than a specific date). it is a transaction within a try / catch block

the code is as follows:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[PS_IDEAL_TEST].[dbo].[CommunicationErrorNotification]') AND TYPE IN (N'U'))
DROP TABLE [PS_IDEAL_TEST].[dbo].[CommunicationErrorNotification];
GO

-- check stored procedure does not exist.
IF OBJECT_ID ('usp_GetErrorInfo', 'P') IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create a stored procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage INTO [PS_IDEAL_TEST].[dbo].[CommunicationErrorNotification];
GO

BEGIN TRY

BEGIN TRANSACTION
USE PS_IDEAL_TEST;

--BELOW CODE REPLACED WITH STORED PROCEDURE (WHICH DOES THIS CODE)
--ALTER DATABASE PS_IDEAL_TEST
--SET SINGLE_USER
--WITH ROLLBACK IMMEDIATE;
EXECUTE usp_SingleUser;

drop table CommunicationKeysToRemove;

declare @ArchiveDatabase as nvarchar(max) = N'Archive_PS.dbo.'
declare @EndTime as SmallDateTime = DATEADD(week, -60, CONVERT(date, getdate()))
declare @ArchiveSql as nvarchar(max)
declare @RowsAffected as int
declare @BatchSize as int = 1000000
declare @StrMsg nvarchar(100)
declare @RowsDeleted as int
declare @InPlace as bit = 1
declare @TruncateJourneyHistory as bit = 0

--EXECUTE usp_Housework;
--- Housework
set @StrMsg = format(getdate(),'yyyy/MM/dd HH:mm:ss','en-US') + ' Analysing...'
RAISERROR (@StrMsg, 0, 1) WITH NOWAIT

set @RowsAffected =
(select count(*) as RowsAffected
from Communications_test
where Communications_test.CommunicationTime < @EndTime
)

set @StrMsg = format(getdate(),'yyyy/MM/dd HH:mm:ss','en-US') + ' Found ' + format(@RowsAffected,'N0','en-US') + ' Communications_test'
RAISERROR (@StrMsg, 0, 1) WITH NOWAIT

set @StrMsg = format(getdate(),'yyyy/MM/dd HH:mm:ss','en-US') + ' Adding archive columns'
RAISERROR (@StrMsg, 0, 1) WITH NOWAIT

ALTER TABLE Communications_test ADD Archive bit NOT NULL CONSTRAINT DF_Communications_Archive DEFAULT(0)

set @StrMsg = format(getdate(),'yyyy/MM/dd HH:mm:ss','en-US') + ' Recording Communications_test To Remove...'
RAISERROR (@StrMsg, 0, 1) WITH NOWAIT

select Communications_test.CommunicationKey
into CommunicationKeysToRemove
from Communications_test
where Communications_test.CommunicationTime < @EndTime

set @StrMsg = format(getdate(),'yyyy/MM/dd HH:mm:ss','en-US') + ' Saved ' + format(@@ROWCOUNT,'N0','en-US') + ' Communications_test'
RAISERROR (@StrMsg, 0, 1) WITH NOWAIT

set @StrMsg = format(getdate(),'yyyy/MM/dd HH:mm:ss','en-US') + ' Indexing Communications_test To Remove...'
RAISERROR (@StrMsg, 0, 1) WITH NOWAIT

CREATE UNIQUE CLUSTERED INDEX [PK_CommunicationKey] ON CommunicationKeysToRemove ([CommunicationKey] ASC)

--EXECUTE usp_Communications;

set @StrMsg = format(getdate(),'yyyy/MM/dd HH:mm:ss','en-US') + ' Flagging Communications_test...'
RAISERROR (@StrMsg, 0, 1) WITH NOWAIT

set @ArchiveSql =
'update Communications_test WITH (TABLOCK) ' +
'set Archive = 1 ' +
'from Communications_test ' +
'inner join CommunicationKeysToRemove AS CommunicationKeysToRemove on CommunicationKeysToRemove.CommunicationKey = Communications_test.CommunicationKey'
exec sp_executesql @ArchiveSql

set @StrMsg = format(getdate(),'yyyy/MM/dd HH:mm:ss','en-US') + ' Archiving Communications_test...'
RAISERROR (@StrMsg, 0, 1) WITH NOWAIT

set @ArchiveSql =
'select Communications_test.* ' +
'into ' + @ArchiveDatabase + 'Archive_' + format(@EndTime,'yyyyMMdd','en-US') + '_Communications_test ' +
'from Communications_test ' +
'where Communications_test.Archive = 1'
exec sp_executesql @ArchiveSql

if @InPlace = 0
begin
set @StrMsg = format(getdate(),'yyyy/MM/dd HH:mm:ss','en-US') + ' Disabling Indexes on Communications_test...'
RAISERROR (@StrMsg, 0, 1) WITH NOWAIT

set @ArchiveSql =
'ALTER INDEX ALL ON Communications_test DISABLE; ' +
'ALTER INDEX PK_Communications ON Communications_test REBUILD'
exec sp_executesql @ArchiveSql
end

set @StrMsg = format(getdate(),'yyyy/MM/dd HH:mm:ss','en-US') + ' Deleting Communications_test...'
RAISERROR (@StrMsg, 0, 1) WITH NOWAIT

set @RowsDeleted = 0
set @RowsAffected = @BatchSize
while @RowsAffected = @BatchSize
begin
set @ArchiveSql =
'delete top (' + cast(@BatchSize as varchar(20)) + ') Communications_test WITH (TABLOCK) ' +
'from Communications_test ' +
'where Archive = 1'
exec sp_executesql @ArchiveSql

set @RowsAffected = @@ROWCOUNT
if (@RowsAffected > 0)
begin
set @RowsDeleted = @RowsDeleted + @RowsAffected
set @StrMsg = format(getdate(),'yyyy/MM/dd HH:mm:ss','en-US') + ' Deleted ' + format(@RowsDeleted,'N0','en-US') + ' rows'
RAISERROR (@StrMsg, 0, 1) WITH NOWAIT
end
end

if @InPlace = 0
begin
set @StrMsg = format(getdate(),'yyyy/MM/dd HH:mm:ss','en-US') + ' Rebuilding Indexes on Communications_test...'
RAISERROR (@StrMsg, 0, 1) WITH NOWAIT

set @ArchiveSql =
'ALTER INDEX ALL ON Communications_test REBUILD'
exec sp_executesql @ArchiveSql
end

set @StrMsg = format(getdate(),'yyyy/MM/dd HH:mm:ss','en-US') + ' Removing archive columns'
RAISERROR (@StrMsg, 0, 1) WITH NOWAIT

ALTER TABLE Communications DROP COLUMN Archive
ALTER TABLE Communications_test DROP CONSTRAINT DF_Communications_Archive

--BELOW CODE REPLACED WITH STORED PROCEDURE (WHICH DOES THIS CODE)
--ALTER DATABASE PS_IDEAL_TEST
--SET MULTI_USER;
EXECUTE usp_MultiUser;
COMMIT TRANSACTION

END TRY

BEGIN CATCH

--store error
EXECUTE usp_GetErrorInfo;

--BELOW CODE REPLACED WITH STORED PROCEDURE (WHICH DOES THIS CODE)
--ALTER DATABASE PS_IDEAL_TEST
--SET MULTI_USER;
EXECUTE usp_ErrorMultiUser;
ROLLBACK TRANSACTION

-- SEND ERROR CODE BACK TO THE sqlcmd??????????????????

END CATCH

as you can see i have tried to put the set single user and multi user into sproc's to see if i could get it to work but to no avail?

the error complains about the multi user sproc but i'm a bit confused as to why it keeps complaining? Any help would be appreciated and i am new to this, so if it is in the wrong forum i apologise.

thanks


#2

From Books Online: "The ALTER DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.


#3

Try this:

EXEC('ALTER DATABASE PS_IDEAL_TEST SET MULTI_USER;')