TFS Event Subscription

Finally got round to looking into this. Firstly, the next iteration of the 2008 Power Tools will introduce a nicer interface for the maintenance of event subscriptions. However, that is not due quite yet so in the meantime there are two tools to fill the gap.

The TfsAlert tool provides a system tray app that balloon tips whenever events change. It monitors 8 different events, and allows filtering. The interface for filtering is to edit the config file, so knowledge of syntax is required. However, that syntax can be built by the next tool.

The second tool is TFS Event Subscription and is a GUI builder for event subscription strings. This allows you to subscribe to a particular event and get a message sent by SOAP, PlainText or EmailHTML. This is a standard feature of TFS, however this interface can be run on a client and is a great deal easier to use compared to command line.

Lastly, this blog provides details on how to amend the emails sent by TFS to point to the TFS Web Access for the work item, rather than a summary report.

Common SQL Server errors and failures

Blatant link, but very useful : Common SQL Server failures and errors

Clustered SQL Server error 28000 - cluster service login error

Whilst trial testing a failover for a clustered (W2K3) SQL Server 2005, we experienced an error 28000, failing on authenticating the cluster service account. This meant that the server service did not restart under cluster manager (though it is possible to start it from command prompt or via services).

This was due to removing the BUILTIN\Administrators group from security for SQL Server. Although this is standard practice, it appears that SQL Server is unable to verify the cluster serice account via an AD group. The solution to this is to either add back the BUILTIN\Administrators account (not desired) or create a security entry for the cluster service account.

SQL 2005 replication history

One of the shortcomings of the new replication monitor is it's lack of history. SQL 2000 was a lot better at this IMO. So, to get this information you need to create a report etc using SQL along these lines:

@subscriber NVARCHAR(256) = NULL
, @dateFrom DATETIME
, @dateTo DATETIME

SELECT ms.publisher
, ms.publisher_db
, ms.subscriber
, ms.subscriber_db
, mh.comments
, mh.[time]
FROM dbo.MSmerge_subscriptions ms
INNER JOIN dbo.MSmerge_agents ma
ON ms.subscriber = ma.subscriber_name
AND ms.subscriber_db = ma.subscriber_db
INNER JOIN dbo.MSmerge_sessions mse
ON ma.id = mse.agent_id
INNER JOIN dbo.MSmerge_history mh
ON mse.session_id = mh.session_id
AND mse.agent_id = mh.agent_id
WHERE ms.subscription_type = 0
AND (ms.subscriber + ':' + ms.subscriber_db = @subscriber OR @subscriber IS NULL)
AND mh.[time] BETWEEN @dateFrom AND @dateTo


To get the subscribers :

SELECT DISTINCT subscriber + ':' + subscriber_db AS subscriber
FROM dbo.MSmerge_subscriptions
WHERE subscription_type = 0

Skip SQL Statement when user does not have permission or linked server missing

Had an interesting problem with a stored procedure that checked replication history. This SP included a local and remote server using linked server. Occasionally the link would drop, and also whenever replication was rebuilt the user would forget to set up the permissions to the re-generated distribution database. Either of these situations throw errors in the SQL, so that it doesn't complete. To save redoing the existing reports, I needed a solution whereby the remote sql is skipped if it doesn't work. The solution seemed to be to use TRY..CATCH, however there is an issue with this. TRY..CATCH does not work with errors produced over a linked server. After some head scratching it was found that this could be overcome using EXEC sp_executesql. Since the SQL will not be in a plan (it is, afterall, on a different server) then there is no performance issue here either. So, the SQL read along the lines of :

BEGIN TRY
SET @sqlstmt = 'SELECT xxx FROM remoteserver.database.dbo.yyy'
EXEC sp_executesql
END TRY
BEGIN CATCH
--ignore error from remote system
END CATCH

Reporting Services Performance Monitoring

Useful technet article on how to monitor SSRS performance report by report.

http://technet.microsoft.com/en-us/library/aa964131.aspx

Merge replication error Msg 20092, Level 16, State 1

Recently I created a (fortunately) test environment in SQL 2005 for a merge replication that was to upgrade an existing SQL 2000 installation. I didn't want to re-initialise the subscriber as there was a large amount of data involved (about 115million rows). Once the subscriber was created (and snapshot created), I tested the merge by updating a particular set of data on the publisher first and then the subscriber to check that the replication worked. Publisher update went fine, data replicated successfully. However, when I tried to update the rows at the subscriber I got an error Msg 20092, Level 16, State 1 Procedure MSmerge_disabledml....

Table 'XXXXXX' into which you are trying to insert, update, or delete data is currently being upgraded or initialized for merge replication. On the publisher data modifications are disallowed until the upgrade completes and snapshot has successfully run. On subscriber data modifications are disallowed until the upgrade completes or the initial snapshot has been successfully applied and it has synchronized with the publisher.

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.



Fortunately the solution for this is quite straight forward. It seems that somehow during the creation of the subscriber (and I admit this was done a few times) the merge replication upgrade triggers were not removed.

To correct this scenario, you must run the following SQL on the subscriber, drop the subscription and re-create the subscription again. The script is (OK I know it uses a cursor but it's a one off fix script so who cares!):

DECLARE @sql NVARCHAR(4000),
@trigger NVARCHAR(100)

DECLARE trig_curs CURSOR for
SELECT NAME
FROM sys.triggers
WHERE name LIKE 'MSmerge%' AND PARENT_CLASS=1

OPEN trig_curs

FETCH NEXT FROM trig_curs INTO @trigger

WHILE @@FETCH_STATUS = 0
BEGIN

set @sql = 'drop trigger [' + @trigger + ']'

print @sql

exec sp_executesql @sql

FETCH NEXT FROM trig_curs INTO @trigger
END

CLOSE trig_curs
DEALLOCATE trig_curs