TFS2010 View and unsubscribe another users alerts

Unfortunately in TFS2010 there is on UI that allows you to view and remove another users post. The issue I had was that a user left, however emails were still being sent from TFS to his account.

The solution - a bit messy.

To view all alerts in a collection:

USE [Tfs_DefaultCollection]

-- Find all subscriptions
SELECT * FROM tbl_EventSubscription ORDER BY Address

*Note that the USE will need to reflect the correct collection database name


Next to unsubscribe. The follow SQL will create a command line to do that :

-- Create script to unsubscribe subscriptions
SELECT 'bissubscribe /unsubscribe /id ' + CAST(id as varchar(255))+ ' /collection http://tfs-server-name:8080/tfs/collection-name'
FROM dbo.tbl_EventSubscription
WHERE Address = 'someuser@mycompany.com'

*Note change tfs-server-name to your TFS server name, and collection-name to reflect the collection you are interested in.


Next, remote onto the TFS server. Open an elevated command line prompt. cd to the TFS installation folder \Tools.
Paste in the result line from the SQL above into the command line prompt and execute (or create a batch file, copy over to TFS server and execute it) .

Now the alert should be removed from the system.

Transactional replication, identity and NOT FOR REPLICATION

Ouch.

Here's the scenario. Have a database server (call it DBS-A, DB1) transactionally replicating to another server (DBS-B, DB1). The tables have identities on them as the unique index (to save space, and as DBS-B is readonly have no problems with duplicate data). All works fine.

So now have another server (DBS-C, DB2) replicating data into DBS-A (into another database DB2) using transactional replication. However, the replication is non standard, in that the sp_MSins_xxxx routine has been amended. This change calls a stored procedure which copies data in the DBS-A, DB1 database. This is a SELECT..INSERT type of affair which works perfectly when called from SSMS or via an application.

However, SQL Server thinks that this data is being created by replication, so obeys the NOT FOR REPLICATION flag on the IDENTITY field, and subsequently returns a not null error, expecting a value etc.

What is annoying is that when you add a table into a replication article, it automatically adds NOT FOR REPLICATION onto any IDENTITY fields and I can not see how to remove this via the UI.

After some investigation I found the following system stored procedure which will do this for you:

DECLARE @tableID INT


SELECT @tableID = object_id('myTable')
EXEC sys.sp_identitycolumnforreplication @tableID, 0

Nice !


Problem 2...
Now this seems to work perfectly on empty tables, however to my horror I found that when I tried it on a populated table (over 1million rows) that the log reader blew up with :

The process could not execute 'sp_replcmds' on 'mydatabase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)

Get help: http://help/MSSQL_REPL20011

The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {000017d6:000016d7:00b9}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
Get help: http://help/18805

The process could not execute 'sp_replcmds' on 'mydatabase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

Arrrrgggh !
This was traced to be definitely the table that I changed, as the error disappeared once the article was dropped.

After struggling through dropping and recreating the replication several times, the (unconfirmed as I don't want to try it again) solution appeared to be an order thing:

• Create an empty table with the same schema as the one you want to add to the replication
• Add empty table to replication
• Remove NOT FOR PUBLICATION using above stored procedure call
• Populate the data from full table to empty table
• Move any foreign keys over from referencing tables

That seems to work.