July 29, 2015 at 11:44 pm
Hi,
I am replicating a stored procedure execution, which builds and executes the following dynamic SQL command:
IF EXISTS (select * from MyDB..sysfiles sf (nolock) where name = 'MyDB_201201')
ALTER DATABASE [MyDB] REMOVE FILE [MyDB_201201]
IF EXISTS (select * from MyDB..sysfilegroups sfg (nolock)
where groupname = 'MyDB_201201' and sfg.groupname not in(
SELECT distinct fg.name file_group_name
FROM sys.filegroups fg
LEFT JOIN sys.database_files df
ON fg.data_space_id = df.data_space_id
LEFT JOIN sys.partitions p
ON fg.data_space_id = p.partition_number
WHERE (p.object_id>4096 or p.object_id IS NULL)
and fg.name='MyDB_201201'))
ALTER DATABASE [MyDB] REMOVE FILEGROUP [MyDB_201201]
I can run this SP with no errors on both the publisher and the subscriber. However, when I try to replicate the execution of this SP, I get an error in replication monitor:
ALTER DATABASE statement not allowed within multi-statement transaction. (Source: MSSQLServer, Error number: 226)
Get help: http://help/226
How can I change my code to workaround this? Perhaps some explicit transactions?
Thanks
July 30, 2015 at 3:59 am
Try it with explicit transactions, separating the ALTER command from the others.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2015 at 9:16 pm
Grant Fritchey (7/30/2015)
Try it with explicit transactions, separating the ALTER command from the others.
I have replaced:
select @s-2 = 'USE [' + @DBName + ']
IF EXISTS (select * from ' + @DBName + '..sysfiles sf (nolock) where name = ''' + @DBName + @FileGroupSuffix + ''')
ALTER DATABASE [' + @DBName + '] REMOVE FILE [' + @DBName + @FileGroupSuffix + ']'
exec sp_executesql @s-2
select @s-2 = 'IF EXISTS (select * from ' + @DBName + '..sysfilegroups sfg (nolock) where groupname = ''' + @DBName + @FileGroupSuffix + ''' and sfg.groupname not in(
SELECT distinct fg.name file_group_name
FROM sys.filegroups fg
LEFT JOIN sys.database_files df
ON fg.data_space_id = df.data_space_id
LEFT JOIN sys.partitions p
ON fg.data_space_id = p.partition_number
WHERE (p.object_id>4096 or p.object_id IS NULL)
and fg.name=''' + @DBName + @FileGroupSuffix + '''))
ALTER DATABASE [' + @DBName + '] REMOVE FILEGROUP [' + @DBName + @FileGroupSuffix + ']'
exec sp_executesql @s-2
To:
select @s-2 = 'USE [' + @DBName + ']
IF EXISTS (select * from ' + @DBName + '..sysfiles sf (nolock) where name = ''' + @DBName + @FileGroupSuffix + ''')
BEGIN
BEGIN TRAN
ALTER DATABASE [' + @DBName + '] REMOVE FILE [' + @DBName + @FileGroupSuffix + ']
COMMIT TRAN
END'
exec sp_executesql @s-2
select @s-2 = 'IF EXISTS (select * from ' + @DBName + '..sysfilegroups sfg (nolock) where groupname = ''' + @DBName + @FileGroupSuffix + ''' and sfg.groupname not in(
SELECT distinct fg.name file_group_name
FROM sys.filegroups fg
LEFT JOIN sys.database_files df
ON fg.data_space_id = df.data_space_id
LEFT JOIN sys.partitions p
ON fg.data_space_id = p.partition_number
WHERE (p.object_id>4096 or p.object_id IS NULL)
and fg.name=''' + @DBName + @FileGroupSuffix + '''))
BEGIN
BEGIN TRAN
ALTER DATABASE [' + @DBName + '] REMOVE FILEGROUP [' + @DBName + @FileGroupSuffix + ']
COMMIT TRAN
END'
exec sp_executesql @s-2
And now getting the same error when trying to run the SP on the subscriber directly, which was not the case before.
July 31, 2015 at 4:11 am
Ah, it wasn't clear in the first post this is all dynamic SQL.
Try wrapping this in a transaction
exec sp_executesql @s-2
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 3, 2015 at 5:59 pm
Grant Fritchey (7/31/2015)
Ah, it wasn't clear in the first post this is all dynamic SQL.Try wrapping this in a transaction
exec sp_executesql @s-2
When I do this, I get the error even when the SP runs directly on the publisher and subscriber.
Msg 226, Level 16, State 6, Line 3
ALTER DATABASE statement not allowed within multi-statement transaction.
select @s-2 = 'USE [' + @DBName + ']
IF EXISTS (select * from ' + @DBName + '..sysfiles sf (nolock) where name = ''' + @DBName + @FileGroupSuffix + ''')
ALTER DATABASE [' + @DBName + '] REMOVE FILE [' + @DBName + @FileGroupSuffix + ']'
BEGIN TRAN
exec sp_executesql @s-2
COMMIT TRAN
August 3, 2015 at 8:48 pm
Trying to do all this dynamically is going to be a bear. Can you break apart the USE statement and the ALTER statement?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 4, 2015 at 6:38 pm
Grant Fritchey (8/3/2015)
Trying to do all this dynamically is going to be a bear. Can you break apart the USE statement and the ALTER statement?
Tried, but the same error happens:
select @s-2 = 'USE [' + @DBName + ']'
exec sp_executesql @s-2
select @s-2 = 'IF EXISTS (select * from ' + @DBName + '..sysfiles sf (nolock) where name = ''' + @DBName + @FileGroupSuffix + ''')
ALTER DATABASE [' + @DBName + '] REMOVE FILE [' + @DBName + @FileGroupSuffix + ']'
exec sp_executesql @s-2
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply