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