Dropping Replicated Columns For SQL Server 2000

I'm sure all of us have tried, at some point, to delete a column from a table on a replicated database in Enterprise Manager only to be greeted with an error message:

Error 5074: The object 'DF__SomeTable__somec__71CF15EB' is dependant on column 'somecolumn'.
ALTER TABLE DROP COLUMN somecolumn failed because one or more objects access this column.

So you figure, I'll just delete the constraint and everything will be OK. But wait, that constraint is on every single replicated server, so you'll have to delete it there too. If there is a lot of latency between you and the other node, it is like waiting for a Christmas that will never come. Oh yeah, spoiler alert: Added bonus - Each one is named slightly differently. So while you may delete it from your main server, none of the syncs are happening or at least the snapshots are failing because you're missing a constraint that everyone has but you. If only there were some way to programaticly determine the randomly-named constraint and delete it. That's where I was today, so I hacked this together for Microsoft SQL Server 2000:

Use YourDatabaseHere
GO

-- Ad-hoc queries require this because sysobjects is just that important.
SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
GO

DECLARE @parent_id INT
DECLARE @table_name NVARCHAR(255)
DECLARE @column_name NVARCHAR(255)

SELECT @table_name = 'SomeTable'
SELECT @column_name = 'somecolumn'
SELECT @parent_id = (SELECT sysobjects.id FROM sysobjects WHERE sysobjects.name = @table_name AND parent_obj = 0)

DELETE FROM sysobjects
WHERE parent_obj = @parent_id
AND (SELECT syscolumns.name FROM syscolumns WHERE syscolumns.id = @parent_id
AND syscolumns.colid = (SELECT sysconstraints.colid FROM sysconstraints
WHERE sysconstraints.id = @parent_id AND sysconstraints.constid = sysobjects.id)) = @column_name
AND xtype = 'D'
GO

-- Turn it off when we're done.
SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE
GO

If you are not familiar with system tables, I heartily recommend you check them out and learn them. Very handy for doing tricky things like this and it will make the above script seem less like magic and will lessen the chances you break something.

For SQL Server 2000: http://msdn2.microsoft.com/en-us/library/aa260604(SQL.80).aspx

For SQL Server 2005: http://msdn2.microsoft.com/en-us/library/ms179932.aspx

If you ARE familiar with system tables, and you find an error in the above script, please let me know in the harshest way possible, so I wont forget (within reason).

Now to run this command on all subscriber databases... For more info on that, check out this link:
http://doc.ddart.net/mssql/sql2000/html/replsql/replimpl_5t9d.htm

Replication Procedures Accidently Added to Publication

We have merge replication configured for about 20 field computers, and 4 servers on our local network. While trying to fix a problem, all articles on the distributor were added to a publication. This includes about 700 procedures generated by SQL server. We weren't able to uncheck them once the changes were applied, probably because the snapshot was created and to uncheck them would invalidate the snapshot.

This caused a lot of problems because we couldn't create any more snapshots because we kept getting the error:
Cannot drop replicataed procedure 'sp_sel_BD87AFD5144A4C7FEE4B86FD60AC49F3_pal' because it is being used for replication.

So I had to write this script to remove them from the publication programaticly:

DECLARE @proc_name NVARCHAR(100)
DECLARE @pub_name NVARCHAR(100)

SET @pub_name = 'PublicationName'

DECLARE cGetRepProcs CURSOR FOR
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND ROUTINE_NAME LIKE '%_%_pal'

OPEN cGetRepProcs
FETCH NEXT FROM cGetRepProcs INTO @proc_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_dropmergearticle @pub_name, @proc_name,1,0,1

FETCH NEXT FROM cGetRepProcs INTO @proc_name
END

CLOSE cGetRepProcs
DEALLOCATE cGetRepProcs

Failed to enumerate changes in the filtered articles.

If you have Microsoft SQL servers setup for merge replication and you can't sync from the enterprise manager because of the following error:

Failed to enumerate changes in the filtered articles.

Then try syncing from the command line using replmerg. Not only does it, strangely, have a higher chance of working correctly, but it also shows you valuable information that can be used for debugging. To use replmerg, right click on the publication and go to Agent Properties. Then click the Steps tab and there should be a job called "Run agent.". Select that step and click Edit. There should be a long line of text in the command box. Highlight it all and copy it (ctrl+a, ctrl+c), then open a command prompt (windows key+r, cmd) and issue this command:

cd \Program Files\Microsoft SQL Server\80\COM

Now type: replmerg and then right click on the command prompt to paste. If you can't paste, go to the command prompt properties (alt+space, p) and make sure Quick Edit is checked (q) then apply and try again.