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