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.