Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

Pitfalls with Merge Replication in a SQL 2000 Environment

, 12 Jun 2007
This article covers pitfalls found while doing a merge replication in SQL 2000
pitfalls_in_sql2000.zip
<!--------------------------------------------------------------------------->  
<!--                           INTRODUCTION                                

 The Code Project article submission template (HTML version)

Using this template will help us post your article sooner. To use, just 
follow the 3 easy steps below:
 
     1. Fill in the article description details
     2. Add links to your images and downloads
     3. Include the main article text

That's all there is to it! All formatting will be done by our submission
scripts and style sheets. 

-->  
<!--------------------------------------------------------------------------->  
<!--                        IGNORE THIS SECTION                            -->
<HTML>
<HEAD>
<TITLE>Pitfalls when doing a Merge Replication within a SQL 2000 environment</TITLE>
<Style>
BODY, P, TD { font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10pt }
H2,H3,H4,H5 { color: #ff9900; font-weight: bold; }
H2 { font-size: 13pt; }
H3 { font-size: 12pt; }
H4 { font-size: 10pt; color: black; }
PRE { BACKGROUND-COLOR: #FBEDBB; FONT-FAMILY: "Courier New", Courier, mono; WHITE-SPACE: pre; }
CODE { COLOR: #990000; FONT-FAMILY: "Courier New", Courier, mono; }
</STYLE>
<LINK REL="stylesheet" type=text/css HREF="http://www.codeproject.com/styles/global.css" />
</HEAD>
<BODY BGCOLOR="#FFFFFF" color=#000000>
<!--------------------------------------------------------------------------->  


<!-------------------------------     STEP 1      --------------------------->
<!--  Fill in the details (CodeProject will reformat this section for you) -->

<PRE>
Title:       Pitfalls with System Seperation using Merge Replication in SQL 2000
Author:      Richard L. McCutchen 
Email:       richard@psychocoder.net
Member ID:   3314668
Language:    Transact-SQL
Platform:    Windows
Technology:  SQL 2000
Level:       Advanced
Description: This an articel explaining pitfalls found during setting up a system seperation using Merge Replication in SQL 2000, and resolutions
             found to resolve these pitfalls.
Section      Enterprise Systems
SubSection   SQL 2000
</PRE>

<!-------------------------------     STEP 2      --------------------------->
<!--  Include download and sample image information.                       --> 

<!-------------------------------     STEP 3      --------------------------->
<!--  Add the article text. Please use simple formatting (<h2>, <p> etc)   --> 

<H2>Introduction</H2>

<P>The following article will provide recently found issues discovered when planning a system seperation using Merge Replication using Microsoft
SQL 2000 Database Server. These issues (or pitfalls) were discovered first-hand in a real world environment at the company I work for when we were
 doing a monthly release of our in-house software, while getting ready for a complete System Seperation using Merge Replication. The resolutions
offered in this article have been tested and are known to work.

</P><H2>Background</H2>

<P>The company I work for made a decision that in order to keep up with the continuing growth of the company and our ever expanding customer base
the best way to improve the performance of both our web presence and our in-house applications was to do a complete System Seperation by co-locating
our database servers off-site, the cut down on the use of bandwidth and increase the overall performace of our websites. The decision was made to move all
the web data off-site and use Merge Replication in SQL 2000 to keep the in-house data and the web data synchronized, since the in-house applications
needed use of the web data as well.</P>

<P>The databases, and the required objects within the database (stored procedures, tables, user-defined functions, triggers, etc) were marked for
replication and all was moving ahead smoothly. When we create our database scripts, whether it be triggers, alter table scripts, or stored procedures,
we employ the use of an in-house created version control header to prevent old scripts from being promoted to production. In the header template
the script it searches the SysObjects System Table for a procedure with that name. If the procedure is found, it is dropped then recreated, incrementing
the version of the script, otehrwise it simply creates the stored procedure as normal. This is when the first pitfall was recognized. If a stored
procedure has been marked for replication using Merge Replication you cannot simply drop the procedure and re-create it, you must do the search (as normal for us),
if the procedure is found then the script alters the procedure, if it isn't found then it is created (a blank procedure with the parameter list) then
it is altered to add the "meat" of the procedure. This was a simple enough issue and resolution, causing a minor setback, but nothing we couldn't recover from
in a small amount of time.</P>

<P>The next pitfall, discovered by myself during the promotion and build of a monthly release of an in-house application, wasnt quite as simple
to fix, and could have caused a huge problem if it had not been discovered prior to the system seperation. Had that happened, if this situation
had been encountered (we'll ge to the "situation", or sequence of events momentarialy) while doing one of the monthly builds for anyone of the in-house application after the seperation it would have posed serious
problems for my company.</P>

<P><strong>Scenario:</strong><br>
While making updates for my application for the next monthly release, I created and ran an alter table script because I needed to add some
additional columns to a table. I created the script and ran it on the dev server, a mirror image of production, or so I thought, and it ran fine,
no rpoblems at all, so I continued with my tasks for the monthly release. It wasn't until it was time to promote the current monthly release to the
QA Department that the pitfall was discovered. While running one of the Alter Table scripts I created, the following error was displayed:<br>
<pre>
Version: 01.00 of: PAT dbo.<sup><font COLOR="red">*</font></sup>Alter_Table_Script.pat  (1 row(s) affected)  Server: Msg 4931, Level 16, State 1, 
Line 3 Cannot add columns to table <sup><font COLOR="red">*</font></sup>'Table_Name' because it is being published for merge replication. - 
Aborting!!!</pre></p>
<P>
Well as you can imagine when I seen this I had a feeling this wasnt good news. It was 9PM at night and I knew there was nothing I could do until morning when
the DBA arrive at work.</p>
<p><strong>The next morning:</strong><br>
So 7:00 AM rolls around the next day and I am already at work with the DBA trying to find out what went wrong, 
and what kind of impact this had on our system seperation project. After a little research he thought the system seperation was dead, unless we could upgrade to
SQL 2005 and in a hurry, as the seperation was mere weeks away. There was some fierce searching going on in the office, Google was being taxed, online
SQL 2000 reference sites, in-house SQL Library (which is actually quite extensive), there were 3 of us desperately looking for a resolution to our problem.</P>
<p>
The first solution turned out to be not so reliable. When a database is marked for replication, then subsuquent tables are marked, SQL creates a
cooresponding table for the marked table, the tables are named <strong>conflict_DBName_TableName</strong>, so I started building a query based on that, until we realized that
SQL 2000 doesn't clean up after itself, when a table is no longer marked for Merge Replication that table isnt deleted, thus offering the
possibility of "false positives" when the query is ran. So of course, this solution was scrapped. That's when the thirs person rusing to find a solution found the
end all be all of solutions.</p>
<p><strong>Solution:</strong><br>
When a database is marked for Merge Replication SQL 2000 creates a new database, aptly named <strong>distribution</strong>, inside this database is a table named
"MSArticles". It is in this table that all the names of all objects that have been marked for Merge Replication are stored, along with the object type.
Then there is a system stored procedure names <strong>sp_repladdcolumn</strong>, but of course this stored procedure can only be ran on tables that
have been marked for replication, it will fail otherwise. This gave me what I needed, what I had to do was first check and see if the database has been marked for Merge Replication,
as if its not then the search of <strong>distribution.dbo.MSArticles</strong> will cause an error, thus failing all together. Taking this all into
consideration, I wrote the below script that did everything I needed it to do, tested it, then released it to the other developers on the team.
</p>
<p><sup><font COLOR="red">*</font></sup>Denotes table name and script name changed<br></p>

<P></P>


<P></P><H2>Using the code</H2>

<P>The script I came up with below will have certain information missing, information that is specific to our Version Control Header, and
propietary to the company I work for, I will however show the script part that actually:
<UL>
<li>Checks if the database has been marked for Merge Replication</li>
<li>Check if the table actually exists (just a double check I like to perform, I dont like suprises)</li>
<li>Checks to see if the table has been marked for merge replication</li>
<li>Alter the table accordingly</li>
</UL>
</P><PRE>
//
// Below is the script used to check required elements so that an Alter Table script can be created and ran on a SQL 2000 database
// that has been marked for Merge Replication.
//
/*First check if the database has been marked for replication.
  If it hasnt and you search distribution.dbo.MSarticles you will an "Invalid Object Name" error
*/
IF EXISTS(SELECT * FROM master..sysdatabases WHERE name='distribution')
	BEGIN
		/* Next make sure table exists in the database*/
		IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Table_Name')
			BEGIN
				/* Now check if the table has been marked for replication*/
				IF EXISTS (SELECT Name, CASE(xtype) WHEN 'U' THEN 'User table' ELSE xtype END AS ObjectType FROM sysobjects  
				WHERE NAME IN (SELECT source_object from distribution.dbo.MSarticles) AND Name = 'Table_Name')
					BEGIN	/*Table is marked for replication*/
						/*
						 TODO: Process for adding a new column
						 Repeat as necessary for all columns to be added 
						Exec sp_repladdcolumn @source_object = N'[dbo].[Table_Name]', @column = N'column_name', 
						@typetext = N'char(5) null', @publication_to_add = N'all'
						*/
						Exec sp_repladdcolumn @source_object = N'[dbo].[Table_Name]', @column = N'column_name', 
						@typetext = N'TIMESTAMP NOT NULL', @publication_to_add = N'all'
					END
				ELSE
				/* Table isnt marked for replication so alter as normal*/
					BEGIN
						ALTER TABLE
							Table_Name
							/* Add your columns here*/
						ADD
							/* Repeat as necessary for the columns you need*/
							Column_Name Data_Type
					END
			END
	END
ELSE
	/* The table is in a database that hasnt been marked for Merge Replication. THis check needs to be done, if you query the
	   distribution.dbo.MSArticles table in a database that hasnt been marked for Merge Replication the whole query will fail
	   as that database & table dont exist.
	BEGIN
		ALTER TABLE
			Table_Name
			/* Add your columns here*/
		ADD
			/* Repeat as necessary for the columns you need*/
			Column_Name Data_Type
	END
</PRE>

<P>That is the script we now use for our alter table scripts since many of our database and database objects have been marked for Merge Replication.
As for other object, such as Stored Procedures, the old way our script worked for to simply query <code>sysobjects</code> to see if the procedure
existed, if it did then drop it and recreate it (and with our Version Control Header the version would increment, preventing old versions from being
promoted to live, but that's a different article all together). So we as a team came up with this script for our Stored Procedures:
<pre>
/* First check <code>sysobjects</code> to see if the procedure exists, if it doesnt exist then we need to create
a shell of the procedure. Use <b>Dynamic SQL</b> as the <b>Create Procedure</b> is supposed to be the first
line in a stored procedure, so you cant do the norm within an IF block*/
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Stored_Procedure_Name')
BEGIN
	PRINT 'Creating Procedure Stored_Procedure_Name'
	DECLARE @SQL varchar(8000)
	SET @SQL = 'CREATE PROCEDURE Stored_Procedure_Name	/* Param List */ 
	AS'
	EXEC (@SQL)
END
PRINT 'Altering Procedure Stored_Procedure_Name'
GO
/* Then ALTER the procedure like you would alter any normal stored procedure*/
ALTER PROCEDURE Stored_Procedure_Name
	/* Param List */
AS
</pre>

That's is, this is how to overcome the pitfalls discovered while doing a Merge Relication in a SQL 2000 database.</P>

<H2>Points of Interest</H2>

<P>
This scenario was corrected in SQL 2005, and I imagine in subsuquent release of the database program so if you aren't running SQL 2000 then
this shouldn't be an issue. If, however, you are running SQL 2000 and are planning a Merge Replication then this information needs to be brought
to the attention of your DBA. Chances are he already knows this, but if he doesn't then you will save him, and your company a ton of headaches.
</P>
<!-------------------------------    That's it!   --------------------------->
</P></BODY>
</HTML>

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Psycho-*Coder*-Extreme
Software Developer (Senior)
United States United States
I received my Bachelors from the University of Georgia in 1989 and have been a Windows programmer ever since. I have worked with encryption algorithms, online payment systems, Windows Development, MSSQL 4 and above, VB 4 and above. I received my .Net Certification in 2005 and currently work as a Software Developer in a .Net House (mainly VB.Net, but I also do some C# on the side). I run a small Web & Software Development company from my home and (as stated) work full-time as a .Net programmer.
 
Update: I am now Senior Application Developer for a small upstart company. I develop solely in C# utilizing MSSQL

| Advertise | Privacy | Mobile
Web01 | 2.8.141015.1 | Last Updated 12 Jun 2007
Article Copyright 2007 by Psycho-*Coder*-Extreme
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid