Epic Blog of Awesome


Adventures in Database Administration: How to remove the msrepl_tran_version column from a table

When transactional replication with immediately updating subscriptions is created, a column called msrepl_tran_version is added to any tables (articles) in the publication along with a default constraint to populate it. Now if you ever remove an article from that publication or remove the publication completely, the column remains. To remove the column, first you have to remove the constraint and then the columns. The script below creates the commands to remove both.

via Adventures in Database Administration: How to remove the msrepl_tran_version column from a table.

Catalog Views Transact-SQL

Catalog views return information that is used by the SQL Server Database Engine. We recommend that you use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views.

via Catalog Views Transact-SQL.

Initializing a Merge Subscription Without a Snapshot

Initializing a Merge Subscription Without a Snapshot

via Initializing a Merge Subscription Without a Snapshot.

This comment from another developer on this technet page saved me:

Step 1 is Mandatory and if you forget it, you’ll see Error 102 at initial sync time
If you forget this first step (“1. Add a uniqueidentifier column with the rowguid and not null column attributes to the tables that will be used in merge replication.”) , you will get a nasty error…

Message: {call sp_MSsetconflicttable (N’FirstTableinPublication’, N’MSmerge_conflict_pub_pubname_fistTableinPublication’, N’publisher_name’, N’databasename’, N’publication_name)}

Number: 102
Message: Incorrect syntax near ‘FirstColumnInTable’.

Source: Merge Replication Provider
Number: -2147201001
Message: The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.

You have to add the rowguid column yourself as per Step 1, after the subscriber database is restored, on each table.

alter tableadd [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL;

Helper script to write the TSQL which you can copy/paste into subscriber connection and run before initial sync

– To find all tables and help the syntax, run this at the publisher:
select ‘alter table [' + name +'] add [rowguid] uniqueidentifier rowguidcol default newsequentialid();’ from sysmergearticles where type=10